Home » SQL & PL/SQL » SQL & PL/SQL » subquery (oracle10.2)
subquery [message #449152] Sat, 27 March 2010 08:31 Go to next message
zxx2403
Messages: 12
Registered: November 2006
Location: china
Junior Member

Hi all
I have query
as following

SELECT *
FROM (SELECT DISTINCT SUBSTR(TB_ISP_PLN.ISP_MONTH, 1, 4) ||
SUBSTR(TB_ISP_PLN.ISP_WEEK, 1, 2) AS LC_WEEK,
TB_ISP_PLN.PR_ID AS PR_ID,
TB_PROD.PR_NAME AS PR_NAME
FROM TB_ISP_PLN, TB_PROD
WHERE TB_PROD.AD_ID = '01'
AND TB_PROD.SD_ID = '10'
AND TB_PROD.ST_ID = '01'
AND TB_PROD.SA_ID = '01'
AND TB_PROD.SC_ID = '20204401'
AND TB_PROD.PS_ID = '54'
AND SUBSTR(TB_ISP_PLN.ISP_MONTH, 1, 4) ||
SUBSTR(TB_ISP_PLN.ISP_WEEK, 1, 2) = '201016'
AND TB_ISP_PLN.ISP_WEEK NOT IN ('S', 'R', 'L')
AND TB_ISP_PLN.PR_ID = TB_PROD.PR_ID) PLN,
TB_LINECAP CAP,
TB_LINE TL,
TB_VERSION TV
WHERE PLN.PR_ID = CAP.PR_ID
AND PLN.LC_WEEK = CAP.LC_WEEK
AND CAP.LN_ID = (SELECT LN_ID
FROM (SELECT LN_ID, ROWNUM ROW_NUM
FROM TB_LINECAP
WHERE PR_ID = PLN.PR_ID AND LC_WEEK = PLN.LC_WEEK ORDER BY LC_LEVEL)
WHERE ROW_NUM < 2)
AND TL.LN_ID = CAP.LN_ID
AND TV.CUR_FLAG = 'Y'

why the inner query don't get the main query value

PLN.PR_ID AND LC_WEEK = PLN.LC_WEEK

thanks for you helping

best regards
Re: subquery [message #449155 is a reply to message #449152] Sat, 27 March 2010 09:28 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Beacuse oracle doesn't let you. You can only reference tables that are one level removed from the current select, you're trying to reference one that is two levels removed.

And next time post the error message, makes it a lot easier to work out what the problem is. Also please use code tags for queries - see the orafaq forum guide if you're not sure how.

[Updated on: Sat, 27 March 2010 09:29]

Report message to a moderator

Re: subquery [message #449163 is a reply to message #449152] Sat, 27 March 2010 10:14 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
1/ Please follow Posting Guidelines - OraFAQ Forum Guide.
2/ Please provided DDL (CREATE TABLE ......) for ALL tables involved.
3/ Please provided DML (INSERT INTO .......) for Test data.
4/ Please provided expected/desired results.
5/ Please Preview Message / Spell-Check Before Posting.
Re: subquery [message #449165 is a reply to message #449152] Sat, 27 March 2010 10:58 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
have a look at the sql WITH clause. You may be able to use it to get around this limitation. It is a better way to write sql anyway.

Consider the following translation of your original code:

with
   pln as (
            SELECT DISTINCT Substr(tb_isp_pln.isp_month,1,4) 
                                    ||Substr(tb_isp_pln.isp_week,1,2) AS lc_week, 
                                    tb_isp_pln.pr_id                  AS pr_id, 
                                    tb_prod.pr_name                   AS pr_name 
                    FROM   tb_isp_pln, 
                           tb_prod 
                    WHERE  tb_prod.ad_id = '01' 
                           AND tb_prod.sd_id = '10' 
                           AND tb_prod.st_id = '01' 
                           AND tb_prod.sa_id = '01' 
                           AND tb_prod.sc_id = '20204401' 
                           AND tb_prod.ps_id = '54' 
                           AND Substr(tb_isp_pln.isp_month,1,4)||Substr(tb_isp_pln.isp_week,1,2) = '201016' 
                           AND tb_isp_pln.isp_week NOT IN ('S','R','L') 
                           AND tb_isp_pln.pr_id = tb_prod.pr_id
           )
  , linecap as (
                 SELECT   ln_id, 
                          ROWNUM row_num 
                 FROM     tb_linecap 
                 WHERE    pr_id = pln.pr_id 
                 AND      lc_week = pln.lc_week 
                 ORDER BY lc_level
               )
  , lnid as (
              select ln_id
              from linecap
              where rownum < 2
            )
  , result as (
                SELECT cap.*,tl.*,tv.*
                FROM   lnid,
                       tb_linecap cap, 
                       tb_line tl, 
                       tb_version tv 
                WHERE  pln.pr_id = cap.pr_id 
                       AND pln.lc_week = cap.lc_week 
                       AND cap.ln_id = lnid.ln_id
                       AND tl.ln_id = cap.ln_id 
                       AND tv.cur_flag = 'Y' 
              )
select *
from result
/


Kevin



Previous Topic: Number datatype size cant decreased..Why??????
Next Topic: Procedure error
Goto Forum:
  


Current Time: Fri Dec 09 11:51:33 CST 2016

Total time taken to generate the page: 0.23626 seconds