I believe it is because of the defaults for the date format and
separator in STRSQL. Use F13 and then option 1 to change those to what
your function is assuming.
Scott
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Jonathan Mason
Sent: Friday, July 27, 2012 5:05 AM
To: 'midrange-l@xxxxxxxxxxxx'
Subject:
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.
midrange.com
