On May 9, 6:05 am, Joel <johow...@gmail.com> wrote:
> 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.- Hide quoted text -
>
> - Show quoted text -
My guess is that this is homework and he's wanting someone else to do
it.
David Fitzjarrell
--
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
