1 Reply Latest reply: Nov 2, 2012 6:25 PM by rp0428 RSS

    Pls Help : Union with outer reference

    972254
      Hi ,
      I have a simple and but at the same time complicated problem with union in my query :
      Please see below sql :
      The inner query with union command can not access to the outer table task_history taskHistory
      I receive below error message :

      **"ORA-00904: "TASKHISTORY"."TIME_STAMP": invalid identifier**
      **00904. 00000 - "%s: invalid identifier"**
      ***Cause:**
      ***Action:"**

      would you please help me to find out what I am doing wrong?

      thanks,


      select
      (select max(closerDate) from
      (
      select cast(max(time_stamp) as date) as closerDate
      from task_history taskHistory_2, rx_tx rxtx_2
      where
      taskHistory_2.task_name IN('order_entry', 'data_entry', 'data_verification','fill','product_verification' )
      and rxtx_2.rx_summary_id = 65656
      and taskHistory_2.rx_tx_id <= 332323
      and rxtx_2.id = taskHistory_2.rx_tx_id
      and rxtx_2.refill_number = 0
      and taskHistory_2.task_action = 'complete'
      and taskHistory_2.time_stamp < taskHistory.time_stamp

      union all

      select cast(max(log1.timestamp) as date) as closerDate
      from AUDIT_LOG log1,
      Line_item li
      where li.order_entry_id= target_record_id and
      TARGET_TABLE_NAME = 'OrderEntry' AND
      TARGET_COLUMN_NAME = 'pickupType' AND
      li.id = 3232
      )
      ) from task_history taskHistory

      Edited by: 969251 on Nov 2, 2012 2:10 PM
        • 1. Re: Pls Help : Union with outer reference
          rp0428
          Welcome to the forum!
          >
          would you please help me to find out what I am doing wrong?
          >
          Well the first thing you are doing wrong is posting in the wrong forum.

          This is the SQL Developer forum and, as you can see from the title, is 'Not for general SQL/PLSQL questions'.

          Please repost the question in the SQL and PL/SQL forum.
          PL/SQL

          When you post use \
           tags on the line before and on the line after your code to preserve the formatting. See the FAQ for details.
          
          You also need to provide your 4 digit Oracle version (result of SELECT * FROM V$VERSION) every time you post.
          {quote}
          The inner query with union command can not access to the outer table task_history taskHistory 
          I receive below error message :
          
          **"ORA-00904: "TASKHISTORY"."TIME_STAMP": invalid identifier**
          {quote}
          What else would you expect? The inner query has no knowledge of the outer query. How could it? The outer query doesn't exist until it processes both of the inner queries.
          
          Each query of the union all needs to be a completely separate query. You should be able to run each query by itself and get a result set.
          If you can't the query is invalid. Since the only table involved that has TIME_STAMP is the task_history table you need to use that table.