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

From: Rumpi Gravenstein <rgravens_at_gmail.com>
Date: Fri, 7 Feb 2014 14:43:19 -0500
Message-ID: <CAEpg1wDz+isxmHftWGA55Xk5+JFnYAtNKnSHdE7UTzqqXuFoSA_at_mail.gmail.com>

In both of your your code snippets (one of which follows) you have aliased your table as x.

<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 Mont

</code>

If you column is coming from that table it should be referenced as x.date_of_lab
and not pl.pl.date_of_lab.

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

Received on Fri Feb 07 2014 - 20:43:19 CET