Re: CASE statement in query produces error - "invalid identifier"

    Use an analytical function instead, something like

select ast
from (SELECT ast,

                      row_number() over (order by abs(pt.date_of_trans -
x.date_of_lab)) rn
         FROM patient_labs x
         WHERE x.ast IS NOT NULL
              AND x.pat_id = pd.pat_id
              and x.date_of_lab between add_months(pt.date_of_trans,1) - 10)
                                                  AND

HTH,

-- 
Stéphane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>
Author, SQL Success
<http://www.amazon.com/SQL-Success-Database-Programming-Proficiency/dp/1909765007/>,
The Art of SQL
<http://www.amazon.com/Art-SQL-Stephane-Faroult/dp/0596008945/>,
Refactoring SQL Applications
<http://www.amazon.com/Refactoring-SQL-Applications-Stephane-Faroult/dp/0596514972/>




On 02/06/2014 09:54 PM, William Threlfall wrote:


> 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