Re: SQL returns "++++++++++" for MIN(DATE)

Birgitta,

Vielen Dank! Ist es nicht toll, dass auch ein Idiot wie ich verwenden kann
Google Translate und Ton intelligent? :)

1. I used STRSQL and F13 to change the date format. It now displays the
date as it should. Is there a way to change that so I don't have to change
it every time I use SQL?

2. I'm using CRTSQLRPGI to compile programs. Is there an easy way to
change the default for that command so I don't have to change it each time?

Many thanks!!

James R. Newman


On Sat, Jul 19, 2014 at 10:45 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

For each host variable used within embedded SQL the SQL precompiler
generates an additional variable named SQLXXXXX.
In case of the date variables the precompiler does neither use the date
format specified within the D- specs nor those defined in the H-specs but
use the date format specified within the compile command option DATFMT.
The default for this date format is *JOB which normally means a date format
with a 2 digit year.
The problem occurs as soon as your RPG variable initialized with a value
lower than 01/01/1940 into the additionally defined SQL variable.
To circumvent this problem just specify a DATEFMT with a 4 digit year, i.e.
ISO, EUR, USA, JIS in the compile command.
An even better alternative is to include a SET OPTION statement in your RPG
program where a date format with a 4 digit year is specified.
Attention the SET OPTION statement must physically be the first SQL
statement in your source code.

BTW in STRSQL just change the date format with F13 --> Option 1 to a date
format with a 4 digit year.

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!"

-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von James
Newman, CDP
Gesendet: Saturday, 19.7 2014 17:03
An: RPG programming on the IBM i / System i
Betreff: SQL returns "++++++++++" for MIN(DATE)

I have the following code in an RPG ILE program:

/EXEC SQL
+ DECLARE C1 CURSOR FOR
+ SELECT MIN(HIDATE)
+ FROM MHIST
/END-EXEC

FWIW, all my date fields are ISO (YYYY-MM-DD).

The program was blowing up when I tried to move the result to another date
field, saying I had an invalid date. I looked at the dump and the date
field contained "++++++++++".

I did STRSQL, ran the command, and got the same thing. I used Query and it
shows the minimum (oldest) date as "1915-01-05". Ran the SQL version again
but this time didn't ask for MIN. Instead asked to see the data and sorted
by date. All the dates prior to 1940-01-01 are "++++++++++".

I know I can sort the records by date, SETLL, and READ but I figured this
would be faster and more efficient.

Is there a "Yes, I really have data before 1940 that I'd like to see"
switch somewhere in SQL that I need to flip?

TIA.


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


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