Best way to join line items on sales order-CodePudding

I have what is probably a remedial question, but I am struggling to come up with the best way to implement it through searching. I am working on a retool workflow that will get the latest shipped sales orders, and send an email. The database I am querying has a Sales Order -> Line Items relationship.

Simplified Example: SalesOrderTable

SalesOrderID	Customer	SoldDate	ShipDate
1	Customer1	12/7/22	12/7/22
2	Customer1	12/7/22	12/7/22
3	Customer2	12/7/22	12/7/22

Lines on the sales orders

LineItemID	LineNumber	SalesOrderID	Product	Price
1	1	1	Eggs	$1.00
2	2	1	Milk	$1.00
3	1	2	Bread	$1.00
4	2	2	Eggs	$1.00
5	3	2	Milk	$1.00
6	1	3	Eggs	$1.00

I am unsure the best way to join the lines to the sales orders. Obviously simply joining the data isn't really what I want, I don't think. Meaning

SELECT * FROM SalesOrder
JOIN lines ON SalesOrder.SalesOrderID = Lines.SalesOrderID
WHERE SalesOrderID = 2

Would produce something like(don't think this is the right way to accomplish):

SalesOrderID	Customer	SoldDate	ShipDate	LineItemID	LineNumber	SalesOrderID	Product	Price
2	Customer2	12/7/22	12/7/22	3	1	2	Bread	$1.00
2	Customer2	12/7/22	12/7/22	4	2	2	Eggs	$1.00
2	Customer2	12/7/22	12/7/22	5	3	2	Milk	$1.00

Ultimately I am trying to feed this data into Carbone.io from Retool Workflow to make a PDF sales order using workflow, which I believe needs to be JSON similar to below

"SalesOrderID": 2,
"Customer": "Customer2",
"SoldDate": "12/7/22",
"LineItems": {ArrayOfLineItems}

I've tried selecting all Sales orders and then using a workflow loop to iterate over each one, selecting all the line items for each line in the sales order. This gives me the line items for each sales order, but I can't seem to figure out how to get the line items and sales order in the same JSON.

Would I be better to try to write a query that includes all the lines as a JSON array in a column?

Sorry for the long post, but appreciate any feedback or suggestions on the best way to attempt this!

CodePudding user response：

Something like this?

SELECT * FROM SalesOrder
JOIN Lines1 ON SalesOrder.SalesOrderID = Lines1.SalesOrderID and Lines1.LineItemId= 1
LEFT OUTER JOIN Lines2 ON SalesOrder.SalesOrderID = Lines2.SalesOrderID and Lines2.LineItemId= 2
LEFT OUTER JOIN Lines3 ON SalesOrder.SalesOrderID = Lines3.SalesOrderID and Lines3.LineItemId= 3

Here is the stuff function. Might get you closer to the format you're looking for.

select t.*
,(SELECT
        DISTINCT
            STUFF((select ','   l.Product
                    FROM LineTable l
                    WHERE l.SalesOrderId = t.SalesOrderId
                    FOR XML path('')),1,1,'') as 'Product'
    ) 
from SalesTable t

CodePudding user response：

You can get the JSON directly in Oracle using:

SELECT JSON_OBJECT(
         KEY 'salesOrderId' VALUE s.salesOrderId,
         KEY 'customer'     VALUE s.customer,
         KEY 'soldDate'     VALUE s.soldDate,
         KEY 'shipDate'     VALUE s.shipDate,
         KEY 'lineItems'    VALUE l.lineItems FORMAT JSON
         RETURNING CLOB
       ) AS salesorder
FROM   salesorder s
       CROSS JOIN LATERAL (
         SELECT JSON_ARRAYAGG(
                  JSON_OBJECT(
                    KEY 'lineItemId' VALUE l.lineItemid,
                    KEY 'lineNumber' VALUE l.lineNumber,
                    KEY 'product'    VALUE l.product,
                    KEY 'price'      VALUE l.price
                  )
                  ORDER BY l.lineNumber
                  RETURNING CLOB
                ) AS lineItems
         FROM   lines l
         WHERE  l.salesorderid = s.salesorderid
       ) l

Which, for the sample data:

CREATE TABLE SalesOrder (SalesOrderID, Customer, SoldDate, ShipDate) AS
SELECT 1,   'Customer1', DATE '2022-07-12', DATE '2022-07-12' FROM DUAL UNION ALL
SELECT 2,   'Customer1', DATE '2022-07-12', DATE '2022-07-12' FROM DUAL UNION ALL
SELECT 3,   'Customer2', DATE '2022-07-12', DATE '2022-07-12' FROM DUAL;

CREATE TABLE Lines (LineItemID, LineNumber, SalesOrderID, Product, Price) AS
SELECT 1,   1,  1,  'Eggs',  1.00 FROM DUAL UNION ALL
SELECT 2,   2,  1,  'Milk',  1.00 FROM DUAL UNION ALL
SELECT 3,   1,  2,  'Bread', 1.00 FROM DUAL UNION ALL
SELECT 4,   2,  2,  'Eggs',  1.00 FROM DUAL UNION ALL
SELECT 5,   3,  2,  'Milk',  1.00 FROM DUAL UNION ALL
SELECT 6,   1,  3,  'Eggs',  1.00 FROM DUAL;

Outputs:

SALESORDER
{"salesOrderId":1,"customer":"Customer1","soldDate":"2022-07-12T00:00:00","shipDate":"2022-07-12T00:00:00","lineItems":[{"lineItemId":1,"lineNumber":1,"product":"Eggs","price":1},{"lineItemId":2,"lineNumber":2,"product":"Milk","price":1}]}
{"salesOrderId":2,"customer":"Customer1","soldDate":"2022-07-12T00:00:00","shipDate":"2022-07-12T00:00:00","lineItems":[{"lineItemId":3,"lineNumber":1,"product":"Bread","price":1},{"lineItemId":4,"lineNumber":2,"product":"Eggs","price":1},{"lineItemId":5,"lineNumber":3,"product":"Milk","price":1}]}
{"salesOrderId":3,"customer":"Customer2","soldDate":"2022-07-12T00:00:00","shipDate":"2022-07-12T00:00:00","lineItems":[{"lineItemId":6,"lineNumber":1,"product":"Eggs","price":1}]}

fiddle