Oracle-L: query question ( top 1) ORA-00904

From: Teresita Castro <Teresita.Castro_at_s-martmx.com>
Date: Mon, 10 Nov 2003 14:39:32 -0800
Message-ID: <F001.005D63DB.20031110143932@fatcity.com>

Hi!!
I am doing a query that have to result the top 1 from a nother query. When I run the query it returns me this error: ORA-00904: "TB_EDORESULT"."ITEM":invalid idenfier

First I was thinking that was because something was misspell, so I put comment (--) infront of ictrans.item = TB_EDORESULT.ITEM
but the error continue now in this part: ictrans.location = tb_edoresult.location

It seem like if the TB_EDORESULT is not recognize when I made a select from another select ( I hope you undestand my English)

select TB_EDORESULT.COMPANY, tb_edoresult.location,tb_edoresult.select_id, TB_EDORESULT.FECHA, ITEMMAST.DESCRIPTION, TB_EDORESULT.ITEM ,

(select x from

      (select row_number() OVER (  PARTITION BY item  ORDER BY tran_counter DESC ) the_row, 
         ictrans.quantity + ictrans.soh_qty as x 
     from ictrans 
       where  ictrans.ictset2_ss_sw='Y' and ictrans.company =TB_EDORESULT.COMPANY 
          and ictrans.location = tb_edoresult.location and ictrans.item = TB_EDORESULT.ITEM  --error here
          and ictrans.update_date < TO_DATE('10/16/2003','MM/DD/YYYY')

) where the_row=1) xx

FROM TB_EDORESULT INNER JOIN ITEMMAST ON TB_EDORESULT.ITEM=ITEMMAST.ITEM WHERE TB_EDORESULT.COMPANY= 2000 AND TB_EDORESULT.LOCATION='TJU01' AND TB_EDORESULT.FECHA = TO_DATE('10/22/2003','MM/DD/YYYY') AND ITEMMAST.ITEM_GROUP='SMART' and SUBSTR(ITEMMAST.ITEM,1,3)<>'070'

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Teresita Castro
INET: Teresita.Castro_at_s-martmx.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon Nov 10 2003 - 16:39:32 CST