I have a fairly simple query that runs fine in an analyzer window but turns all values in a particular 'CASE' column to null when run as an aquascript. After saving it as an aquascript the query looks like this:
query = 'SELECTn v.SCHED_PROV, TO_CHAR(v.APPT_DATE, 'MM/DD/YYYY') APPTDATE, TO_CHAR(v.APPT_DATE, 'Dy') n DAY,v.VIS_TYPE, n CASE n WHEN p.MEDREC IS NOT NULL n THEN 'LPP' n ELSE NULL n END LPP,n.MRN_FULLnFROMn CCHOSPDATA.ONC_NP n n JOIN CCHOSPDATA.ONC_VISITS v n ON n.MRN=v.MRN n LEFT OUTER JOIN PATIENT p n ON p.MEDREC=n.MRN nWHEREn TO_CHAR(v.APPT_DATE, 'WW') = TO_CHAR(SYSDATE – 12, 'WW') n AND SYSDATE – v.APPT_DATE 'LYMPHOMA%' nORDER BYn v.APPT_DATE, v.SCHED_PROV, n.LASTNAME, n.FIRSTNAME';
Just run in an analyzer window it looks like this:
SELECT
v.SCHED_PROV, TO_CHAR(v.APPT_DATE, 'MM/DD/YYYY') APPTDATE, TO_CHAR(v.APPT_DATE, 'Dy')
DAY,v.VIS_TYPE,
CASE
WHEN p.MEDREC IS NOT NULL
THEN 'LPP'
ELSE NULL
END LPP,n.MRN_FULL
FROM
CCHOSPDATA.ONC_NP n
JOIN CCHOSPDATA.ONC_VISITS v
ON n.MRN=v.MRN
LEFT OUTER JOIN PATIENT p
ON p.MEDREC=n.MRN
WHERE
TO_CHAR(v.APPT_DATE, 'WW') = TO_CHAR(SYSDATE – 12, 'WW')
AND SYSDATE – v.APPT_DATE 'LYMPHOMA%'
ORDER BY
v.APPT_DATE, v.SCHED_PROV, n.LASTNAME, n.FIRSTNAME
I've attached screen shots to show how the 'LPP' column is completely empty when run as a script and populated normally when run in an Analyzer window. I've tried various ways of formatting this as a CASE statement but all seem to fail. This is Oracle 10g on OS 10.6.8 and ADS 10.0.4_05