Home » SQL & PL/SQL » SQL & PL/SQL » need a help in SQL (oracle 9)
need a help in SQL [message #358386] Tue, 11 November 2008 01:28 Go to next message
royamarnath
Messages: 21
Registered: November 2005
Junior Member
Hi I need a help in SQL.
I have stuck in a situation like this.

Input data -

emp id skill
------ -----------------
131 oracle,vb,java
123 c,c++,vb,java,db2
111 oracle



now my expected output will be

empid skill
---------------
131 oracle
131 vb
131 java
123 c
123 c++
123 vb
123 java
123 db2
111 oracle




can i write this without writing any stored procedures? [ by writing Stored procedures, i had to do that at last. But I need it without writing SP.]


If anybody have a look in this and help me it would be helpful

thanks
roy

[Updated on: Tue, 11 November 2008 02:22] by Moderator

Report message to a moderator

Re: need a help in SQL [message #358400 is a reply to message #358386] Tue, 11 November 2008 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, it can be done in many ways and it has been explained here many times (not so far than last week), so please search it.

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) and use code tags.
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: need a help in SQL [message #358696 is a reply to message #358386] Wed, 12 November 2008 03:41 Go to previous messageGo to next message
sinida1984
Messages: 83
Registered: September 2007
Location: India
Member
Hi,

Please try this...If working please reply.

select empid,substr(skill, INSTR(skill, ',', 1, LEVEL ) + 1,
INSTR(skill, ',', 1, LEVEL+1) -
INSTR(skill, ',', 1, LEVEL) -1 ) skill
FROM (
SELECT ','||skill||',' AS skill ,empid
FROM test13
)
CONNECT BY PRIOR empid = empid
AND INSTR (skill, ',', 1, LEVEL+1) > 0
AND PRIOR dbms_random.string ('p', 10) IS NOT NULL

Thanks and regards
Sinida
Re: need a help in SQL [message #358701 is a reply to message #358696] Wed, 12 November 2008 04:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
1/
Can you explain
Quote:
AND PRIOR dbms_random.string ('p', 10) IS NOT NULL


2/
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) and use code tags.
Use the "Preview Message" button to verify.

Regards
Michel
Re: need a help in SQL [message #358918 is a reply to message #358701] Thu, 13 November 2008 00:05 Go to previous messageGo to next message
sinida1984
Messages: 83
Registered: September 2007
Location: India
Member
Hi,
Dbms_random package can be used for generation random numbers. As here we need to end the query somewhere (the next record to be selected is a child of itself) that package is used here. If you know more information please provide.

Regards
Sinida.
Re: need a help in SQL [message #358926 is a reply to message #358918] Thu, 13 November 2008 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
What is the probability that "dbms_random.string ('p', 10) IS NOT NULL" is false?
What is the relation between this condition and your sentence " here we need to end the query somewhere (the next record to be selected is a child of itself) that package is used here."?

Regards
Michel
Re: need a help in SQL [message #358955 is a reply to message #358926] Thu, 13 November 2008 01:11 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
This "strange" construct is used to detect cycling records in 9i, though CONNECT BY NOCYCLE does not exist.

http://laurentschneider.com/wordpress/2008/09/cycling.html
Re: need a help in SQL [message #358961 is a reply to message #358955] Thu, 13 November 2008 01:18 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This does not answer my questions.

Regards
Michel
Re: need a help in SQL [message #358966 is a reply to message #358961] Thu, 13 November 2008 01:28 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
No, it doesn't.
I'm interested in the answer of the OP too..

Regards
JUM
Re: need a help in SQL [message #358972 is a reply to message #358926] Thu, 13 November 2008 01:40 Go to previous messageGo to next message
sinida1984
Messages: 83
Registered: September 2007
Location: India
Member
Hi

In my humble opinion this 'prior' condition may work since it does not produce a CONNECT BY loop

Regards
Sinida
Re: need a help in SQL [message #358985 is a reply to message #358972] Thu, 13 November 2008 03:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You can do it without need for specifying PRIOR:
create table test_018 (emp_id  number, skill  varchar2(100));

insert into test_018 values (131, 'oracle,vb,java');
insert into test_018 values (123, 'c,c++,vb,java,db2');
insert into test_018 values (111, 'oracle');

commit;

select emp_id
      ,substr(skill,instr(skill,',',1,lvl)+1
                   ,instr(skill,',',1,lvl+1) - instr(skill,',',1,lvl)-1) sub_skill
from  (select emp_id, ','||skill||',' skill from test_018)
     ,(select level lvl from dual connect by level <= (select max(length(skill) - length(replace(skill,',','')))-1 from (select ','||skill||',' skill from test_018)))
where lvl <= length(skill) - length(replace(skill,',',''))-1
order by emp_id,lvl


The second select on Test_081 in the Connect by clause can be discarded if you can guarantee a maximum number of sub skills.
Re: need a help in SQL [message #359027 is a reply to message #358386] Thu, 13 November 2008 06:47 Go to previous messageGo to next message
royamarnath
Messages: 21
Registered: November 2005
Junior Member
i have executed the query -

select emp_id
,substr(skill,instr(skill,',',1,lvl)+1
,instr(skill,',',1,lvl+1) - instr(skill,',',1,lvl)-1) sub_skill
from (select emp_id, ','||skill||',' skill from test_018)
,(select level lvl from dual connect by level <= (select max(length(skill) - length(replace(sk
where lvl <= length(skill) - length(replace(skill,',',''))-1
order by emp_id,lvl;



but i am getting the error as follows -

SQL> /
from (select emp_id, ','||skill||',' skill from test_018)
*
ERROR at line 4:
ORA-01473: cannot have subqueries in CONNECT BY clause
Re: need a help in SQL [message #359029 is a reply to message #359027] Thu, 13 November 2008 06:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your version is too old, this is why we ask to ALWAYS give the version number with 4 decimals.

Regards
Michel
Re: need a help in SQL [message #359032 is a reply to message #359027] Thu, 13 November 2008 07:13 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
just replace the subquery with a value larger than the largest number of comma separated values in a field.
I'd try 100.
Re: need a help in SQL [message #359035 is a reply to message #358386] Thu, 13 November 2008 07:34 Go to previous messageGo to next message
royamarnath
Messages: 21
Registered: November 2005
Junior Member
my vesion of oracle is
Oracle9i Enterprise Edition Release 9.2.0.1.0

anyways i will try with the solution you have provided.
Thanks
Re: need a help in SQL [message #359156 is a reply to message #359035] Thu, 13 November 2008 23:15 Go to previous messageGo to next message
sinida1984
Messages: 83
Registered: September 2007
Location: India
Member
I think sys_guid() provides more performance than using dbms_random...

SELECT empid,
Substr(Skill,Instr(Skill,',',1,LEVEL) + 1,Instr(Skill,',',1,LEVEL + 1) - Instr(Skill,',',1,LEVEL) - 1) Skill
FROM (SELECT ','||Skill||',' AS Skill,empid
FROM Test13)
CONNECT BY PRIOR empid = empid
AND Instr(Skill,',',1,LEVEL + 1) > 0
AND PRIOR Sys_guid() IS NOT NULL


Regards
Sinida

[Updated on: Thu, 13 November 2008 23:17]

Report message to a moderator

Re: need a help in SQL [message #359176 is a reply to message #359156] Fri, 14 November 2008 00:36 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
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) and use code tags.
Use the "Preview Message" button to verify.

In addition, it has been said that this part is useless.

Regards
Michel
Re: need a help in SQL [message #359194 is a reply to message #359176] Fri, 14 November 2008 01:26 Go to previous messageGo to next message
_jum
Messages: 508
Registered: February 2008
Senior Member
Quote:

In addition, it has been said that this part is useless.



This hasn't been said (?) and isn't quit right in my opinion if OP wants to use CONNECT BY in ORA 9.2:

create table test_018 (emp_id  number, skill  varchar2(100));

insert into test_018 values (131, 'oracle,vb,java');
insert into test_018 values (123, 'c,c++,vb,java,db2');
insert into test_018 values (111, 'oracle');
insert into test_018 values (131, 'vb,oracle');

commit;

SELECT emp_id,
Substr(Skill,Instr(Skill,',',1,LEVEL) + 1,Instr(Skill,',',1,LEVEL + 1) - Instr(Skill,',',1,LEVEL) - 1) Skill
FROM (SELECT ','||Skill||',' AS Skill,emp_id
FROM test_018)
CONNECT BY PRIOR emp_id = emp_id
AND Instr(Skill,',',1,LEVEL + 1) > 0
--AND PRIOR Sys_guid() IS NOT NULL

gives ORA-01436: CONNECT-BY-Loop




Re: need a help in SQL [message #359195 is a reply to message #359194] Fri, 14 November 2008 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
sinida1984 wrote on Thu, 13 November 2008 08:40
Hi

In my humble opinion this 'prior' condition may work since it does not produce a CONNECT BY loop

Regards
Sinida

I didn't check the posted solution as this has been answered so many times (and can be done with that trick) that it does not deserve to add new things.

Regards
Michel

[Updated on: Fri, 14 November 2008 01:39]

Report message to a moderator

Re: need a help in SQL [message #359212 is a reply to message #359195] Fri, 14 November 2008 02:59 Go to previous messageGo to next message
sinida1984
Messages: 83
Registered: September 2007
Location: India
Member
Hi Michel,

Please give us another solution if you know so that we can also learn which does not include connect by.

Regards
Sinida

[Updated on: Fri, 14 November 2008 03:09]

Report message to a moderator

Re: need a help in SQL [message #359218 is a reply to message #359212] Fri, 14 November 2008 03:12 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said, solutions has already been posted several times, so search.

Regards
Michel
Previous Topic: How to Pass values returned by refcusror to another cursor
Next Topic: Single ROW Query - but not all columns
Goto Forum:
  


Current Time: Sat Dec 03 18:24:34 CST 2016

Total time taken to generate the page: 0.07373 seconds