sql statement - Why no work? - solved!

[Andrew Lin <alin@TECHNEGLAS.COM>]

Hi all,

I got great suggestions from Bill, teknogeek, samir, ginger, and
denise.

Everytime I got a new suggestion, I thought, "YES, that must be
it...."

Then I tried it, and it still didn't work.  However in the process of
trying out
different things I narrowed the problem  down to:

  AND C.EFFDT =
       (SELECT MAX(EFFDT)
        FROM   PS_JOB
        WHERE  EMPLID    = $EmplID
          AND  EMPL_RCD = #Empl_RcdNo
          AND  EFFDT    <= $Pay_End_Dt)    <------- sqr didn't like
this
   AND C.EFFSEQ =
       (SELECT ... <snip>)
   AND D.EFFDT =
       (SELECT MAX(EFFDT)
        FROM   PS_BEN_PROG_PARTIC
        WHERE  EMPLID    = $EmplID
          AND  EMPL_RCD = #Empl_RcdNo
          AND  EFFDT    <= $Pay_End_Dt)   <------- or this

Anyway, I ended up declaring a date variable and using that instead,
which is weird since I've never run into this problem before with
comparing dates to string-dates.

Thanks all!

PS: Just got an e-mail from Taj.Mohammed; he nailed it on the head.