Home » SQL & PL/SQL » SQL & PL/SQL » Complex Query (Oracle10g, Win2003)
Complex Query [message #341747] Wed, 20 August 2008 02:56 Go to next message
weekend79
Messages: 188
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 #341754 is a reply to message #341747] Wed, 20 August 2008 03:41 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use REPLACE.

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Complex Query [message #341786 is a reply to message #341754] Wed, 20 August 2008 05:49 Go to previous messageGo to next message
weekend79
Messages: 188
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 #341789 is a reply to message #341786] Wed, 20 August 2008 05:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Michel Cadot wrote on Wed, 20 August 2008 10:41
Use REPLACE.

Post a test case: create table and insert statements along with the result you want with these data.

Regards
Michel


Re: Complex Query [message #341808 is a reply to message #341789] Wed, 20 August 2008 06:49 Go to previous messageGo to next message
weekend79
Messages: 188
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
ramya29p
Messages: 100
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 #342140 is a reply to message #342136] Thu, 21 August 2008 08:54 Go to previous messageGo to next message
saadatahmad
Messages: 452
Registered: March 2005
Location: Germany/Paderborn
Senior Member

Thanks for the feedback and agreed for the query has limitations. Anyway, in the middle of working for this query using hierarchical queries, the solution came from ramya29p.

thanks and regards,
Saadat Ahmad
Re: Complex Query [message #342142 is a reply to message #342136] Thu, 21 August 2008 09:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
ramya29p
Messages: 100
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 #342324 is a reply to message #342315] Fri, 22 August 2008 00:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No it does not, there is still one line missing.

And USE SQL Formatter and keep your lines in 80 characters.

Regards
Michel
Re: Complex Query [message #342329 is a reply to message #342324] Fri, 22 August 2008 01:13 Go to previous messageGo to next message
ramya29p
Messages: 100
Registered: November 2007
Location: Chennai
Senior Member
Hi Michel,
can you tell me what line i am missing .but this query is giving the correct result.
Re: Complex Query [message #342333 is a reply to message #342329] Fri, 22 August 2008 01:21 Go to previous messageGo to next message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
ramya29p
Messages: 100
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 Go to previous message
Michel Cadot
Messages: 64117
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: History Correction
Next Topic: what is BYPASS_UJVC? in update statement
Goto Forum:
  


Current Time: Tue Dec 06 04:13:00 CST 2016

Total time taken to generate the page: 0.14368 seconds