Home » SQL & PL/SQL » SQL & PL/SQL » Inner query return different results when i wrap it up (Oracle 11g )
icon3.gif  Inner query return different results when i wrap it up [message #432945] Fri, 27 November 2009 09:34 Go to next message
bajrang
Messages: 4
Registered: November 2009
Junior Member
Hi

Below query result set doesnt match:--

select sys_connect_by_path() from a START WITH RNUM=1
CONNECT BY PRIOR a=a
AND PRIOR RNUM = RNUM -1


select * from (
select sys_connect_by_path() from a START WITH RNUM=1
CONNECT BY PRIOR a=a
AND PRIOR RNUM = RNUM -1)


Any one have idea on this?

Thanks in advance.
Re: Inner query return different results when i wrap it up [message #432946 is a reply to message #432945] Fri, 27 November 2009 09:48 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
You need to help us by following the Posting Guidelines as stated below.
http://www.orafaq.com/forum/t/88153/0/
Go to the URL above click the link "Posting Guidelines"
Go to the section labeled "Practice" & do as directed.


Please post actual results from both so we can see what you see.
Re: Inner query return different results when i wrap it up [message #432948 is a reply to message #432945] Fri, 27 November 2009 10:12 Go to previous messageGo to next message
pablolee
Messages: 2813
Registered: May 2007
Location: Scotland
Senior Member
Also, post actual query please (not one that s simplified to such an extent that it is no longer valid)
Re: Inner query return different results when i wrap it up [message #432949 is a reply to message #432945] Fri, 27 November 2009 10:18 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Also post your version with 4 decimals.
And post execution plans for both queries.

Regards
Michel
Re: Inner query return different results when i wrap it up [message #432956 is a reply to message #432945] Fri, 27 November 2009 11:07 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
a is table and also a column??!!!

As suggested by Pablolee Sir,
Quote:

Also, post actual query please (not one that s simplified to such an extent that it is no longer valid)

[Updated on: Fri, 27 November 2009 11:10]

Report message to a moderator

Re: Inner query return different results when i wrap it up [message #432958 is a reply to message #432956] Fri, 27 November 2009 11:27 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
a is table and also a column??!!!

Why not?

Regards
Michel
Re: Inner query return different results when i wrap it up [message #432960 is a reply to message #432958] Fri, 27 November 2009 12:23 Go to previous messageGo to next message
Its_me_ved
Messages: 979
Registered: October 2009
Location: India
Senior Member
Michel Cadot wrote on Fri, 27 November 2009 11:27
Quote:
a is table and also a column??!!!

Why not?

Regards
Michel


well, its not a good table design if you have

[Updated on: Fri, 27 November 2009 12:25]

Report message to a moderator

Re: Inner query return different results when i wrap it up [message #432962 is a reply to message #432960] Fri, 27 November 2009 12:37 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select owner, table_name, column_name from dba_tab_columns where table_name=column_name;
OWNER                          TABLE_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
SYS                            OID$                           OID$
SYS                            GLOBAL_NAME                    GLOBAL_NAME

A table containing only global names how is it a bad design that its name is GLOBAL_NAME and its column name too?

Regards
Michel

[Updated on: Fri, 27 November 2009 12:38]

Report message to a moderator

Re: Inner query return different results when i wrap it up [message #432977 is a reply to message #432956] Fri, 27 November 2009 18:09 Go to previous messageGo to next message
bajrang
Messages: 4
Registered: November 2009
Junior Member
No, a is not same , hv put it for reference only.

Query should be like:

select sys_connect_by_path() from table1 START WITH RNUM=1
CONNECT BY PRIOR a=a
AND PRIOR RNUM = RNUM -1


select * from (
select sys_connect_by_path() from table1 START WITH RNUM=1
CONNECT BY PRIOR a=a
AND PRIOR RNUM = RNUM -1)

Thanks for your responses.

I will post explain for both asap.
Re: Inner query return different results when i wrap it up [message #432981 is a reply to message #432977] Fri, 27 November 2009 20:24 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
Senior Member
It would be helpful if you provided DDL for tables involved.
It would be helpful if you provided DML for test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

Which statement give "correct" results & why is it so?
Re: Inner query return different results when i wrap it up [message #433036 is a reply to message #432981] Sun, 29 November 2009 04:50 Go to previous messageGo to next message
bajrang
Messages: 4
Registered: November 2009
Junior Member
DDL:--
create table A (bundle_id number,bundle_key number, del_flg number,catalog_key number);

create table B (bundle_key number, del_flg number,cat_digital_item_key number);



below is DML:---

This is Correct result:-

select max(substr(SYS_CONNECT_BY_PATH(BUNDLE_ID, ', '),3)) as IN_bundles,cat_digital_item_key from
(select a.bundle_id,B.CAT_DIGITAL_ITEM_KEY,
row_number() over (partition by cat_digital_item_key order by cat_digital_item_key) RNUM
from
(select * from A where del_flg=0) a,
(select * from B where del_flg=0) b
where a.catalog_key=56
and A.BUNDLE_KEY=B.BUNDLE_KEY )
START WITH RNUM=1
CONNECT BY PRIOR CAT_DIGITAL_ITEM_KEY=CAT_DIGITAL_ITEM_KEY
AND PRIOR RNUM = RNUM -1
group by cat_digital_item_key


Result:----

IN_BUNDLES CAT_DIGITAL_ITEM_KEY

86893 382
86880, 86891, 86892, 86893, 94942 650
125623 894


select in_bundles,cat_digital_item_key from (
select max(substr(SYS_CONNECT_BY_PATH(BUNDLE_ID, ', '),3)) as IN_bundles,cat_digital_item_key from
(select a.bundle_id,B.CAT_DIGITAL_ITEM_KEY,
row_number() over (partition by cat_digital_item_key order by cat_digital_item_key) RNUM
from
(select * from ods_bundles where del_flg=0) a,
(select * from ODS_BUNDLE_DIGI_ITEM_BDG where del_flg=0) b
where a.catalog_key=56
and A.BUNDLE_KEY=B.BUNDLE_KEY )
START WITH RNUM=1
CONNECT BY PRIOR CAT_DIGITAL_ITEM_KEY=CAT_DIGITAL_ITEM_KEY
AND PRIOR RNUM = RNUM -1
group by cat_digital_item_key
)

Result:---

IN_BUNDLES CAT_DIGITAL_ITEM_KEY

86880, 86893, 86891, 86880, 86892 650
125623 894
86893, 86893 382
Re: Inner query return different results when i wrap it up [message #433038 is a reply to message #433036] Sun, 29 November 2009 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Fri, 27 November 2009 17:18
Also post your version with 4 decimals.
And post execution plans for both queries.

Regards
Michel

Also read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Re: Inner query return different results when i wrap it up [message #433039 is a reply to message #432945] Sun, 29 November 2009 05:10 Go to previous messageGo to next message
bajrang
Messages: 4
Registered: November 2009
Junior Member
VERSION:-- Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production

           
        explain plan for
select max(substr(SYS_CONNECT_BY_PATH(BUNDLE_ID, ', '),3)) as IN_bundles,cat_digital_item_key from
(select bundle_id,cat_digital_item_key ,
row_number() over (partition by cat_digital_item_key order by cat_digital_item_key) RNUM
from sample1 ,sample2
where sample1.bundle_key=sample2.bundle_key)
START WITH RNUM=1
CONNECT BY PRIOR CAT_DIGITAL_ITEM_KEY=CAT_DIGITAL_ITEM_KEY 
AND PRIOR RNUM = RNUM -1
group by cat_digital_item_key

Plan hash value: 2298196759
 
------------------------------------------------------------
| Id  | Operation                                | Name    |
------------------------------------------------------------
|   0 | SELECT STATEMENT                         |         |
|   1 |  SORT GROUP BY                           |         |
|*  2 |   CONNECT BY NO FILTERING WITH START-WITH|         |
|   3 |    COUNT                                 |         |
|   4 |     VIEW                                 |         |
|   5 |      WINDOW SORT                         |         |
|   6 |       MERGE JOIN                         |         |
|   7 |        SORT JOIN                         |         |
|   8 |         TABLE ACCESS FULL                | SAMPLE2 |
|*  9 |        SORT JOIN                         |         |
|  10 |         TABLE ACCESS FULL                | SAMPLE1 |
------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("CAT_DIGITAL_ITEM_KEY"=PRIOR "CAT_DIGITAL_ITEM_KEY" AND 
              "RNUM"-1=PRIOR "RNUM")
       filter("RNUM"=1)
   9 - access("SAMPLE1"."BUNDLE_KEY"="SAMPLE2"."BUNDLE_KEY")
       filter("SAMPLE1"."BUNDLE_KEY"="SAMPLE2"."BUNDLE_KEY")
 
Note
-----
   - rule based optimizer used (consider using cbo)


explain plan for
select * from 
(

select max(substr(SYS_CONNECT_BY_PATH(BUNDLE_ID, ', '),3)) as IN_bundles,cat_digital_item_key from
(select bundle_id,cat_digital_item_key ,
row_number() over (partition by cat_digital_item_key order by cat_digital_item_key) RNUM
from sample1 ,sample2
where sample1.bundle_key=sample2.bundle_key)
START WITH RNUM=1
CONNECT BY PRIOR CAT_DIGITAL_ITEM_KEY=CAT_DIGITAL_ITEM_KEY 
AND PRIOR RNUM = RNUM -1
group by cat_digital_item_key


)


PLAN_TABLE_OUTPUT

Plan hash value: 2425689087
 
-------------------------------------------------------------
| Id  | Operation                                 | Name    |
-------------------------------------------------------------
|   0 | SELECT STATEMENT                          |         |
|   1 |  VIEW                                     |         |
|   2 |   SORT GROUP BY                           |         |
|*  3 |    CONNECT BY NO FILTERING WITH START-WITH|         |
|   4 |     COUNT                                 |         |
|   5 |      VIEW                                 |         |
|   6 |       WINDOW SORT                         |         |
|   7 |        MERGE JOIN                         |         |
|   8 |         SORT JOIN                         |         |
|   9 |          TABLE ACCESS FULL                | SAMPLE2 |
|* 10 |         SORT JOIN                         |         |
|  11 |          TABLE ACCESS FULL                | SAMPLE1 |
-------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("CAT_DIGITAL_ITEM_KEY"=PRIOR "CAT_DIGITAL_ITEM_KEY" AND 
              "RNUM"-1=PRIOR "RNUM")
       filter("RNUM"=1)
  10 - access("SAMPLE1"."BUNDLE_KEY"="SAMPLE2"."BUNDLE_KEY")
       filter("SAMPLE1"."BUNDLE_KEY"="SAMPLE2"."BUNDLE_KEY")
 
Note
-----
   - rule based optimizer used (consider using cbo)


[Mod-Edit: Frank added code-tags to improve readability, because JUST pointing people to the forum guide does not improve readability of existing posts]

[Updated on: Sun, 29 November 2009 07:56] by Moderator

Report message to a moderator

Re: Inner query return different results when i wrap it up [message #433040 is a reply to message #433039] Sun, 29 November 2009 05:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Sun, 29 November 2009 11:59
Michel Cadot wrote on Fri, 27 November 2009 17:18
Also post your version with 4 decimals.
And post execution plans for both queries.

Regards
Michel

Also read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.

Re: Inner query return different results when i wrap it up [message #433070 is a reply to message #432945] Sun, 29 November 2009 13:26 Go to previous messageGo to next message
Kevin Meade
Messages: 2098
Registered: December 1999
Location: Connecticut USA
Senior Member
You should never post code that does not work. Don't do it again.

However, if I fix your syntax error, I see no problem. Works for me.

[code]
SQL> create table a (a number,rnum number);

Table created.

SQL> insert into a values (1,1);

1 row created.

SQL> insert into a values (1,2);

1 row created.

SQL> insert into a values (1,3);

1 row created.

SQL> commit;

  1  select sys_connect_by_path(rnum,',') from a START WITH RNUM=1
  2  CONNECT BY PRIOR a=a
  3* AND PRIOR RNUM = RNUM -1
SQL> /

SYS_CONNECT_BY_PATH(RNUM,',')
---------------------------------------------------------------------------
,1
,1,2
,1,2,3

SQL> select * from (
  2  select sys_connect_by_path(rnum,',') from a START WITH RNUM=1
  3  CONNECT BY PRIOR a=a
  4  AND PRIOR RNUM = RNUM -1
  5  )
  6  /

SYS_CONNECT_BY_PATH(RNUM,',')
----------------------------------------------------------------------
,1
,1,2
,1,2,3


SQL> select * from v$version;

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

Good luck, Kevin
Re: Inner query return different results when i wrap it up [message #433073 is a reply to message #433039] Sun, 29 November 2009 13:45 Go to previous message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Note
-----
- rule based optimizer used (consider using cbo)

11g and you still use RBO!
Follow the advice and I bet you will no more have this problem.
I'm not suprised you have a problem with CONNECT BY (RBO) code and analytic functions when the code of this former was written before the latter existed.

Regards
Michel
Previous Topic: rows between 2 times
Next Topic: String to Number Problem
Goto Forum:
  


Current Time: Wed Sep 28 16:02:48 CDT 2016

Total time taken to generate the page: 0.15429 seconds