combine 2 queries into one using columns from a query as variables in the other-CodePudding

Having a hard time visualizing on where to start as creating advanced (IMO) SQL statements is not my forte and can't easily find a previous solution on stackflow if there is one. I would like to have the 2 queries below run as 1 query by using the selected 'warehouse', 'style code', 'color code' from the top query as variables in the 2nd query. Right now, I run the results from 1 in a for loop to get the result needed from 2 line by line. Hoping for a more efficient way of doing it.

** I need ALL results of the top query and only results from the 2nd if there is a match, otherwise query 2 will be a zero. **

SELECT
    btrim(store."TEXT") AS warehouse, 
    style."EXT_ID" AS style_code,  
    style."TEXT" AS style_desc, 
    roll."COLOR" AS color_code,
    roll."COLOR_TEXT" AS color_desc,
    roll."EXT_ID" as roll_number,
    roll."QTY_UNIT" as unit_measure,
    roll."COST_UNIT_PRICE" as cost,
    round(roll."ROLL_WIDTH",2) AS width,
    round(sum( CASE WHEN roll."EXT_ID" not like 'PND%' THEN roll."QTY_AVAIL" ELSE 0 END),2) as in_store,
    round(sum( CASE WHEN roll."EXT_ID" ~ 'PND-(?!PO)[A-Z]' THEN roll."QTY_AVAIL" ELSE 0 END),2) as on_pnd,
    sku."ITEM_CLASS" AS category,
    supplier."EXT_ID" AS vendor_code,
    supplier."TEXT" AS vendor_name,
    store."EXT_ID" as warehouse_number,
    'GFS' as "system",
    store."REGION_ID" as region
 FROM "ROLL" roll
   LEFT JOIN "STYLE" style ON style."ID"::text = roll."STYLE_ID"::text
   LEFT JOIN "SKU" sku ON sku."ID"::text = roll."SKU"::text
   LEFT JOIN "STORE" store ON store."ID"::text = roll."WHSE_ID"::text
   LEFT JOIN "SUPPLIER" supplier ON supplier."ID"::text = style."MFR"::text
WHERE roll."QTY_AVAIL" > 0.99 and style."EXT_ID" is not null  
GROUP BY store."EXT_ID",store."TEXT",style."EXT_ID",style."TEXT",roll."COLOR",roll."COLOR_TEXT",roll."QTY_UNIT",roll."COST_UNIT_PRICE", roll."EXT_ID", roll."ROLL_WIDTH",sku."ITEM_CLASS",supplier."EXT_ID",supplier."TEXT", system, store."REGION_ID"
ORDER BY supplier."EXT_ID", style."EXT_ID", roll."COLOR";`


SELECT round(sum(CASE WHEN invoice_item."UNIT_COST_UNIT_PRICE" < 0.00 THEN invoice_item."QTY" * '-1'::numeric ELSE invoice_item."QTY" END),2)  AS invoiced
FROM "INVOICE_ITEM" invoice_item
 LEFT JOIN "STYLE" style ON style."ID"::text = invoice_item."STYLE_ID"::text
 LEFT JOIN "INVOICE" invoice ON invoice_item."INVOICE_ID"::text = invoice."ID"::text
 LEFT JOIN "STORE" store ON store."ID"::text = invoice."STORE_ID"::text
WHERE store."EXT_ID" = '{$warehouse}' and style."EXT_ID" = '{$style_code}' and invoice_item."COLOR" = '{$color_code}' and invoice_item."CREATED_ON" >= '2021-01-01' and invoice_item."CREATED_ON" <= now();

Here is the CTE I tried...

WITH  line_data AS (
SELECT
    btrim(store."TEXT") AS warehouse, 
    style."EXT_ID" AS style_code,  
    style."TEXT" AS style_desc, 
    roll."COLOR" AS color_code,
    roll."COLOR_TEXT" AS color_desc,
    roll."EXT_ID" as roll_number,
    roll."QTY_UNIT" as unit_measure,
    roll."COST_UNIT_PRICE" as cost,
    round(roll."ROLL_WIDTH",2) AS width,
    round(sum( CASE WHEN roll."EXT_ID" not like 'PND%' THEN roll."QTY_AVAIL" ELSE 0 END),2) as in_store,
    round(sum( CASE WHEN roll."EXT_ID" ~ 'PND-(?!PO)[A-Z]' THEN roll."QTY_AVAIL" ELSE 0 END),2) as on_pnd,
    sku."ITEM_CLASS" AS category,
    supplier."EXT_ID" AS vendor_code,
    supplier."TEXT" AS vendor_name,
    store."EXT_ID" as warehouse_number,
    'GFS' as "system",
    store."REGION_ID" as region
 FROM "ROLL" roll
   LEFT JOIN "STYLE" style ON style."ID"::text = roll."STYLE_ID"::text
   LEFT JOIN "SKU" sku ON sku."ID"::text = roll."SKU"::text
   LEFT JOIN "STORE" store ON store."ID"::text = roll."WHSE_ID"::text
   LEFT JOIN "SUPPLIER" supplier ON supplier."ID"::text = style."MFR"::text
WHERE roll."QTY_AVAIL" > 0.99 and style."EXT_ID" is not null  
GROUP BY store."EXT_ID",store."TEXT",style."EXT_ID",style."TEXT",roll."COLOR",roll."COLOR_TEXT",roll."QTY_UNIT",roll."COST_UNIT_PRICE", roll."EXT_ID", roll."ROLL_WIDTH",sku."ITEM_CLASS",supplier."EXT_ID",supplier."TEXT", system, store."REGION_ID"
)

SELECT l_d.*,
round(sum(CASE WHEN invoice_item."UNIT_COST_UNIT_PRICE"::numeric < 0.00 THEN invoice_item."QTY" * '-1'::numeric ELSE invoice_item."QTY"::numeric END),2)  AS invoiced
FROM line_data l_d, "INVOICE_ITEM" invoice_item
  LEFT JOIN "STYLE" style ON style."ID"::text = invoice_item."STYLE_ID"::text
  LEFT JOIN "INVOICE" invoice ON invoice_item."INVOICE_ID"::text = invoice."ID"::text
  LEFT JOIN "STORE" store ON store."ID"::text = invoice."STORE_ID"::text
WHERE store."EXT_ID" = l_d.warehouse and style."EXT_ID" = l_d.style_code and invoice_item."COLOR" = l_d.color_code and invoice_item."CREATED_ON" >= '2021-01-01' and invoice_item."CREATED_ON" <= now()
GROUP BY warehouse,style_code,style_desc,color_code,color_desc,roll_number,unit_measure,cost,width,in_store,on_pnd,category,vendor_code,vendor_name,warehouse_number,"system",region;

CodePudding user response：

I'm thinking something like this. There are definitely better ways to organize this though.

SELECT round(sum(CASE WHEN ii."UNIT_COST_UNIT_PRICE" < 0.00 THEN ii."QTY" * '-1'::numeric ELSE ii."QTY" END),2) AS invoiced
FROM "INVOICE" i INNER JOIN INVOICE_ITEM" ii
     ON ii."INVOICE_ID"::text = i."ID"::text
         AND ii."CREATED_ON" >= '2021-01-01' and ii."CREATED_ON" <= now()
     right outer join line_data ld ON
         ld.style_code = ii."STYLE_ID"::text AND
         ld.color_code = ii."COLOR" AND
         ld.store_id = i."STORE_ID"::text