Hi frnds plz help with the query. I have 4 tables as below
Table A ScriptId(PK)
ScriptId | Title|
-------------------
1 ABC
2 XYZ
Table B ScriptId(FK), OT_ID(PK)
ScriptId | OT_ID| SUB_DT
---------------------------------
1 5 01-03-11
1 6 01-05-11
2 7 13-04-11
Table C OT_ID(FK)
OT_ID| ContactName
---------------------------------
5 Mr. A
5 Mr. X
6 Mr. C
7 Mr. B
Table D OT_ID(FK)
OT_ID| Sub_By
---------------------------------
5 Mr. M
5 Mr. N
6 Mr. O
7 Mr. P
Now the final result should be like as below here the data from table D
should come as comma separated based on OT_ID
ScriptId | Title| OT_ID| SUB_DT | ContactName | Sub_By
---------------------------------------------------------------------------------
1 ABC 5 01-03-11 Mr. A Mr. M / Mr. N
1 ABC 5 01-03-11 Mr. X Mr. M / Mr. N
1 ABC 6 01-05-11 Mr. C Mr. O
2 XYZ 7 13-04-11 Mr. B Mr. P
Now I am not able to get the data under column Sub_By in the above result. I
am trying to do correlation please correct me where I am going wrong.
I am using the below query
---------------
Select A.script_id,A.title,B.outgoing_note_id,B
.sub_date,C.contact_name,
(
SELECT SUBSTR (SYS_CONNECT_BY_PATH (USER_ID , ' / '), 4) csv
FROM
(SELECT SUB_BY , ROW_NUMBER () OVER (ORDER BY USER_ID ) rn,COUNT (*) OVER ()
cnt
FROM D where B.outgoing_note_id=D.outgoing_note_id
)
WHERE rn = cnt START WITH rn = 1 CONNECT BY rn = PRIOR rn + 1
) as "SUB_BY",
>From B,C,A
Where
B.outgoing_note_id = C.outgoing_note_id and
A.script_id = B.script_id and
order by A.script_id,B.sub_date desc,B.outgoing_note_id desc
--------------
/*The problem I am facing is at the below line
where B.outgoing_note_id=D.outgoing_note_id
*/ Plz correct me.
--
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
