Re: Imbedded SQL and Date

Dave Petrosky wrote:

Searched this archive and all over the web (actually reached the
guy at the end and he beat me)
I have this code:
dtodaysDate s 8 0
todaysDate = %dec(%date());
Exec SQL Create Alias QTEMP/OrderHeaderRemote for OHORDHP(REMOTE);

I personally recommend avoiding the use of a "long name" in QTEMP when not absolutely required; i.e. when not using SESSION tables in portable SQL code. Doing so requires the DB2 for i SQL to establish a special list of long names; a list unique to that process, and FWIW I believe also stored [or indirectly via a pointer which is stored] in QTEMP.

Also for SQL that does not need to be ported from DB2 for i SQL, nor is required to use a long name, a better option than a temporary ALIAS is to just use an override; e.g. OVRDBF OHR TOFILE(OHORDHP) TOMBR(REMOTE) OVRSCOPE(as_appropriate), such that the SQL statement then references unqualified table name OHR. FWiW an ALIAS with a "long name" is actually doubly indirect because the SQL must first redirect to the private long name list for the process, in order to get the short name, then from that short name the final name is then resolved from the DDM file which implements the ALIAS.

Exec SQL
Insert Into WRKBATRMT
( workFile, workMember, workCMPN, workDIVN, workDPTN
, workCUSN, workORNR)
Select 'OHORDHP', 'REMOTE', ohcmpn, ohdivn, ohdptn
, ohcusn, ohornr
from QTEMP/OrderHeaderRemote
where (20000000 + OHDTET) < todaysDate;

As embedded SQL, presumably the WHERE clause was instead:
where (20000000 + OHDTET) < :todaysDate;

FWiW if all dates are known to be since the start of year 2010 and before the start of year 2100, the value for todaysDate can have the 20000000 subtracted to eliminate the expression in the WHERE clause. That would enable an index or column statistics on OHDTET to be used when implementing the query.

This returns no rows. If I take this code to interactive SQL and
substitute 20100325 for todaysDate, the code works fine.

Was the variable verified to have that same value, as viewed in a debug of the program, as compared to what was specified in the interactive request? That is, did "todaysDate = %dec(%date());" effect what was intended?

I have tried making todaysDate as packed, tried casting
(20000000 + OHDTET) and todaysDate as date, as char(8) first
then as date. I have some hair left, so please help me out.

A string of ten digits as character string, a string without any separators, does not CAST to a DATE data type since the separators are required to recognize the format of the date string.

Regards, Chuck