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 Go to next message
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 #432237 is a reply to message #432235] Mon, 23 November 2009 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
What do you ant exactly?

Regards
Michel
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 Go to previous messageGo to next message
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 #432240 is a reply to message #432238] Mon, 23 November 2009 03:19 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Quote:
i am getting this error em.main_seq is invalid identifier.


please copy paste your error from sql plus
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 Go to previous messageGo to next message
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 #432245 is a reply to message #432237] Mon, 23 November 2009 03:30 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Michel wrote:
What do you ant exactly?


Michel you just used a short form "ant"

[Updated on: Mon, 23 November 2009 03:31]

Report message to a moderator

Re: how to get outer table value in the nested subquery [message #432246 is a reply to message #432235] Mon, 23 November 2009 03:33 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
still i am getting same error.

'ORA-00904:em.main_seq is invalid identifier.'
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 Go to previous messageGo to next message
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 #432249 is a reply to message #432245] Mon, 23 November 2009 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
ayush_anand wrote on Mon, 23 November 2009 10:30
Michel wrote:
What do you ant exactly?


Michel you just used a short form "ant"

Just a missing "w" at "ant".
When I posted this OP's post only contains the query.

Regards
Michel

Re: how to get outer table value in the nested subquery [message #432250 is a reply to message #432249] Mon, 23 November 2009 03:44 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
I thought Quote:
What do you ant"icipate" exactly?
Smile
Re: how to get outer table value in the nested subquery [message #432381 is a reply to message #432235] Mon, 23 November 2009 22:23 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
"CORE 10.2.0.4.0 Production"
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
Re: how to get outer table value in the nested subquery [message #432390 is a reply to message #432381] Mon, 23 November 2009 23:00 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
Please copy paste your SQL session where you getting this error?
OR you getting this error from any tool??
Re: how to get outer table value in the nested subquery [message #432391 is a reply to message #432235] Mon, 23 November 2009 23:07 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
i am using PL/SQL developer tool.if you insert this data in emp table and then execute this query then you can see this error.
Re: how to get outer table value in the nested subquery [message #432396 is a reply to message #432391] Mon, 23 November 2009 23:31 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
If you post us the create table and insert statements then we can do it.
And if you use SQL*Plus and copy and paste what you did, you can directly see it and immediatly gives you an answer.

Regards
Michel
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 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
If you want from my side Wink
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 Go to previous messageGo to next message
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 #432401 is a reply to message #432399] Mon, 23 November 2009 23:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
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.

Regards
Michel
Re: how to get outer table value in the nested subquery [message #432402 is a reply to message #432235] Mon, 23 November 2009 23:45 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
Micheal,

i got the root cause.how to modify the query to get the required result.
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 Go to previous messageGo to next message
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 #432427 is a reply to message #432403] Tue, 24 November 2009 03:19 Go to previous messageGo to next message
Michel Cadot
Messages: 68733
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
It is "working" for you because you have a 10.2.0.1 and this one is buggy on this point.
This has been fixed in 10.2.0.2.
10.2.0.1 is the ONLY version in which it "works".

See this discussion I had with Tom Kyte 4 years ago:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:48704116042682#50678380017237

And the follow-on, 7 months later when 10.2.0.1 was delivered:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:48704116042682#64283169182746

Which was ended with an official answer from Oracle at:
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:48704116042682#65718645306451

Regards
Michel
Re: how to get outer table value in the nested subquery [message #432434 is a reply to message #432427] Tue, 24 November 2009 04:03 Go to previous messageGo to next message
ayush_anand
Messages: 417
Registered: November 2008
Senior Member
wow you have every answer Wink
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 Go to previous message
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?
Previous Topic: Execute an external application from PL/SQL
Next Topic: oracle 9i
Goto Forum:
  


Current Time: Tue Feb 11 04:51:13 CST 2025