Home » SQL & PL/SQL » SQL & PL/SQL » Help needed for SQL Query (merged)
Help needed for SQL Query (merged) [message #393142] Fri, 20 March 2009 09:05 Go to next message
sankaram
Messages: 6
Registered: November 2004
Junior Member
Hi All,

I have data like this in the table

C1 C2 GROUP
1 2 100
1 3 100
2 4 100
2 5 200
5 10 200
3 11 300

I need output as follows
C1 C2
1 2
1 3
1 4 (This is not required in the output as this relation in
the same group 100)
2 4
1 5 (1->2, 2->5, so 1->5 Required because 2,5 is in 200 group)
1 10
2 5
2 10(This is not required in the output as this relation in
the same group 200)
3 11

Can you suggest me how to get this through query
Re: Help needed for SQL Query (merged) [message #393144 is a reply to message #393142] Fri, 20 March 2009 09:11 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please 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 (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Explain with words what are the requirements.
Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel

[Updated on: Fri, 20 March 2009 09:11]

Report message to a moderator

Re: Help needed for SQL Query (merged) [message #393158 is a reply to message #393142] Fri, 20 March 2009 10:15 Go to previous messageGo to next message
sankaram
Messages: 6
Registered: November 2004
Junior Member
CREATE TABLE TESTING
(C1 NUMBER,
 C2 NUMBER,
 GRP NUMBER)
/

INSERT INTO TESTING VALUES (1,2,100);
INSERT INTO TESTING VALUES (1,3,100);
INSERT INTO TESTING VALUES (2,4,100);
INSERT INTO TESTING VALUES (2,5,200);
INSERT INTO TESTING VALUES (5,10,200);
INSERT INTO TESTING VALUES (3,11,200);

I want output as follows
C1 C2
1 2
1 3
1 5
1 10
2 4
2 5
3 11
5 10

Logic is C2 is output of C1, For a give Grp value lest say 100, check the output of C1(ie C2) is input to in other groups (other than 100, i.e 200)
If yes then we need to display the result in the Output

For example
C1 C2 GRP
1  2  100
2  4  100
2  5  200
5  10 200

Output should be
1 2
1 5
1 10
2 4
2 5
5 10

I hope I have explained you.

[Updated on: Fri, 20 March 2009 11:18] by Moderator

Report message to a moderator

Re: Help needed for SQL Query (merged) [message #393166 is a reply to message #393158] Fri, 20 March 2009 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't format and align the column results, see how it is easy just adding the code tags (read guide).
You didn't tell us what is your Oracle version.

Have a look at CONNECT BY and hierarchial queries in SQL Reference.

And should be the result for the test case you gave?

Regards
Michel

[Updated on: Fri, 20 March 2009 11:29]

Report message to a moderator

Re: Help needed for SQL Query (merged) [message #393171 is a reply to message #393158] Fri, 20 March 2009 11:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select * from testing;
        C1         C2        GRP
---------- ---------- ----------
         1          2        100
         2          4        100
         2          5        200
         5         10        200

4 rows selected.

SQL> select connect_by_root c1 c1, c2
  2  from testing
  3  connect by c1 = prior c2 and grp != connect_by_root grp
  4  /
        C1         C2
---------- ----------
         1          2
         1          5
         1         10
         2          4
         2          5
         5         10

6 rows selected.

Regards
Michel
Re: Help needed for SQL Query (merged) [message #393470 is a reply to message #393166] Mon, 23 March 2009 00:13 Go to previous messageGo to next message
sankaram
Messages: 6
Registered: November 2004
Junior Member
Thanks Michel, My Oracle Version is 9.2.0.5, next time, I will format and align the content before posting.

Re: Help needed for SQL Query (merged) [message #393490 is a reply to message #393166] Mon, 23 March 2009 01:39 Go to previous messageGo to next message
sankaram
Messages: 6
Registered: November 2004
Junior Member
Hi Michel,

This Query is not working in Oracle 9.2.0.5

Re: Help needed for SQL Query (merged) [message #393504 is a reply to message #393490] Mon, 23 March 2009 02:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
"not working" is not an Oracle error.
Copy and paste your SQL*Plus session (and format it as I did).

Regards
Michel
Re: Help needed for SQL Query (merged) [message #393520 is a reply to message #393490] Mon, 23 March 2009 03:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> select c1, c2
  2  from ( select to_number(substr(sys_connect_by_path(c1,'/'),
  3                                 2, instr(sys_connect_by_path(c1,'/')||'/','/',1,2)-2))
  4                  c1,
  5                grp,
  6                to_number(substr(sys_connect_by_path(grp,'/'),
  7                                 2, instr(sys_connect_by_path(grp,'/')||'/','/',1,2)-2))
  8                  root_grp,
  9                c2,
 10                level lvl
 11         from testing
 12         connect by c1 = prior c2
 13       )
 14  where grp != root_grp or lvl = 1
 15  /
        C1         C2
---------- ----------
         1          2
         1          5
         1         10
         2          4
         2          5
         5         10

6 rows selected.

Regards
Michel
Re: Help needed for SQL Query (merged) [message #393597 is a reply to message #393520] Mon, 23 March 2009 09:24 Go to previous message
Michel Cadot
Messages: 64110
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Another answer is:
SQL> select c1, c2
  2  from ( select to_number(substr(sys_connect_by_path(c1,'/'),
  3                                 2, instr(sys_connect_by_path(c1,'/')||'/','/',1,2)-2))
  4                  c1,
  5                substr(sys_connect_by_path(grp,'/')||'/',
  6                       1, instr(sys_connect_by_path(grp,'/')||'/','/',1,2))
  7                  root_grp,
  8                substr(sys_connect_by_path(grp,'/')||'/',
  9                       instr(sys_connect_by_path(grp,'/')||'/','/',1,2), 
 10                       instr(sys_connect_by_path(grp,'/')||'/','/',1,3))
 11                  grp_path,
 12                c2
 13         from testing
 14         connect by c1 = prior c2
 15       )
 16  where instr(nvl(grp_path,'/'),root_grp) = 0
 17  /
        C1         C2
---------- ----------
         1          2
         1          5
         1         10
         2          4
         2          5
         5         10

6 rows selected.

There is a difference between these 2 queries, I let you find it. They don't answer to the exactly same question.

Regards
Michel
Previous Topic: Tune PLSQL block
Next Topic: Analyzing Index Problem
Goto Forum:
  


Current Time: Mon Dec 05 11:00:58 CST 2016

Total time taken to generate the page: 0.10385 seconds