SELECT EMPL_TERM_DATE,
              ORGN_CODE_HIER,
              FULL_NAME,
              AS_OF_DATE,
              LOCN_DESC,
             JOB_CONTRACT_TYPE,
             JOB_END_DATE,
             PCLS_CODE,
             JOB_BEGIN_DATE,
             CURRENT_HIRE_DATE,
             ORGN_CODE_HIER
FROM   PZVEMPL A
 WHERE  EMPL_TERM_DATE IS  NOT  NULL  AND
ORGN_CODE_HIER = {?orgn}
AND AS_OF_DATE=TO_DATE('5/14/2008','MM/DD/YYYY' )
AND FULL_NAME = 'Brown, Jack N.'
and JOB_END_DATE = (SELECT MAX(JOB_END_DATE) FROM PZVEMPL B WHERE B.PIDM_KEY = A.PIDM_KEY)

 

Things work fine.  I get the record I expected.  However, I need to have the TO_DATE function work on a variable.  I setup a date variable called EndDate and entered 5/14/2008.  The query is 

 

SELECT EMPL_TERM_DATE,
              ORGN_CODE_HIER,
              FULL_NAME,
              AS_OF_DATE,
              LOCN_DESC,
             JOB_CONTRACT_TYPE,
             JOB_END_DATE,
             PCLS_CODE,
             JOB_BEGIN_DATE,
             CURRENT_HIRE_DATE,
             ORGN_CODE_HIER
FROM   PZVEMPL A
 WHERE  EMPL_TERM_DATE IS  NOT  NULL  AND
ORGN_CODE_HIER = {?orgn}
AND AS_OF_DATE=TO_DATE({?EndDate},'MM/DD/YYYY' )
AND FULL_NAME = 'Brown, Jack N.'
and JOB_END_DATE = (SELECT MAX(JOB_END_DATE) FROM PZVEMPL B WHERE B.PIDM_KEY = A.PIDM_KEY)

 

I get a message saying missing expression.  It must be on the TO_DATE line because that is the only line I changed.  (How many times have you heard that before ????)  Does the query need something special when using date variables ? 

 

I don't have access to run my query directly against the database.  I can only test in Crystal.

 

Thanks for your help,
John