I am writing a query to pull employee and account information. I keep a running history with no overlapping dates per employee in an accounthistory table. I am basically trying to prompt my users for which date they want the employee/account for that employee. So I need my join to be dynamic/conditional based on what date option the user will select at run time. Here is the current query I am working with which returns a missing expression error on line#3 column#51. I am still digging around but thought I would ask here in case someone has achieved something similar like this already.
(The user will populate the :DATEOPTION variable via the application. Any other variables are automatically populated by the application)
SELECT EMP.employeeid, ACT.accountname FROM employee EMP LEFT OUTER JOIN accounthistory AHI ON (EMP.employeeid = AHI.employeeid AND CASE :DATEOPTION WHEN 1 THEN EMP.ppstartdatedtm BETWEEN AHI.effectivedtm AND AHI.expirationdtm WHEN 2 THEN EMP.ppenddatedtm BETWEEN AHI.effectivedtm AND AHI.expirationdtm WHEN 3 THEN EMP.cpstartdatedtm BETWEEN AHI.effectivedtm AND AHI.expirationdtm WHEN 4 THEN EMP.cpenddatedtm BETWEEN AHI.effectivedtm AND AHI.expirationdtm WHEN 5 THEN TO_DATE(:STARTDATETIME,'MM/DD/YYYY HH24:MI:SS') BETWEEN AHI.effectivedtm AND AHI.expirationdtm WHEN 6 THEN TO_DATE(:ENDDATETIME,'MM/DD/YYYY HH24:MI:SS') BETWEEN AHI.effectivedtm AND AHI.expirationdtm END) INNER JOIN account ACT ON (AHI.accountid = ACT.accountid
