Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2011
    Posts
    1

    Unanswered: nested selects and indentifier scope

    I am finding an issue in nesting select statements. And I am interpreting the problem as a sort of variable scope visibility but I am not 100% sure.

    The following query works fine:

    -------------------------------
    SELECT
    NAME,
    PASSPORT,
    BIRTH_FK,
    (
    SELECT
    CASE
    WHEN DTYPE = 'foreign'
    THEN
    ( ' do something ' )
    ELSE
    (
    SELECT param1 FROM thisTable
    WHERE ID = PROBLEM_ID
    WHERE ROWNUM <= 1
    )
    END

    FROM
    PLACE_TABLE
    WHERE
    ID = BIRTH_FK

    ),
    ALLIANCE,
    FROM_DATE

    FROM
    firtsTable
    WHERE
    FK = 419
    ORDER BY
    FROM_DATE DESC
    -----------------------------

    When I apply this modification to the internal select
    "SELECT param1 FROM thisTable WHERE ID = PROBLEM_ID WHERE ROWNUM <= 1 " as in the following example I get an error. ORA-00904: "PROBLEM_ID ": invalid identifier


    ----------------------------
    SELECT
    NAME,
    PASSPORT,
    BIRTH_FK,
    (
    SELECT
    CASE
    WHEN DTYPE = 'foreign'
    THEN
    ( ' do something ' )
    ELSE
    (SELECT param1
    FROM
    (SELECT param1 FROM thisTable
    WHERE ID = PROBLEM_ID
    ORDER BY somedate DESC)
    WHERE ROWNUM <= 1
    )
    END

    FROM
    PLACE_TABLE
    WHERE
    ID = BIRTH_FK

    ),
    ALLIANCE,
    FROM_DATE

    FROM
    firtsTable
    WHERE
    FK = 419
    ORDER BY
    FROM_DATE DESC
    ---------------------------------

    The problem is that in the first query the identifier PROBLEM_ID is found from the previous outer select statement and I get the result I hope for.

    In the second query, since I need to perform a "ORDER BY somedate DESC" I create another nested SELECT level and the scope of PROBLEM_ID is lost.
    In fact I receive the error Error: ORA-00904: "PROBLEM_ID ": invalid identifier.

    Is there a way to ensure that the "scope" of PROBLEM_ID is kept in the nested query?

    Is the problem somewhere else?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >WHERE ID = PROBLEM_ID
    >WHERE ROWNUM <= 1

    posted code throws syntax error near above lines
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Try this:
    Code:
    select name, passport, birth_fk,
           ( select case
                      when dtype = 'foreign' then 'do something'
                      else ( select param1
                              from ( select param1
                                      from  thistable
                                     where  id       =  problem_id
                                      and   rownum  <=  1
                                     order by somedate desc ) )
                    end
              from place_table
             where id = birth_fk ), alliance, from_date
     from  firtstable
    where  fk = 419
    order by from_date desc

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •