The query you provided doesn't work at all (errors) and further the
column names in it don't match with the tables you specified... so I
certainly hope this isn't a homework problem you are cheating on! If
it's not, please try the below:
CREATE TABLE a AS
SELECT 1 scriptid, 'ABC' title FROM DUAL
UNION ALL
SELECT 2, 'XYZ' FROM DUAL;
CREATE TABLE b AS
SELECT 1 scriptid, 5 ot_id, TO_DATE('01-03-11','DD-MM-RR') sub_dt FROM
DUAL
UNION ALL
SELECT 1, 6, TO_DATE('01-05-11','DD-MM-RR') FROM DUAL
UNION ALL
SELECT 2, 7, TO_DATE('13-04-11','DD-MM-RR') FROM DUAL;
CREATE TABLE c AS
SELECT 5 ot_id, 'Mr. A' contactname FROM DUAL
UNION ALL
SELECT 5, 'Mr. X' FROM DUAL
UNION ALL
SELECT 6, 'Mr. C' FROM DUAL
UNION ALL
SELECT 7, 'Mr. B' FROM DUAL;
CREATE TABLE d AS
SELECT 5 ot_id, 'Mr. M' sub_by FROM DUAL
UNION ALL
SELECT 5, 'Mr. N' FROM DUAL
UNION ALL
SELECT 6, 'Mr. O' FROM DUAL
UNION ALL
SELECT 7, 'Mr. P' FROM DUAL;
SELECT *
FROM a;
SELECT *
FROM b;
SELECT *
FROM c;
SELECT *
FROM d;
SELECT a.scriptid, a.title, b.ot_id, b.sub_dt, c.contactname, e.sub_by
FROM a
JOIN b ON a.scriptid = b.scriptid
JOIN c ON b.ot_id = c.ot_id
JOIN ( SELECT ot_id, listagg(sub_by, ' / ') WITHIN GROUP (ORDER BY
1) AS sub_by /*listagg is an 11g built-in function*/
FROM d
GROUP BY ot_id) e
ON c.ot_id = e.ot_id
order by A.scriptid,B.sub_dt desc,B.ot_id desc;
drop table a purge;
drop table b purge;
drop table c purge;
drop table d purge;
On May 8, 1:35 pm, chintan salvi <chintu4...@gmail.com> wrote:
> 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
