9 Replies Latest reply on Oct 20, 2013 12:29 AM by Muzz

    Invalid Identified

    Muzz

      Hi All

      I try to convert my query  by using emp table.

      Query

      SELECT empno,ename,JOB,

                          (SELECT (CASE WHEN sal>3000 THEN 999

                                  ELSE sal

                           end ) filteredsal

                           FROM

                           (

                           SELECT sal

                           FROM emp inner_emp

                           WHERE inner_emp.empno=outer_emp.empno

                            )) sss

      FROM emp outer_emp;

       

      I'm getting error message

      Error Message

      ORA-00904: "OUTER_EMP"."EMPNO": invalid identifier

      00904. 00000 -  "%s: invalid identifier"

      *Cause:   

      *Action:

      Error at Line: 9 Column: 44

       

      Please help me out. I know outer_emp.empno is out of scope but how come I fix this issue...

       

       

       


        • 1. Re: Invalid Identified
          Frank Kulash

          Hi,

           

          It depends on what your data is like, what results you want, and what version of Oracle you're using.

           

          Whenever you have a question, post a little sample data (CREATE TABLE and INSERT statements, relevant columns only) for all the tables involved, and the results you want from that data.
          Explain, using specific examples, how you get those results from that data.
          Always say what version of Oracle you're using (e.g. 11.2.0.2.0).

          See the forum FAQ: https://forums.oracle.com/message/9362002

           

          If emp.empno is unique, then perhaps you want something like

           

          SELECT  empno, ename, job

          ,       CASE

                      WHEN  sal > 3000

                      THEN  999

                      ELSE  sal

                  END               AS sss

          FROM    emp

          ;

          • 2. Re: Invalid Identified
            Muzz

            Hi Frank

             

            Thanks for the reply and I'm sorry about it. My oracle version is Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production and as per your query concern I have modified my query and now its something like that.

            Query

            SELECT empno,ename,JOB,

                                 (SELECT (CASE WHEN sal_prev>2000 THEN 999

                                        ELSE sal_prev

                                 end ) filteredsal

                                 FROM

                                 (

                                 SELECT LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,sal

                                 FROM emp inner_emp

                                 WHERE inner_emp.empno=outer_emp.empno

                                  )) sss

            FROM emp outer_emp;

             

             

             

            The above query is not allowing me to put outer_emp.empno inside. please suggest...

            • 3. Re: Invalid Identified
              Etbin

              Ask Tom "Is there some sort of nesting limit for ..."  ANSI SQL has table references (correlation names) scoped to just one level deep


              SELECT empno,

                     ename,

                     JOB,

                     (SELECT CASE WHEN sal_prev > 2000

                                  THEN 999

                                  ELSE sal_prev

                             end filteredsal

                        FROM (SELECT LAG(sal,1,0) OVER (ORDER BY sal) AS sal_prev,

                                     sal

                                FROM emp inner_emp

                               WHERE inner_emp.empno = outer_emp.empno

                             )

                     ) sss

                FROM emp outer_emp


              Regards

               

              Etbin

              • 4. Re: Invalid Identified
                AkhileshB

                Hello,

                 

                The following code would probably work.

                 

                SELECT empno,

                  ename,

                  JOB,

                  (SELECT  

                    CASE

                      WHEN sal_prev>2000

                      THEN 999

                      ELSE sal_prev

                    END

                   filteredsal

                  FROM

                    (SELECT empno,LAG(sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,

                      sal

                    FROM emp

                    )inner_emp

                    WHERE inner_emp.empno=outer_emp.empno

                  ) sss

                FROM emp outer_emp;

                1 person found this helpful
                • 5. Re: Invalid Identified
                  Muzz

                  Thanks for the replies, I went to Ask Tom link and tried to grab the idea but unable to understand properly. I appreciate if someone explain it in more detail as well as correct my query. Thanks

                  • 6. Re: Invalid Identified
                    Frank Kulash

                    Hi,

                     

                    That page has a lot of different questions and answers.  Which one(s) don't you understand?  The more specific you can be, the more helpful we can be.  Post a question like "Whn Tome Kyte said ... does that mean ...?  If so, why doesn't this work ....?  I would expect that query to produce ... because ..."

                     

                    The main question and answer, which is relevant to this thread, is that a sub-query can be coprrelated only to it's immediate parent query; not to a grand-parent or more distant ancestor.  Let's format your query to make this easier to see:

                     

                    SELECT  empno, ename, job               -- This is Level 1

                    ,      (                                -- Begin Level 2

                               SELECT ( CASE

                                            WHEN  sal_prev > 2000

                                            THEN  999

                                            ELSE  sal_prev

                                        END

                                      )  AS filteredsal

                               FROM   (                     -- Begin Level 3

                                         SELECT  LAG (sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,sal

                                         FROM    emp inner_emp

                                         WHERE   inner_emp.empno = outer_emp.empno    -- This causes the error

                                      )                     -- End Level 3

                           )                                -- End Level 2

                                    AS sss                  -- This is Level 1

                    FROM   emp outer_emp                    -- This is still Level 1

                    ;

                    Tom Kyte was saying that expressions in Level N can referenece columns from the tables in Levels N and N-1 only, not from Level N-2 or earlier.  In your code, the statement that causes the error is in Level 3. You could reference a table friom Level 3 - 1 = 2 at that point, but not outer_emp, which is from Level 1.

                    • 7. Re: Invalid Identified
                      Muzz

                      Hi Frank

                      Thanks for the great explanation and now as per my understanding I have to write my query something like this:

                      Query

                      (SELECT  empno, ename, job               -- This is Level 1

                      ,      (                                -- Begin Level 2

                                 SELECT ( CASE

                                              WHEN  sal_prev > 2000

                                              THEN  999

                                              ELSE  sal_prev

                                          END

                                        )  AS filteredsal

                                 FROM   (                     -- Begin Level 3

                                           SELECT  empno,LAG (sal, 1, 0) OVER (ORDER BY sal) AS sal_prev,sal

                                           FROM    emp inner_emp) inner_emp-- End Level 3

                                           WHERE   inner_emp.empno = outer_emp.empno    -- This causes the error

                                                            

                             )                                -- End Level 2

                                      AS sss                  -- This is Level 1

                      FROM   emp outer_emp )                   -- This is still Level 1

                      ;

                       

                      I also added empno in level 3 because I cannot join inner_emp.empno with outer_emp.empno without adding this into my query.

                       

                      Now my question is that is there any other way to do this query or the query that I wrote is the only possible way. Thanks

                       

                      Regards

                      Shu

                       

                       

                      • 8. Re: Invalid Identified
                        Frank Kulash

                        Hi, Shu,

                         

                         

                         

                        979596 wrote:

                         

                        ... Now my question is that is there any other way to do this query or the query that I wrote is the only possible way. ...

                         

                        There are always different ways to get the same results.  For a few very simple problems, you might be able to say that a given query is the best way, but for almost all real problems, there are multiple good ways to get the same results.

                         

                        You still haven't explained exactly what you want to do, or even what data you're using.  Is your emp table anything like scott.emp?  When you have mutliple rows with the same sal (e.g., WARD and MARTIN both have sal=1250) the query you posted gives indeterminate results.  (That is, it could say WARD has sss=1100 and MARTIN has sss=1250, but then again it could say WARD has sss=1250 and MARTIN has sss=1100.)  Is that really what you want?

                         

                        Here's one way to re-write your query:

                         

                        SELECT    empno, ename, job

                        ,         LAG ( CASE

                                            WHEN  sal > 2000

                                            THEN  999

                                            ELSE  sal

                                        END

                                      , 1

                                      , 0

                                      ) OVER (ORDER BY sal) AS sss

                        ,         sal            -- For debugging only

                        FROM      emp

                        ORDER BY  sal            -- For debugging only

                        ;

                        (I added 2 lines just to help see I was getting the right results.  You can remove them if you want.)

                        Notice that this gets around the problem of correlating to level N-2 by having everything on the same level, so there's no need to correlate at all.

                         

                        Another reasonable way to get the same results is:

                         

                        WITH    got_filteredsal       AS

                        (

                            SELECT  empno, ename, job, sal

                            ,       CASE

                                        WHEN  sal > 2000

                                        THEN  999

                                        ELSE  sal

                                    END      AS filteredsal

                            FROM    scott.emp

                        )

                        SELECT    empno, ename, job

                        ,         LAG (filteredsal, 1, 0) OVER (ORDER BY sal) AS sss

                        ,         sal       -- For debugging only

                        FROM      got_filteredsal

                        ORDER BY  sal       -- For debugging only

                        ;


                        Again, there's no problem about correlating to level N-2, because there are only 2 levels, and they're not correlated anyway (that is, the sub-query doesn't need any data from the parent query).

                        • 9. Re: Invalid Identified
                          Muzz

                          Thanks Frank. Now I understand the concept very clearly.. I think you should write an oracle book because you explain things in a simpler way. Thanks again

                          1 person found this helpful