RE: SQL cursor inputting default dates from a DDS file

It is always had to say anything without knowing your query and without
knowing the SQL Message (SQLCODE / SQLSTATE)
According to the message you get in green screen, I suspect it is a date
proble.
The message you get in green screen points to "numeric data". (Date is a
different data type).

Do you select numeric columns in your SELECT Statement?
If so, could it be these numeric columns include invalid numeric data
(*Blanks)?

You can check for invalid numeric data by running the following Query (if
you are on 7.3 or higher)
SELECT * FROM TABLE(SYSTOOLS.VALIDATE_DATA('YOURSCHEMA', 'YOURFILE'));

If the UDTF does not exist on your system, run a query that reads all rows
and then search the joblog forMessage-Id CPF5035.
The following query returns information about all invalid numeric values
read in the current job.
Select Cast(Trim(Substr(Message_Tokens, 1, 10)) as VarChar(10)) File,
Cast(Trim(Substr(Message_Tokens, 21, 10)) as VarChar(10))
Library,
Cast(Trim(Substr(Message_Tokens, 31, 10)) as VarChar(10)) Field,
BigInt(Substr(Hex(Message_Tokens), 81, 8)) RRN,
Cast(Trim(Substr(Message_Second_Level_Text, 57))
as VarChar(256)) Text
From Table(QSYS2.JobLog_Info('*')) x
where MESSAGE_ID = 'CPF5035';

Mit freundlichen Grüßen / Best regards

Birgitta Hauser


"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training them
and keeping them!"
?Train people well enough so they can leave, treat them well enough so they
don't want to.? (Richard Branson)


-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Alan
Cassidy
Sent: Dienstag, 26. Januar 2021 22:49
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: Re: SQL cursor inputting default dates from a DDS file

I just remembered to try it in the 5250 version with STRSQL.

Now I got a numbered error for SQL0302: "Numeric data that is not valid".

So it looks like not a date issue as I thought.


On 1/26/2021 4:45 PM, Alan Cassidy wrote:

That's a good question, and I'll add an SQL statement to make sure,
but I think the answer to your question is Yes.


On 1/26/2021 4:35 PM, Therrien, Paul via RPG400-L wrote:

not sure if this is the answer, but in your connection configuration
do you have *ISO as date format?

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx] On Behalf
Of Alan Cassidy
Sent: Tuesday, January 26, 2021 4:13 PM
To: rpg400-l@xxxxxxxxxxxxxxxxxx
Subject: SQL cursor inputting default dates from a DDS file

Good afternoon Ladies and Gentlemen,

I'm trying to do a quick RPG query with an SQL cursor, but I need to
be able to input three dates, any one of which has '0001-01-01' in
the filtered subset I will try to fetch. Ran my statement in the
separate SQL scripting window (from RDi) but it threw the error that
there is a conversion error. I"m sure the above zero-one date is the
culprit.

I'm sure I solved this once, years back, but if anybody has the quick
answer to as to how I can cast those dates, I'd sure appreciate it
and would send good vibes your way!

(I wouldn't even mind if our global SQL guru piped in her thoughts!
You know who you are!)

--aec

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post
a message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription
related questions.

Help support midrange.com by shopping at amazon.com with our
affiliate link: https://amazon.midrange.com

--
This is the RPG programming on IBM i (RPG400-L) mailing list To post a
message email: RPG400-L@xxxxxxxxxxxxxxxxxx To subscribe, unsubscribe, or
change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com