Re: Imbedded SQL and Date

[CRPence <CRPbottle@xxxxxxxxx>]

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