Home » SQL & PL/SQL » SQL & PL/SQL » how to get outer table value in the nested subquery
how to get outer table value in the nested subquery [message #432235] |
Mon, 23 November 2009 03:03  |
chaituu
Messages: 115 Registered: June 2008
|
Senior Member |
|
|
Quote:
if you remove subquery then only empid=5555 will come that is correct.if i add subquery to get the invoice_id for that empid then i am getting this error em.main_seq is invalid identifier.i know that outer table value is NOT coming in the subquery.how to modify this query to get invoice_id.
SELECT EMPID,
(SELECT DISTINCT V.INVOICE_ID
FROM (SELECT INVOICE_ID,
ROW_NUMBER() OVER(PARTITION BY E.EMPID, E.MAIN_SEQ ORDER BY E.MAIN_SEQ) RANK
FROM EMP E
WHERE E.EMPID = EM.EMPID
AND E.MAIN_SEQ = EM.MAIN_SEQ
AND E.INVOICE_ID IS NOT NULL) V
WHERE RANK = 1)
FROM EMP EM
WHERE 1 =
(SELECT COUNT(DISTINCT E.EXCHANGERATE)
FROM EMP E
WHERE EM.EMPID = E.EMPID
AND EM.MAIN_SEQ = E.MAIN_SEQ
GROUP BY E.EMPID, E.MAIN_SEQ
HAVING SUM(NVL(E.AMOUNT, 0)) <> 0 AND COUNT(E.DEATILSEQ) = 3);
-
Attachment: emp.sql
(Size: 1.58KB, Downloaded 849 times)
[Updated on: Mon, 23 November 2009 03:09] Report message to a moderator
|
|
|
|
Re: how to get outer table value in the nested subquery [message #432238 is a reply to message #432237] |
Mon, 23 November 2009 03:11   |
chaituu
Messages: 115 Registered: June 2008
|
Senior Member |
|
|
Quote:
if you remove subquery then only empid=5555 will come that is correct.if i add subquery to get the invoice_id for that empid then i am getting this error em.main_seq is invalid identifier.i know that outer table value is NOT coming in the subquery.how to modify this query to get invoice_id.
how to get invoice id for that empid where invoiceid is not null.
|
|
|
|
Re: how to get outer table value in the nested subquery [message #432243 is a reply to message #432240] |
Mon, 23 November 2009 03:25   |
ayush_anand
Messages: 417 Registered: November 2008
|
Senior Member |
|
|
SQL> SELECT EMPID,
2 (SELECT DISTINCT V.INVOICE_ID
3 FROM (SELECT INVOICE_ID,
4 ROW_NUMBER() OVER(PARTITION BY E.EMPID, E.MAIN_SEQ ORDER BY E.MAIN_SEQ)
RANK
5 FROM EMP E
6 WHERE E.EMPID = EM.EMPID
7 AND E.MAIN_SEQ = EM.MAIN_SEQ
8 AND E.INVOICE_ID IS NOT NULL) V
9 WHERE RANK = 1) a
10 FROM EMP EM
11 WHERE 1 =
12 (SELECT COUNT(DISTINCT E.EXCHANGERATE)
13 FROM EMP E
14 WHERE EM.EMPID = E.EMPID
15 AND EM.MAIN_SEQ = E.MAIN_SEQ
16 GROUP BY E.EMPID, E.MAIN_SEQ
17 HAVING SUM(NVL(E.AMOUNT, 0)) <> 0 AND COUNT(E.DEATILSEQ) = 3);
(SELECT DISTINCT V.INVOICE_ID
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
this is the error I am getting in your query
please modify it to return only one row
SQL> SELECT EMPID,
2 (SELECT DISTINCT V.INVOICE_ID
3 FROM (SELECT INVOICE_ID,
4 ROW_NUMBER() OVER(PARTITION BY E.EMPID, E.MAIN_SEQ ORDER BY E.MAIN_SEQ)
RANK
5 FROM EMP E
6 WHERE E.EMPID = EM.EMPID
7 AND E.MAIN_SEQ = EM.MAIN_SEQ
8 AND E.INVOICE_ID IS NOT NULL) V
9 WHERE RANK = 1 and rownum <2) a
10 FROM EMP EM
11 WHERE 1 =
12 (SELECT COUNT(DISTINCT E.EXCHANGERATE)
13 FROM EMP E
14 WHERE EM.EMPID = E.EMPID
15 AND EM.MAIN_SEQ = E.MAIN_SEQ
16 GROUP BY E.EMPID, E.MAIN_SEQ
17 HAVING SUM(NVL(E.AMOUNT, 0)) <> 0 AND COUNT(E.DEATILSEQ) = 3);
EMPID A
--------- -------------------------------------------------------------
55555 INV1
55555 INV1
55555 INV1
[Updated on: Mon, 23 November 2009 03:28] Report message to a moderator
|
|
|
|
|
Re: how to get outer table value in the nested subquery [message #432247 is a reply to message #432246] |
Mon, 23 November 2009 03:34   |
ayush_anand
Messages: 417 Registered: November 2008
|
Senior Member |
|
|
do this and post
SQL> select banner from v$version
2 /
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production
|
|
|
|
|
|
|
|
|
Re: how to get outer table value in the nested subquery [message #432398 is a reply to message #432396] |
Mon, 23 November 2009 23:33   |
ayush_anand
Messages: 417 Registered: November 2008
|
Senior Member |
|
|
If you want from my side 
SQL> create table emp
2 (EMPID number, MAIN_SEQ number, DEATILSEQ number, AMOUNT number, EXCHANGERATE number, INVOICE_ID varchar2(100));
Table created.
SQL> insert into EMP (EMPID, MAIN_SEQ, DEATILSEQ, AMOUNT, EXCHANGERATE, INVOICE_ID)
2 values (3333, 7, 1, -130.6, 67.66, null);
1 row created.
SQL> insert into EMP (EMPID, MAIN_SEQ, DEATILSEQ, AMOUNT, EXCHANGERATE, INVOICE_ID)
2 values (3333, 7, 2, 0, 67.66, null);
1 row created.
SQL> insert into EMP (EMPID, MAIN_SEQ, DEATILSEQ, AMOUNT, EXCHANGERATE, INVOICE_ID)
2 values (3333, 7, 3, -130.6, 67.66, 'INV1');
1 row created.
SQL> insert into EMP (EMPID, MAIN_SEQ, DEATILSEQ, AMOUNT, EXCHANGERATE, INVOICE_ID)
2 values (44444, 8, 1, 1, 1, null);
1 row created.
SQL> insert into EMP (EMPID, MAIN_SEQ, DEATILSEQ, AMOUNT, EXCHANGERATE, INVOICE_ID)
2 values (44444, 8, 2, -1, 1, null);
1 row created.
SQL> insert into EMP (EMPID, MAIN_SEQ, DEATILSEQ, AMOUNT, EXCHANGERATE, INVOICE_ID)
2 values (44444, 8, 3, 0, 1, 'INV2');
1 row created.
SQL> insert into EMP (EMPID, MAIN_SEQ, DEATILSEQ, AMOUNT, EXCHANGERATE, INVOICE_ID)
2 values (55555, 2, 1, 4, 1, null);
1 row created.
SQL> insert into EMP (EMPID, MAIN_SEQ, DEATILSEQ, AMOUNT, EXCHANGERATE, INVOICE_ID)
2 values (55555, 2, 2, 24, 1, null);
1 row created.
SQL> insert into EMP (EMPID, MAIN_SEQ, DEATILSEQ, AMOUNT, EXCHANGERATE, INVOICE_ID)
2 values (55555, 2, 3, 24, 1, 'INV3');
1 row created.
SQL> insert into EMP (EMPID, MAIN_SEQ, DEATILSEQ, AMOUNT, EXCHANGERATE, INVOICE_ID)
2 values (3333, 7, 4, 1, 68.9, null);
1 row created.
SQL> commit;
Commit complete.
|
|
|
Re: how to get outer table value in the nested subquery [message #432399 is a reply to message #432235] |
Mon, 23 November 2009 23:36   |
chaituu
Messages: 115 Registered: June 2008
|
Senior Member |
|
|
create table EMP
(
EMPID NUMBER,
MAIN_SEQ NUMBER,
DEATILSEQ NUMBER,
AMOUNT NUMBER(10,2),
EXCHANGERATE NUMBER(10,2),
INVOICE_ID VARCHAR2(15)
);
here is the table structure and data is already attached in my 1st post.
SQL> SELECT EMPID,
2 (SELECT DISTINCT V.INVOICE_ID
3 FROM (SELECT INVOICE_ID,
4 ROW_NUMBER() OVER(PARTITION BY E.EMPID, E.MAIN_SEQ ORDER BY E.MAIN_SEQ)
RANK
5 FROM EMP E
6 WHERE E.EMPID = EM.EMPID
7 AND E.MAIN_SEQ = EM.MAIN_SEQ
8 AND E.INVOICE_ID IS NOT NULL) V
9 WHERE RANK = 1)
10 FROM EMP EM
11 WHERE 1 =
12 (SELECT COUNT(DISTINCT E.EXCHANGERATE)
13 FROM EMP E
14 WHERE EM.EMPID = E.EMPID
15 AND EM.MAIN_SEQ = E.MAIN_SEQ
16 GROUP BY E.EMPID, E.MAIN_SEQ
17 HAVING SUM(NVL(E.AMOUNT, 0)) <> 0 AND COUNT(E.DEATILSEQ) = 3);
AND E.MAIN_SEQ = EM.MAIN_SEQ
*
ERROR at line 7:
ORA-00904: "EM"."MAIN_SEQ": invalid identifier
|
|
|
|
|
Re: how to get outer table value in the nested subquery [message #432403 is a reply to message #432401] |
Mon, 23 November 2009 23:46   |
ayush_anand
Messages: 417 Registered: November 2008
|
Senior Member |
|
|
'Michel wrote'You can only reference a table that is one level up.
SQL> SELECT EMPID,
2 (SELECT DISTINCT V.INVOICE_ID
3 FROM (SELECT INVOICE_ID,
4 ROW_NUMBER() OVER(PARTITION BY E.EMPID, E.MAIN_SEQ ORDER BY E.MAIN_SEQ)
RANK
5 FROM EMP E
6 WHERE E.EMPID = EM.EMPID
7 AND E.MAIN_SEQ = EM.MAIN_SEQ
8 AND E.INVOICE_ID IS NOT NULL) V
9 WHERE RANK = 1)
10 FROM EMP EM
11 WHERE 1 =
12 (SELECT COUNT(DISTINCT E.EXCHANGERATE)
13 FROM EMP E
14 WHERE EM.EMPID = E.EMPID
15 AND EM.MAIN_SEQ = E.MAIN_SEQ
16 GROUP BY E.EMPID, E.MAIN_SEQ
17 HAVING SUM(NVL(E.AMOUNT, 0)) <> 0 AND COUNT(E.DEATILSEQ) = 3);
(SELECT DISTINCT V.INVOICE_ID
*
ERROR at line 2:
ORA-01427: single-row subquery returns more than one row
How it is working for me Michel
[Updated on: Mon, 23 November 2009 23:48] Report message to a moderator
|
|
|
|
|
Re: how to get outer table value in the nested subquery [message #432436 is a reply to message #432235] |
Tue, 24 November 2009 04:11  |
chaituu
Messages: 115 Registered: June 2008
|
Senior Member |
|
|
Quote:
You can only reference a table that is one level up.
In your case EM is 2 levels above the query that references it and so it is invisible for it.
what i understood was my query will work only in 10.2.0.1 version not in 10.2.0.4 version.
to work in my version is there any way to modify the query?
|
|
|
Goto Forum:
Current Time: Tue Feb 11 04:51:13 CST 2025
|