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

I'm going to change the compile option as well as put it in the SQL.

With regard to imbedding in the SQL, is this correct:
/EXEC SQL
+ SET OPTION DATFMT(*ISO)
+ DECLARE C1 CURSOR FOR
+ SELECT MIN(HIDATE)
+ FROM MHIST
/END-EXEC

When I change this in PDM, I get a message saying the ( is invalid.


Jim


On Sun, Jul 20, 2014 at 3:59 AM, Birgitta Hauser <Hauser@xxxxxxxxxxxxxxx>
wrote:

Hi James,

The translaters get better and better (at least for simple sentences). ;)

@1: If you change the date format in STRSQL with F13 option 1 it should be
preserved for all sub-sequent calls.

@2: I prefer to include an SET OPTION statement in all my embedded SQL
programs (sometimes I need to compile on our customer's machines, and
without the SET OPTION statement, I've always remember the settings). But
if your company, manager etc. will allow it you can change the defaults of
the CRTSQLRPGI system command with the CHGCMDDFT command.

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 20:24
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: 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.

--
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.