INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Oracle SQL Newbie

Oracle SQL Newbie

(OP)
Hello all. I am trying to figure out how to write SQL to group data by common values together after they've been identified and assembled together. Using the following sample data,

DATA
ORDERS TABLE
ID 100

ORDER DETAIL TABLE
ID 200
Orders_ ID 100
Part_ID 500

PART TABLE
ID 500

ID 501

ID 502

STANDARDS TABLE
ID 700
Part_ID 500
Standard A

ID 701
Part_ID 500
Standard B

ID 702
Part_ID 500
Standard C

ID 703
Part_ID 501
Standard D

ID 704
Part_ID 501
Standard E

ID 705
Part_ID 501
Standard F

ID 706
Part_ID 502
Standard A

ID 707
Part_ID 502
Standard B

ID 708
Part_ID 502
Standard C

I want to generate the following query output:

Order ID 100

Standard A,B,C
Part_ID 500
Part ID 502

Standard D,E,F
Part_ID 501

Any suggestions on the code to use?

RE: Oracle SQL Newbie

You could us the LISTAGG() function (That is if you have at least Oracle version 11g.)
Otherwise you would need to use SYS_CONNECT_BY_PATH() or WM_CONCAT() functions.
3eyes

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

RE: Oracle SQL Newbie

(OP)
We use Oracle 11g. This is what happens with LISTAGG. Can you see something wrong??

SELECT "ARINVT"."ITEMNO",
LISTAGG( "MASTER_SPEC"."CODE", '; ') WITHIN GROUP (ORDER BY "ARINVT"."ITEMNO") "ITEM"
FROM ((("IQMS"."ORD_DETAIL" "ORD_DETAIL"
LEFT OUTER JOIN "IQMS"."ARINVT" "ARINVT" ON "ORD_DETAIL"."ARINVT_ID"="ARINVT"."ID")
LEFT OUTER JOIN "IQMS"."ORDERS" "ORDERS" ON "ORD_DETAIL"."ORDERS_ID"="ORDERS"."ID")
LEFT OUTER JOIN "IQMS"."ARINVT_SPEC" "ARINVT_SPEC" ON "ARINVT"."ID"="ARINVT_SPEC"."ARINVT_ID")
LEFT OUTER JOIN "IQMS"."MASTER_SPEC" "MASTER_SPEC" ON "ARINVT_SPEC"."MASTER_SPEC_ID"="MASTER_SPEC"."ID"
WHERE "ORD_DETAIL"."ORDERS_ID"=104138
GROUP BY "ARINVT"."ITEMNO"
ORDER BY "ARINVT"."ITEMNO"
-- Failed:
General SQL error.
ORA-00923: FROM keyword not found where expected

Manual SQL Statement -> Finished with 1 error(s).

RE: Oracle SQL Newbie

Don't know if you actually need all those parenthesis, try:

CODE

SELECT   Arinvt.Itemno
       , LISTAGG ( Master_Spec.Code, '; ') WITHIN GROUP (ORDER BY Arinvt.Itemno) Item
    FROM Iqms.Ord_Detail Ord_Detail
         LEFT OUTER JOIN Iqms.Arinvt Arinvt ON Ord_Detail.Arinvt_Id = Arinvt.Id
         LEFT OUTER JOIN Iqms.Orders Orders ON Ord_Detail.Orders_Id = Orders.Id
         LEFT OUTER JOIN Iqms.Arinvt_Spec Arinvt_Spec
            ON Arinvt.Id = Arinvt_Spec.Arinvt_Id
         LEFT OUTER JOIN Iqms.Master_Spec Master_Spec
            ON Arinvt_Spec.Master_Spec_Id = Master_Spec.Id
   WHERE Ord_Detail.Orders_Id = 104138
GROUP BY Arinvt.Itemno
ORDER BY Arinvt.Itemno 
noevil

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

RE: Oracle SQL Newbie

(OP)
still got the same error

SELECT Arinvt.Itemno
, LISTAGG ( Master_Spec.Code, '; ') WITHIN GROUP (ORDER BY Arinvt.Itemno) Item
FROM Iqms.Ord_Detail Ord_Detail
LEFT OUTER JOIN Iqms.Arinvt Arinvt ON Ord_Detail.Arinvt_Id = Arinvt.Id
LEFT OUTER JOIN Iqms.Orders Orders ON Ord_Detail.Orders_Id = Orders.Id
LEFT OUTER JOIN Iqms.Arinvt_Spec Arinvt_Spec
ON Arinvt.Id = Arinvt_Spec.Arinvt_Id
LEFT OUTER JOIN Iqms.Master_Spec Master_Spec
ON Arinvt_Spec.Master_Spec_Id = Master_Spec.Id
WHERE Ord_Detail.Orders_Id = 104138
GROUP BY Arinvt.Itemno
ORDER BY Arinvt.Itemno

-- Failed:
General SQL error.
ORA-00923: FROM keyword not found where expected

Manual SQL Statement -> Finished with 1 error(s).

RE: Oracle SQL Newbie

And/Or what version of Oracle are we talking about?

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

RE: Oracle SQL Newbie

(OP)
Oracle 11g R1 - which I see doesn't support LISTAGG. I still need help to figure this out please.... bigsmile

RE: Oracle SQL Newbie

Try this one:

CODE

SELECT  Arinvt.Itemno
     ,  WM_CONCAT( Master_Spec.Code) Item_Codes
    FROM Iqms.Ord_Detail Ord_Detail
         LEFT OUTER JOIN Iqms.Arinvt Arinvt ON Ord_Detail.Arinvt_Id = Arinvt.Id
         LEFT OUTER JOIN Iqms.Orders Orders ON Ord_Detail.Orders_Id = Orders.Id
         LEFT OUTER JOIN Iqms.Arinvt_Spec Arinvt_Spec
            ON Arinvt.Id = Arinvt_Spec.Arinvt_Id
         LEFT OUTER JOIN Iqms.Master_Spec Master_Spec
            ON Arinvt_Spec.Master_Spec_Id = Master_Spec.Id
   WHERE Ord_Detail.Orders_Id = 104138
GROUP BY Arinvt.Itemno
ORDER BY Arinvt.Itemno 
Note that WM_CONCAT is undocumented and unsupported by Oracle, meaning it should not be used in production systems.
bigcheeks

----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close