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

[CRPence <CRPbottle@xxxxxxxxx>]

  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