RE: Tracing iSeries SQL Queries from a web page

PERFECT!  Thanks Birgitta, it worked out fine.

Brian.

-----Original Message-----
From: Hauser, Birgitta [mailto:Birgitta.Hauser@xxxxxxxxxxx] 
Sent: Friday, January 13, 2006 2:42 AM
To: 'Midrange Systems Technical Discussion'
Subject: AW: Tracing iSeries SQL Queries from a web page

Hi,

the second solution would be the better one.
A date is always stored as a binary value. The scalar function will convert
the character representation of a date into a binary value.
The character representation of a date must have a 4 digit year.
But you can either use 'YYYY-MM-DD' or 'MM/DD/YYYY' or 'DD.MM.YYYY'.

If you convert a date into a character representation, you also can specify
a date format, which is ISO for 'YYYY-MM-DD' and USA for 'MM/DD/YYYY' or EUR
for 'DD.MM.YYYY'. If you don't specify a date format in the scalar function
CHAR, the job's date format is used. Normally the job's date format has a 2
digit year.  Because the job's data format may be different for different
jobs or connection, add a date format to the scalar function to fix it.
CHAR(KRDATE, ISO) = '2006-01-13'

Using the scalar function CHAR on the left side of the equal sign, will bar
the query optiomizer to use an existing access path (logical file or index)
Access path used by SQL must be built over existing columns in the
underlying table.

Usign the scalar function DATE on the right side of the equal sign, will
allow to use a access path built over the DATE column.

Birgitta
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von rob@xxxxxxxxx
Gesendet: Donnerstag, 12. Januar 2006 23:02
An: Midrange Systems Technical Discussion
Betreff: Re: Tracing iSeries SQL Queries from a web page

Maybe the STRSQL and your web connection use different job date formats 
and that is why it works differently.
Can you change
and char(krdate)='12/23/06'
to
and krdate=date('2006-12-23') 
For future reference, always pick a day greater than 12 so that we can 
make sure that we format it right.  And not have to think "did he mean 
mm/dd/yy or dd/mm/yy?".

Rob Berendt