Home » SQL & PL/SQL » SQL & PL/SQL » Need help for Query
Need help for Query [message #218661] Fri, 09 February 2007 03:58 Go to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
I have 2 tables Parent and Child. Parent table has Parent_ID and Child table has Child_ID and Parent_ID. Here are the rows:

Parent table:		
Parent_ID	
---------	
1
2
3
4
5
6

Child table
Child_ID	Parent_ID
--------	---------
C1		1
C2		2
C3		1	
C4		4	
C5		3	
C6		3	
C7		4	
C8		4	
C9		1

I need an output like this:

Relationships
-------------
1-C1,C3,C9
2-C2
3-C5,C6
4-C4,C7,C8


Is this possible using a SQL or even perhaps a function inside a SQL???
Re: Need help for Query [message #218672 is a reply to message #218661] Fri, 09 February 2007 04:44 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

CREATE OR REPLACE FUNCTION TEST_FUNC(V_ID NUMBER) RETURN VARCHAR2 IS
V_TXT VARCHAR2(4000) DEFAULT NULL;
V_SEP VARCHAR2(1) DEFAULT NULL;
BEGIN
FOR X IN (SELECT CHILD_ID FROM CHILD_TAB WHERE ID=V_ID)
LOOP
V_TXT :=V_TXT||V_SEP||X.CHILD_ID;
V_SEP :=',';
END LOOP;
RETURN V_TXT;
END;


search these forum or
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:229614022562you will get many such examples.

regards,

[Updated on: Fri, 09 February 2007 04:48]

Report message to a moderator

Re: Need help for Query [message #218681 is a reply to message #218672] Fri, 09 February 2007 05:07 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
THanks.. It will then be:

select Pid||'-'||test_func(pid)
from Parent 
where pid in (select pid from child)
Re: Need help for Query [message #218683 is a reply to message #218681] Fri, 09 February 2007 05:13 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: March 2002
Location: Mumbai
Senior Member
hi,

nope,its
 
select p.id,test_func(p.id)txt from parent_tab p,child_tab c
where p.id=c.id
group by p.id 


refer to the link which i have sent you.


regards,
Re: Need help for Query [message #218690 is a reply to message #218683] Fri, 09 February 2007 05:46 Go to previous messageGo to next message
vgs2005
Messages: 123
Registered: April 2005
Senior Member
Understood, but your query will return:

PID     TXT
---     --------
1	C1,C2,C9
2	C7,C8
3	C3
4	C4
5	C5,C6


I need the results to be (just one column):

RELATIONSHIPS
-------------
1-C1,C2,C9
2-C7,C8
3-C3
4-C4
5-C5,C6


Anyway, it was helpful.. Smile
Re: Need help for Query [message #218691 is a reply to message #218690] Fri, 09 February 2007 05:55 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
vgs2005... you have the same problem??
dhananjay, I looked into the site. And yes, I just need one column. It's a minor difference anyway.. Smile
Re: Need help for Query [message #218692 is a reply to message #218691] Fri, 09 February 2007 05:57 Go to previous messageGo to next message
vgs2005
Messages: 123
Registered: April 2005
Senior Member
Used to have...Once upon a time..
Re: Need help for Query [message #218698 is a reply to message #218661] Fri, 09 February 2007 06:22 Go to previous messageGo to next message
uzairabsar
Messages: 1
Registered: February 2007
Location: DELHI
Junior Member
Hi,


please follow this

DECLARE
V_P_NO NUMBER;
V_VAL VARCHAR2(50);
CURSOR X IS SELECT A.P_NO FROM PARENT_T A;
CURSOR Y IS SELECT B.P_NO,B.C_NO FROM CHILD_T B
WHERE B.P_NO=V_P_NO
GROUP BY B.P_NO,B.C_NO;
BEGIN
FOR J IN X
LOOP
V_P_NO:=J.P_NO;
FOR K IN Y
LOOP
IF V_VAL IS NULL THEN
V_VAL:=K.C_NO;
ELSE
V_VAL:=V_VAL||','||K.C_NO;
END IF;
END LOOP;

DBMS_OUTPUT.PUT_LINE(V_P_NO||'-'||V_VAL);
V_VAL:=NULL;
END LOOP;

END;


Rgards

Uzair Hasan Nizami
Re: Need help for Query [message #218720 is a reply to message #218698] Fri, 09 February 2007 09:49 Go to previous messageGo to next message
a_developer
Messages: 194
Registered: January 2006
Senior Member
Thanks for the help. But I actually prefer to limit the coding by using SQL statements as much as possible instead of putting the entire logic in PL/Sql..
Re: Need help for Query [message #218744 is a reply to message #218661] Fri, 09 February 2007 14:50 Go to previous message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

Search this site for concat_all.

A userdefined function would be the best, here

Srini
Previous Topic: Help with a Trigger (Mutating Trigger)
Next Topic: unique constraint violated
Goto Forum:
  


Current Time: Sun Dec 04 18:57:09 CST 2016

Total time taken to generate the page: 0.07413 seconds