Home » SQL & PL/SQL » SQL & PL/SQL » Complex Query (Oracle10g, Win2003)
Complex Query [message #341747] |
Wed, 20 August 2008 02:56  |
weekend79
Messages: 198 Registered: April 2005 Location: Islamabad
|
Senior Member |

|
|
Hi
my query say Query1 returns two columns i.e
Student_Name Subject_Codes
Abc 001,003,005
pqr 002,005,006
xyz 005,008
another table say tab_sub_code contain names against all Subject_Codes and have no relation with the tables that used to extract above result.
tab_sub_code
Subject_Codes Subject_Name
001 sub_1Name
002 sub_2Name
003 sub_3Name
i want to join my Query1 with tab_sub_code in a way that result will be like
Student_Name Subjects
Abc sub_1Name,sub_3Name,sub_5Name
pqr sub_2Name,sub_5Name,Sub_6Name
xyz sub_5Name,sub_8Name
Wishes
|
|
|
|
Re: Complex Query [message #341786 is a reply to message #341754] |
Wed, 20 August 2008 05:49   |
weekend79
Messages: 198 Registered: April 2005 Location: Islamabad
|
Senior Member |

|
|
sorry for complexity let it be more simple now
the is table tab1 as
Student_Name Subject_Codes
Abc 001,003,005
pqr 002,005,006
xyz 005,008
other table Tab2
tab_sub_code
Subject_Codes Subject_Name
001 sub_1Name
002 sub_2Name
003 sub_3Name
I want as query that result as follows:
Student_Name Subjects
Abc sub_1Name,sub_3Name,sub_5Name
pqr sub_2Name,sub_5Name,Sub_6Name
xyz sub_5Name,sub_8Name
wishes
|
|
|
|
Re: Complex Query [message #341808 is a reply to message #341789] |
Wed, 20 August 2008 06:49   |
weekend79
Messages: 198 Registered: April 2005 Location: Islamabad
|
Senior Member |

|
|
Michel Cadot
please find enclosed the create and insert code
create table tab1 ( Student_Name varchar2(50),subject_code varchar2(100));
insert into tab1(student_name, subject_code) values ('abc', '001,003,005');
insert into tab1(student_name, subject_code) values ('pqr', ' 002,005,006');
insert into tab1(student_name, subject_code) values ('xyz', '005,008');
create table tab_sub_code (Subject_Codes char(3), subject_name varchar2(30));
insert into tab_sub_code (subject_codes, subject_name) values ('001', 'sub_1Name');
insert into tab_sub_code (subject_codes, subject_name) values ('002', 'sub_2Name');
insert into tab_sub_code (subject_codes, subject_name) values ('003', 'sub_3Name');
insert into tab_sub_code (subject_codes, subject_name) values ('004', 'sub_4Name');
insert into tab_sub_code (subject_codes, subject_name) values ('005', 'sub_5Name');
insert into tab_sub_code (subject_codes, subject_name) values ('006', 'sub_6Name');
insert into tab_sub_code (subject_codes, subject_name) values ('007', 'sub_7Name');
insert into tab_sub_code (subject_codes, subject_name) values ('008', 'sub_8Name');
insert into tab_sub_code (subject_codes, subject_name) values ('009', 'sub_9Name');
I want as query that result as follows:
Student_Name Subjects
Abc sub_1Name,sub_3Name,sub_5Name
pqr sub_2Name,sub_5Name,Sub_6Name
xyz sub_5Name,sub_8Name
Wishes
|
|
|
Re: Complex Query [message #341830 is a reply to message #341747] |
Wed, 20 August 2008 08:23   |
spmano1983
Messages: 269 Registered: September 2007
|
Senior Member |
|
|
Hi,
Try this
select student_name,
(select subject_name from tab_sub_code where subject_codes=f1)
|| ','||(select subject_name from tab_sub_code where subject_codes=f2)
||',' ||(select subject_name from tab_sub_code where subject_codes=f3)
from
(
select student_name
,trim(replace(substr(subject_codes,instr(subject_codes,',',1,1),4),',','')) f1
,trim(replace(substr(subject_codes,instr(subject_codes,',',1,2),4),',','')) f2
,trim(replace(substr(subject_codes,instr(subject_codes,',',1,3),4),',','')) f3
from
(
select student_name, ','||subject_codes||',' subject_codes from
student
))
|
|
|
Re: Complex Query [message #341833 is a reply to message #341830] |
Wed, 20 August 2008 08:49   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
SQL> select student_name,
2 (select subject_name from tab_sub_code where subject_codes=f1)
3 || ','||(select subject_name from tab_sub_code where subject_codes=f2)
4 ||',' ||(select subject_name from tab_sub_code where subject_codes=f3)
5 from
6 (
7 select student_name
8 ,trim(replace(substr(subject_codes,instr(subject_codes,',',1,1),4),',','')) f1
9 ,trim(replace(substr(subject_codes,instr(subject_codes,',',1,2),4),',','')) f2
10 ,trim(replace(substr(subject_codes,instr(subject_codes,',',1,3),4),',','')) f3
11 from
12 (
13 select student_name, ','||subject_codes||',' subject_codes from
14 student
15 ))
16 /
student
*
ERROR at line 14:
ORA-00942: table or view does not exist
Does not pass the first step.
But I'm pretty sure, it does not pass any test.
Regards
Michel
|
|
|
Re: Complex Query [message #341861 is a reply to message #341808] |
Wed, 20 August 2008 10:48   |
S.Rajaram
Messages: 1027 Registered: October 2006 Location: United Kingdom
|
Senior Member |
|
|
The way I would approach this problem is as follows
a) break the comma separated list into multiple rows
b) join with the table and substitute
c) re-group it
@Michael, I would be interested to see a solution using replace.
Regards
Raj
|
|
|
Re: Complex Query [message #341892 is a reply to message #341861] |
Wed, 20 August 2008 13:14   |
 |
saadatahmad
Messages: 452 Registered: March 2005 Location: Germany/Paderborn
|
Senior Member |

|
|
hi,
here is the query.
works with 9i or greater
SCOTT @ORCL> SELECT x.student_name
2 , MAX(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(x.subject_name)
3 , ',')
4 , 2))
5 KEEP(DENSE_RANK LAST ORDER BY x.curr) as_string
6 FROM (SELECT t.student_name
7 , t.subject_name
8 , ROW_NUMBER()
9 OVER (PARTITION BY t.student_name
10 ORDER BY t.subject_name) curr
11 , ROW_NUMBER()
12 OVER (PARTITION BY t.student_name
13 ORDER BY t.subject_name) - 1 prev
14 FROM
15 (
16 select student_name, subject_name
17 from tab1, tab_sub_code
18 where subject_codes = substr(subject_code, 1,3)
19 union
20 select student_name, subject_name
21 from tab1, tab_sub_code
22 where subject_codes = substr(subject_code, 5,3)
23 union
24 select student_name, subject_name
25 from tab1, tab_sub_code
26 where subject_codes = substr(subject_code, 9,3)
27 ) t
28 ) x
29 GROUP BY x.student_name
30 START WITH x.curr = 1
31 CONNECT BY x.prev = PRIOR x.curr
32 AND x.student_name = PRIOR x.student_name
33 /
STUDENT_NAME AS_STRING
--------------- ------------------------------------------------------------------------------------
abc sub_1Name,sub_3Name,sub_5Name
pqr sub_2Name,sub_5Name,sub_6Name
xyz sub_5Name,sub_8Name
regards,
Saadat Ahmad
|
|
|
Re: Complex Query [message #342017 is a reply to message #341892] |
Thu, 21 August 2008 01:39   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
I disagree with you, it does not work:
SQL> select * from tab1;
STUDENT_NAME SUBJECT_CODE
------------ ---------------------------------------------------
abc 001,003,005
pqr 002,005,006
xyz 005,008
pqr 002,005,006,009
4 rows selected.
SQL> SELECT x.student_name
2 , MAX(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(x.subject_name)
3 , ',')
4 , 2))
5 KEEP(DENSE_RANK LAST ORDER BY x.curr) as_string
6 FROM (SELECT t.student_name
7 , t.subject_name
8 , ROW_NUMBER()
9 OVER (PARTITION BY t.student_name
10 ORDER BY t.subject_name) curr
11 , ROW_NUMBER()
12 OVER (PARTITION BY t.student_name
13 ORDER BY t.subject_name) - 1 prev
14 FROM
15 (
16 select student_name, subject_name
17 from tab1, tab_sub_code
18 where subject_codes = substr(subject_code, 1,3)
19 union
20 select student_name, subject_name
21 from tab1, tab_sub_code
22 where subject_codes = substr(subject_code, 5,3)
23 union
24 select student_name, subject_name
25 from tab1, tab_sub_code
26 where subject_codes = substr(subject_code, 9,3)
27 ) t
28 ) x
29 GROUP BY x.student_name
30 START WITH x.curr = 1
31 CONNECT BY x.prev = PRIOR x.curr
32 AND x.student_name = PRIOR x.student_name
33 /
STUDENT_NAME AS_STRING
------------ --------------------------------------------------
abc sub_1Name,sub_3Name,sub_5Name
xyz sub_5Name,sub_8Name
2 rows selected.
Regards
Michel
|
|
|
Re: Complex Query [message #342116 is a reply to message #342017] |
Thu, 21 August 2008 06:56   |
 |
saadatahmad
Messages: 452 Registered: March 2005 Location: Germany/Paderborn
|
Senior Member |

|
|
hi,
With due respect, I would like to mention here that the data which you provided is not according to the format of the OP.
Check his first post.
This is his data
Student_Name Subject_Codes
Abc 001,003,005
pqr 002,005,006
xyz 005,008
I know in the insert statement, he put one space for one row being added. But that may be a typo mistake. Otherwise in his data there is no space and my statement is written keeping in mind the format of his data.
Now, I'll come to your data
SQL> select * from tab1;
STUDENT_NAME SUBJECT_CODE
------------ ---------------------------------------------------
abc 001,003,005
pqr 002,005,006
xyz 005,008
pqr 002,005,006,009
There should be no space before 002 because in his second table, there is no space included in the codes.
There is no space in the codes table.
SAADAT @ORCL> select * from tab_sub_code
2 /
SUB SUBJECT_NAME
--- ------------------------------
001 sub_1Name
002 sub_2Name
003 sub_3Name
004 sub_4Name
005 sub_5Name
006 sub_6Name
007 sub_7Name
008 sub_8Name
009 sub_9Name
9 rows selected.
SAADAT @ORCL>
Now to get the result for your data we must add the select condition which sutracts values from 13 onwards. So this is your data and the result but without spaces ofcourse.
SAADAT @ORCL> select * from tab1
2 /
STUDENT_NAME SUBJECT_CODE
-------------------------------------------------- -------------------------------------------------
abc 001,003,005
pqr 002,005,006
xyz 005,008
pqr 002,005,006,009
SAADAT @ORCL> SELECT x.student_name
2 , MAX(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(x.subject_name)
3 , ',')
4 , 2))
5 KEEP(DENSE_RANK LAST ORDER BY x.curr) as_string
6 FROM (SELECT t.student_name
7 , t.subject_name
8 , ROW_NUMBER()
9 OVER (PARTITION BY t.student_name
10 ORDER BY t.subject_name) curr
11 , ROW_NUMBER()
12 OVER (PARTITION BY t.student_name
13 ORDER BY t.subject_name) - 1 prev
14 FROM
15 (
16 select student_name, subject_name
17 from tab1, tab_sub_code
18 where subject_codes = substr(subject_code, 1,3)
19 union
20 select student_name, subject_name
21 from tab1, tab_sub_code
22 where subject_codes = substr(subject_code, 5,3)
23 union
24 select student_name, subject_name
25 from tab1, tab_sub_code
26 where subject_codes = substr(subject_code, 9,3)
27 union -- This union statement must be added for your data
28 select student_name, subject_name
29 from tab1, tab_sub_code
30 where subject_codes = substr(subject_code, 13,3)
31 ) t) x
32 GROUP BY x.student_name
33 START WITH x.curr = 1
34 CONNECT BY x.prev = PRIOR x.curr
35 AND x.student_name = PRIOR x.student_name
36 /
STUDENT_NAME AS_STRING
-------------------------------------------------- -------------------------------------------------
abc sub_1Name,sub_3Name,sub_5Name
pqr sub_2Name,sub_5Name,sub_6Name,sub_9Name
xyz sub_5Name,sub_8Name
SAADAT @ORCL>
However, I agree that for this type of data, we must use substr function carefully, otherwise we may get unexpected result.
Note: I think that if the OP does not want any explicit relation between his two tables then at least data must be consistent in his tables. I appreciate your valuable input and if I'm stil wrong please mention it.
regards,
Saadat Ahmad
|
|
|
Re: Complex Query [message #342129 is a reply to message #342116] |
Thu, 21 August 2008 08:01   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
The data is the data OP provided (plus one row, I admit), you should be able to handle variations, don't you think? For myself, I always add a space after a comma, others add one before, others one before and after, your query should be able to handle this (it's really easy to trim).
Now what if there are 5, 6, 7, 8, 9 ... 100 values in the string, will you put 100 unions?
Regards
Michel
|
|
|
Re: Complex Query [message #342136 is a reply to message #342129] |
Thu, 21 August 2008 08:14   |
ramya29p
Messages: 146 Registered: November 2007 Location: Chennai
|
Senior Member |
|
|
Hi,
Instead of using union we can use in this way even if there is any space before the string the following query will work
SELECT x.student_name,
MAX(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(x.subject_name), ','), 2))
KEEP(DENSE_RANK LAST ORDER BY x.curr) as_string
FROM (select a.student_name,a.subject_code,b.subject_codes,b.subject_name,
row_number() over(partition by a.student_name order by b.subject_name)-1 prev,
row_number() over(partition by a.student_name order by b.subject_name) curr
from tab1 a, tab_sub_code b
where a.subject_code like '%'||b.subject_codes||'%') x
GROUP BY x.student_name
START WITH x.curr = 1
CONNECT BY x.prev = PRIOR x.curr AND x.student_name = PRIOR x.student_name
[Updated on: Thu, 21 August 2008 09:10] by Moderator Report message to a moderator
|
|
|
|
Re: Complex Query [message #342142 is a reply to message #342136] |
Thu, 21 August 2008 09:07   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Keep your lines in 80 characters.
Use SQL Formatter.
SQL> select * from tab1;
STUDENT_NAME SUBJECT_CODE
------------ ------------------------------------------------------------------------------
abc 001,003,005
pqr 002,005,006
xyz 005,008
pqr 002,005,006,009
4 rows selected.
SQL> SELECT x.student_name,
2 MAX(SUBSTR(SYS_CONNECT_BY_PATH(TO_CHAR(x.subject_name), ','), 2))
3 KEEP(DENSE_RANK LAST ORDER BY x.curr) as_string
4 FROM (select a.student_name,a.subject_code,b.subject_codes,b.subject_name,
5 row_number() over(partition by a.student_name order by b.subject_name)-1 prev,
6 row_number() over(partition by a.student_name order by b.subject_name) curr
7 from tab1 a, tab_sub_code b
8 where a.subject_code like '%'||b.subject_codes||'%') x
9 GROUP BY x.student_name
10 START WITH x.curr = 1
11 CONNECT BY x.prev = PRIOR x.curr AND x.student_name = PRIOR x.student_name;
STUDENT_NAME AS_STRING
------------ ----------------------------------------------------------------------
abc sub_1Name,sub_3Name,sub_5Name
pqr sub_2Name,sub_2Name,sub_5Name,sub_5Name,sub_6Name,sub_6Name,sub_9Name
xyz sub_5Name,sub_8Name
3 rows selected.
Something does not work in your query.
Regards
Michel
[Updated on: Thu, 21 August 2008 09:13] Report message to a moderator
|
|
|
Re: Complex Query [message #342315 is a reply to message #342142] |
Fri, 22 August 2008 00:31   |
ramya29p
Messages: 146 Registered: November 2007 Location: Chennai
|
Senior Member |
|
|
Hi,
This Query will Work
SQL> select x.student_name,LTRIM(MAX(SYS_CONNECT_BY_PATH(x.subject_name,',')) keep (DENSE_RANK LAST ORDER BY x.curr),',') as_string
2 from (select a.student_name,a.subject_code,b.subject_codes,b.subject_name,
3 row_number() over(partition by a.student_name order by b.subject_name)-1 prev,
4 row_number() over(partition by a.student_name order by b.subject_name) curr
5 from (select student_name,max(subject_code) as subject_code from tab1 group by student_name) a,tab_sub_code b
6 where a.subject_code like '%'||b.subject_codes||'%') x
7 GROUP BY x.student_name
8 START WITH x.curr = 1
9 CONNECT BY x.prev = PRIOR x.curr AND x.student_name = PRIOR x.student_name
10 /
STUDENT_NAME AS_STRING
-------------------------------------------------- --------------------------------------------------------------------------------
abc sub_1Name,sub_3Name,sub_5Name
pqr sub_2Name,sub_5Name,sub_6Name,sub_9Name
xyz sub_5Name,sub_8Name
|
|
|
|
|
Re: Complex Query [message #342333 is a reply to message #342329] |
Fri, 22 August 2008 01:21   |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
10 /
STUDENT_NAME AS_STRING
------------ ---------------------------------------------------------------------
abc sub_1Name,sub_3Name,sub_5Name
pqr sub_2Name,sub_5Name,sub_6Name,sub_9Name
xyz sub_5Name,sub_8Name
3 rows selected.
SQL> select * from tab1;
STUDENT_NAME SUBJECT_CODE
------------ ---------------------------------------------------------------------
abc 001,003,005
pqr 002,005,006
xyz 005,008
pqr 002,005,006,009
4 rows selected.
My fault, I had 2 students with same name.
SQL> update tab1 set student_name='ghj' where student_name='pqr' and rownum=1;
1 row updated.
SQL> select * from tab1;
STUDENT_NAME SUBJECT_CODE
------------ ---------------------------------------------------
abc 001,003,005
ghj 002,005,006
xyz 005,008
pqr 002,005,006,009
4 rows selected.
10 /
STUDENT_NAME AS_STRING
------------ ---------------------------------------------------
abc sub_1Name,sub_3Name,sub_5Name
ghj sub_2Name,sub_5Name,sub_6Name
pqr sub_2Name,sub_5Name,sub_6Name,sub_9Name
xyz sub_5Name,sub_8Name
4 rows selected.
Now explain it or at least format it as it will be easier to understand.
Regards
Michel
|
|
|
Re: Complex Query [message #342791 is a reply to message #342333] |
Mon, 25 August 2008 02:21   |
ramya29p
Messages: 146 Registered: November 2007 Location: Chennai
|
Senior Member |
|
|
Hi Michel,
is this the output u expected?
select x.student_name,
LTRIM(MAX(SYS_CONNECT_BY_PATH(x.subject_name, ','))
keep(DENSE_RANK LAST ORDER BY x.curr),
',') as_string
from (select a.student_name,
a.subject_code,
b.subject_codes,
b.subject_name,
row_number() over(partition by a.subject_code order by b.subject_name) - 1 prev,
row_number() over(partition by a.subject_code order by b.subject_name) curr
from (select student_name, max(subject_code) as subject_code
from tab1
group by student_name, subject_code) a,
tab_sub_code b
where a.subject_code like '%' || b.subject_codes || '%') x
GROUP BY x.student_name, x.subject_code
START WITH x.curr = 1
CONNECT BY x.prev = PRIOR x.curr AND x.student_name = PRIOR x.student_name and
x.subject_code = prior x.subject_code
SQL>
STUDENT_NAME AS_STRING
-------------------------------------------------- --------------------------------------------------------------------------------
abc sub_1Name,sub_3Name,sub_5Name
pqr sub_2Name,sub_5Name,sub_6Name
pqr sub_2Name,sub_5Name,sub_6Name,sub_9Name
xyz sub_5Name,sub_8Name
SQL>
|
|
|
Re: Complex Query [message #342797 is a reply to message #342791] |
Mon, 25 August 2008 02:32  |
 |
Michel Cadot
Messages: 68737 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Isn't it easier to read?
Even easier is to use "with" clause.
For instance:
SQL> with
2 val as ( -- get all values of each student
3 select distinct
4 student_name,
5 trim(regexp_substr(trim(subject_code),'[^,]+', 1, level)) subject_code
6 from tab1
7 connect by regexp_substr(trim(subject_code),'[^,]+', 1, level) is not null
8 ),
9 rep as ( -- replace these values by name
10 select student_name, subject_name
11 from val, tab_sub_code
12 where subject_codes = subject_code
13 )
14 select student_name, wmsys.wm_concat(subject_name) subjects
15 from rep
16 group by student_name
17 order by student_name
18 /
STUDENT_NAME SUBJECTS
------------ --------------------------------------------------
abc sub_1Name,sub_3Name,sub_5Name
pqr sub_2Name,sub_5Name,sub_6Name
wxy sub_5Name,sub_6Name,sub_9Name,sub_8Name
xyz sub_5Name,sub_8Name
4 rows selected.
Regards
Michel
|
|
|
Goto Forum:
Current Time: Tue Feb 18 22:49:35 CST 2025
|