Oracle SQL Newbie

[dellguy]

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?

 

[LKBrwnDBA]

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.



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

 

[dellguy]

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).

 

[LKBrwnDBA]

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

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

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

 

[dellguy]

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).

 

[fredericofonseca]

what program are you using to execute that sql?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[LKBrwnDBA]

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

 

[dellguy]

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

 

[LKBrwnDBA]

Try this one:

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.


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