Home » SQL & PL/SQL » SQL & PL/SQL » passing a variable in sub query
passing a variable in sub query [message #198567] Tue, 17 October 2006 12:48 Go to next message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
I have the following query where I'm trying to pass the HT variable to the outer query and get the following error:

ORA-00904: "RES1"."HT": invalid identifier

How can I get this variable passed?

Thanks,
Stan

      SELECT
         RES1.pseq,
         RES1.HT AS HT1,
         DECODE(RES1.WTN,1,RES1.WT) AS WT1
      FROM
        (SELECT /*+ ORDERED */
           p.pseq,
           CASE
              WHEN pr1.labseq IN (99, 173, 362, 054, 1752)
              THEN pr1.resval
           END AS WT,
           ROW_NUMBER() OVER (PARTITION BY p.pseq ORDER BY pr1.perf_ddt DESC) AS WTN
        FROM
           pat p,
           pat_rts pr1,
           (select * from pat_rts where pat_rts.labseq IN (1234,5678)) HT
        WHERE
                    p.pseq = pr1.pseq
           AND HT.pseq = p.pseq
           AND pr1.labseq IN (99,173,362,054,1752)
           AND p.build IN ('Z')
           AND pr1.resval IS NOT NULL) RES1
Re: passing a variable in sub query [message #198571 is a reply to message #198567] Tue, 17 October 2006 13:32 Go to previous messageGo to next message
Nirmala
Messages: 43
Registered: October 2004
Member
There is no column called HT in the subquery RES1. If you need columns of the pat_rts table you need to select them in your sub query and then select them in the main query. something like this
      SELECT
         RES1.pseq,
         RES1.pseq AS HT1,
         RES1.labseq AS HT1,
         DECODE(RES1.WTN,1,RES1.WT) AS WT1
      FROM
        (SELECT /*+ ORDERED */
           p.pseq,
           CASE
              WHEN pr1.labseq IN (99, 173, 362, 054, 1752)
              THEN pr1.resval
           END AS WT,
           ht.pseq,
           ht.labseq,
           ROW_NUMBER() OVER (PARTITION BY p.pseq ORDER BY pr1.perf_ddt DESC) AS WTN
        FROM
           pat p,
           pat_rts pr1,
           (select * from pat_rts where pat_rts.labseq IN (1234,5678)) HT
        WHERE
                    p.pseq = pr1.pseq
           AND HT.pseq = p.pseq
           AND pr1.labseq IN (99,173,362,054,1752)
           AND p.build IN ('Z')
           AND pr1.resval IS NOT NULL) RES1
Re: passing a variable in sub query [message #198572 is a reply to message #198567] Tue, 17 October 2006 13:47 Go to previous message
staann56
Messages: 136
Registered: May 2006
Location: atlanta
Senior Member
Thank you....what an oversight on my part.

Stan
Previous Topic: -1247467259ORA-12154:TNS:Could not resolved service name
Next Topic: Create field of CLOB datatype with a size of 30000
Goto Forum:
  


Current Time: Thu Dec 08 18:10:23 CST 2016

Total time taken to generate the page: 0.26278 seconds