Re: Data Mapping error using SQL UDF (was:(no subject))

The original message does not appear on the NewsGroup, so I am replying to this response\message.

The file.member being queried, from which the data is derived [for an expression] is always identified in such messages. What would be of most interest if all rows were processed in the presumed-successful query request; e.g. output to outfile versus display.

The fact that the SELECT performed the functions correctly, but that when the implicit CAST to the UDTF defined columns failed, should be worth a review. The simple SELECT just creates a report according to the defined expressions\columns. The FUNCTION must additionally force those expressions\columns to fit in the defined column list. The UDTF is also likely running optimized for all rows. As alluded above, the non-UDF query may simply not have encountered a row causing the issue.

While not a good explanation for the errors shown [more likely a /bad/ value instead for the column CKLWDT], the most conspicuous problem I see with the source is a mismatch\misalignment between the CHAR(10) for a date-as-formatted-date-string and the expression that defined that; i.e. columns RELEASED and RISK seem most suspect.

Regards, Chuck

On 27 Jul 2012 06:22, Luis Rodriguez wrote:

The error would seem to be in HTRANS, not in your UDF. Is HTRANS
a view? Are you sure STRSQL is not giving errors when doing a
SELECT * FROM HTRANS where CKB2CD = '4458499W'?

On Fri, Jul 27, 2012 at 6:34 AM, Jonathan Mason wrote:

Sorry for all the questions, I'm getting to grips with UDFs 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
format 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) || '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 (source
included 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.

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