CASE statement in query produces error - "invalid identifier"

From: William Threlfall <William.Threlfall_at_albertahealthservices.ca>
Date: Thu, 6 Feb 2014 13:54:21 -0700
Message-ID: <36800EC4761690448F1B444A1AEF44BB13E88FD4_at_EXMBXC5.crha.bewell.ca>

The following SQL code picks the first occurrence of an AST lab result that exists on the latest date during the time period of one month post-transplant +/- 10 days

(SELECT ast FROM patient_labs x

    WHERE x.ast IS NOT NULL
    AND x.pat_id = pd.pat_id
    AND x.date_of_lab =

             (SELECT MAX(pl.date_of_lab) FROM patient_labs pl
                WHERE pl.ast IS NOT NULL
                AND pl.pat_id = pd.pat_id
                AND (pl.date_of_lab >= (add_months(pt.date_of_trans,1) - 10) AND pl.date_of_lab <= (add_months(pt.date_of_trans,1) + 10)))
     AND ROWNUM < 2) "AST 1 Month",

What I really want is the AST lab result that exists on the date closest to one month post-transplant within the range of one month post-transplant +/- 10 days. I tried using MEDIAN instead of MAX, but that doesn't consistently give the AST result on the date closest to one month post-transplant either.

So I thought of using a CASE to get the AST (if it exists) on the exact date of one month post-transplant, or failing that, try +/- 1 day, then +/- 3 days, +/- 5 days, +/- 7 days and finally +/- 10 days, otherwise NULL, using the following code:

(SELECT AST FROM patient_labs x

    WHERE x.AST IS NOT NULL
    AND x.pat_id = pd.pat_id
    AND x.date_of_lab =

            CASE WHEN pl.ast IS NOT NULL AND pl.pat_id = pd.pat_id AND pl.date_of_lab = add_months(pt.date_of_trans,1) THEN pl.date_of_lab
                       WHEN pl.ast IS NOT NULL AND pl.pat_id = pd.pat_id AND pl.date_of_lab >= add_months(pt.date_of_trans,1) - 1 AND pl.date_of_lab <= add_months(pt.date_of_trans,1) + 1 THEN pl.date_of_lab
                       WHEN pl.ast IS NOT NULL AND pl.pat_id = pd.pat_id AND pl.date_of_lab >= add_months(pt.date_of_trans,1) - 3 AND pl.date_of_lab <= add_months(pt.date_of_trans,1) + 3 THEN pl.date_of_lab
                       WHEN pl.ast IS NOT NULL AND pl.pat_id = pd.pat_id AND pl.date_of_lab >= add_months(pt.date_of_trans,1) - 5 AND pl.date_of_lab <= add_months(pt.date_of_trans,1) + 5 THEN pl.date_of_lab
                       WHEN pl.ast IS NOT NULL AND pl.pat_id = pd.pat_id AND pl.date_of_lab >= add_months(pt.date_of_trans,1) - 7 AND pl.date_of_lab <= add_months(pt.date_of_trans,1) + 7 THEN pl.date_of_lab
                       WHEN pl.ast IS NOT NULL AND pl.pat_id = pd.pat_id AND pl.date_of_lab >= add_months(pt.date_of_trans,1) - 10 AND pl.date_of_lab <= add_months(pt.date_of_trans,1) + 10 THEN pl.date_of_lab
             ELSE NULL
            END
     AND ROWNUM < 2) "AST 1 Month",

However, the code above produces an error, telling me that pl.date_of_lab is an invalid identifier. So it seems that I am doing something wrong in my use of the CASE statement.

Can anyone give me some idea of how to get what I want?

Oh and while I'm posting, I have noticed that almost all the posts are about DBA and/or System Administrator issues, mostly dealing with the inner workings of Oracle. I have been a DBA in the past and a System Administrator in the past, but in my current role, I'm an Oracle SQL Developer. I am struggling at this point in time because it's been 15 years since the last time I did Oracle SQL and PL/SQL development. During my last contract I was using FoxPro 9, and the contract before that I was using MS SQL Server T-SQL, both of which have built-in procedural command language to calculate things (e.g. picking out the AST lab result closest to 1 month post-transplant, or calculating age at most recent CREA lab result) much easier than I can do in Oracle using pure SQL. PL/SQL would probably be lots easier, but for now I am trying to do what I want to do using pure SQL. If I can't do what I want in pure SQL, then I will try using PL/SQL.

So if there is a better place than this to get input/advice/help for SQL or PL/SQL development, please let me know. There's no point in posting here if nobody (or almost nobody) has any interest in how to do certain things in an SQL query or a PL/SQL procedure.

Thanks and Cheers, - Bill.

---

This message and any attached documents are only for the use of the intended recipient(s), are confidential and may contain privileged information. Any unauthorized review, use, retransmission, or other disclosure is strictly prohibited. If you have received this message in error, please notify the sender immediately, and then delete the original message. Thank you.

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Feb 06 2014 - 21:54:21 CET