How PeopleSoft SQL Objects Treat NULL Dates

Something confusing happened when I invoked one of my SQL objects in some App Engine PeopleCode, but now that I understand it, it makes sense.  The example that I use involves the passing in of two date values, both of which happen to be NULL.  Below is an explanation of how PeopleSoft compensates for incoming NULL date values.


Here's the SQL text from the SQL object that I used, which has 11 bind variables, including two of them that expect date values:

SELECT 'Y'   

  FROM PS_Y_AUTORECON_DTL   

 WHERE OPERATING_UNIT = :1     

   AND ACCOUNT = :2     

   AND CLASS_FLD = :3     

   AND JOURNAL_ID = :4     

   AND JOURNAL_DATE = %DateIn(:5)     

   AND JOURNAL_LINE = :6     

   AND CHARTFIELD1 = :7     

   AND AMOUNT = :8     

   AND JRNL_LN_REF = :9   

   AND LINE_DESCR = :10     

   AND JOURNAL_LINE_DATE = %DateIn(:11)

After appending -TOOLSTRACESQL 7 to my Process Definition, the following came out as part of the .trc file in Process Scheduler

2092 10:22:03.425 0.000176 Cur#3.19380.SADEV RC=0 Dur=0.000075 COM Stmt=SELECT 'Y' FROM PS_Y_AUTORECON_DTL WHERE OPERATING_UNIT = :1 AND ACCOUNT = :2 AND CLASS_FLD = :3 AND JOURNAL_ID = :4 AND JOURNAL_DATE IS NULL AND JOURNAL_LINE = :5 AND CHARTFIELD1 = :6 AND AMOUNT = :7 AND JRNL_LN_REF = :8 AND LINE_DESCR = :9 AND JOURNAL_LINE_DATE IS NULL2093 10:22:03.425 0.000034 Cur#3.19380.SADEV RC=0 Dur=0.000000 Bind-1 type=2 length=8 value=11890077
2094 10:22:03.425 0.000030 Cur#3.19380.SADEV RC=0 Dur=0.000001 Bind-2 type=2 length=4 value=1820
2095 10:22:03.425 0.000029 Cur#3.19380.SADEV RC=0 Dur=0.000000 Bind-3 type=2 length=5 value=GRCLR
2096 10:22:03.426 0.000029 Cur#3.19380.SADEV RC=0 Dur=0.000001 Bind-4 type=2 length=8 value=JLM08046
2097 10:22:03.426 0.000029 Cur#3.19380.SADEV RC=0 Dur=0.000000 Bind-5 type=19 length=1 value=0
2098 10:22:03.426 0.000028 Cur#3.19380.SADEV RC=0 Dur=0.000000 Bind-6 type=2 length=1 value=
2099 10:22:03.426 0.000037 Cur#3.19380.SADEV RC=0 Dur=0.000001 Bind-7 type=19 length=1 value=0
2100 10:22:03.426 0.000030 Cur#3.19380.SADEV RC=0 Dur=0.000001 Bind-8 type=2 length=1 value=
2101 10:22:03.426 0.000029 Cur#3.19380.SADEV RC=0 Dur=0.000001 Bind-9 type=2 length=1 value=

Apparently, because the two incoming date values were NULL, PeopleSoft magically changed the SQL so that there were only 9 bind variables instead of the 11 in the original SQL object.  It then substituted "IS NULL" in place of the other two bind variables.