Re:

Hi All

Sorry for all the questions, I'm getting to grips with UDF's in SQL and slowly going round the bend.

I have a UDF that simply accepts a reference number and returns a subset of columns from a transaction file. Within the set of columns I have a number of seven digit date fields, i.e. in the formay CYYMMDD which were being displayed with comma separators. To resolve this I'm using the following formula to convert to a 10 character date in ISO format:

char(date(right(digits(DATE_COLUMN + 19000000), 8) concat '000000'), ISO)

The function creates successfully, however when I run it in interactive SQL I get the following error:

select * from table(GETTRANS('4458499W')) T1

Query cannot be run. See lower level messages.
However, when I run the SQL from the function itself (at end of post) it runs successfully:

select

CKB2CD,
char(date(right(digits(CKHEFD+19000000),8) concat '000000'), ISO),
CKAQCD, CKPEST, CKBIST, CKAZVA, CKPGST, CKBGST, CKPHST, CKB7CD,
char(date(right(digits(CKLWDT+19000000),8) concat '000000'), ISO),
CKBHST,
char(date(right(digits(CKCRDT+19000000),8) concat '000000'), ISO)
from
hplib/htrans
SELECT statement run complete.
Looking at the job log it says that the format of the date is invalid, but I don't understand how that can be the case if the formula works in interactive SQL.

The job log details and function source are shown below. If anyone can shed some light on what the problem is I'd be grateful.

Thanks

Jonathan

JOBLOG:

2 > strsql
Data mapping error on member HTRANS.
Data mapping error on member HTRANS.
Data mapping error on member HTRANS.
Syntax of date, time, or timestamp value not valid.
User-defined function error on member QSQPTABL.
Cancel reply received for message .


Message ID . . . . . . : CPF5035 Severity . . . . . . . : 10
Message type . . . . . : Diagnostic
Date sent . . . . . . : 27/07/12 Time sent . . . . . . : 11:47:22

Message . . . . : Data mapping error on member HTRANS.
Cause . . . . . : A data mapping error occurred on field
Cast(Concat(Substring((Cast(ABS(HTRANS_1.CKLWDT+19000000) AS
Numeric(9,0))),Character_Length((Cast(ABS(HTRANS_1.CKLWDT+19000000) AS
Numeric(9,0))))-8+1,8),'000000') AS TimeStamp) in record number 0, record
format *FIRST, member number 1, in member HTRANS file HTRANS in library
HPLIB, because of error code 17. The error code meanings follow:
17 -- The format of the data in a date, time, or timestamp field is not
valid.

FUNCTION:

create function GETTRANS(iPolicy varchar(8))

returns table (REFNO char(8), EFFECTIVE char(10), AGENT char(6),
ACCTSTS char(1), TRANTYP char(2), AMOUNT dec(13,2),
SUSPSTS char(1), HISTSTS char(5), POSTED char(1),
RELEASED char(10), RISK char(2), INSTALMENT char(1),
CREATED char(10)
)
language SQL

begin
return
select CKB2CD,
char(date(right(digits(CKHEFD+19000000),8) concat '000000'), ISO),
CKAQCD, CKPEST, CKBIST, CKAZVA, CKPGST, CKBGST, CKPHST, CKB7CD,
char(date(right(digits(CKLWDT+19000000),8) concat '000000'), ISO),
CKBHST,
char(date(right(digits(CKCRDT+19000000),8) concat '000000'), ISO)
from HPLIB/HTRANS
where CKB2CD = iPolicy;
end



________________________________
Proud partner of The Ageas Bowl and the Ageas Salisbury International Arts Festival.

Registered Address: Ageas House Tollgate Eastleigh Hampshire SO53 3YA Registered Number: 354568 England
Authorised and regulated by the Financial Services Authority

This e-mail together with any attachments are intended for the addressee only and may be private and confidential. If you are not the intended recipient, or the person responsible for delivering it to the intended recipient, you must not open any attachments, or copy, disclose, distribute, retain or use this e-mail, including any attachments, in any way whatsoever; please return it to us immediately using the reply facility on e-mail.

Consider the environment and think before you print this email.
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.