Query with Rollup 1

[llmclaughlin]

The below query I'm trying to get a sum of the T_Total column, I've tried using the group by rollup, which doesn't seem to give the results I want. I would like the effect of the rollup where it sums that column only.

SELECT
SUBSTR(ig.vendor_code || ' ' || vendor_name, 1, 25) t_group,
II.inventory_item_number,
Inventory_Item_name,
II.Item_cost,
COBRA_WAREHOUSE_SETTINGS.center_name,
NVL(T_Total, 0) TT_Total
FROM
COBRA_WAREHOUSE_SETTINGS@sk120p1.craware.net,
VENDORS@sk120p1.cobraware.net ig,
INVENTORY_STOCK_ITEMS@sk120p1.craware.net II,
(
SELECT
inventory_item_number,
SUM(quantity) AS t_total
FROM
INVENTORY_WAREHOUSE_TRANS@sk120p1.craware.net
WHERE
transaction_type IN ('SB', 'SO', 'SOP', 'SBP')
AND
transaction_date BETWEEN TO_DATE('01/01/2006','MM/DD/YYYY') AND TO_DATE('01/30/2006','MM/DD/YYYY')
GROUP BY inventory_item_number
) T_T
WHERE
II.primary_vendor_code (+) = ig.vendor_code
AND
T_T.inventory_item_number (+) = II.inventory_item_number
AND
II.inventory_item_number BETWEEN 1000 AND 9999
ORDER BY Inventory_Item_name

Any suggestions

Thanks

Louie

 

[SimonSellick]

Hi,

How many rows are in COBRA_WAREHOUSE_SETTINGS? There doesn't seem to be a join condition for the table.

I'm not quite clear whether your join to INVENTORY_STOCK_ITEMS is equi or outer - do some items not have a primary vendor, or are you wanting all vendors irrespective of whether they have items?

Anyway, won't this have the required effect:

SELECT    SUBSTR(ig.vendor_code || ' ' || vendor_name, 1, 25) t_group,
          II.inventory_item_number,
          Inventory_Item_name,
          II.Item_cost,
          /* not sure about this COBRA_WAREHOUSE_SETTINGS.center_name, */
          NVL(SUM(T_T.quantity), 0) TT_Total
FROM      /* not sure about this COBRA_WAREHOUSE_SETTINGS@sk120p1.craware.net, */
          VENDORS@sk120p1.cobraware.net ig
JOIN      INVENTORY_STOCK_ITEMS@sk120p1.craware.net II 
            ON II.primary_vendor_code (+) = ig.vendor_code
           AND II.inventory_item_number BETWEEN 1000 AND 9999
LEFT JOIN INVENTORY_WAREHOUSE_TRANS@sk120p1.craware.net T_T 
            ON T_T.inventory_item_number (+) = II.inventory_item_number
            AND T_T.transaction_type IN ('SB', 'SO', 'SOP', 'SBP')
            AND T_T.transaction_date BETWEEN TO_DATE('01/01/2006','MM/DD/YYYY') 
              AND TO_DATE('01/30/2006','MM/DD/YYYY')
GROUP BY SUBSTR(ig.vendor_code || ' ' || vendor_name, 1, 25),
         II.inventory_item_number,
         Inventory_Item_name,
         II.Item_cost
ORDER BY Inventory_Item_name

Or something along those lines?

 

[llmclaughlin]

First of all, thanks

I'm not able to execute the query you suggested, I get Sql command not properly ended. It points to the JOIN statement. I don't see anything wrong. Do you?



Louie

 

[SimonSellick]

Louie,

I can't run it as I don't have the tables, but I should have removed your (+) outer join indicators as it's using ANSI syntax. Also, I have an idea that Oracle doesn't like an IN clause in an outer join condition; if that turns out to be an issue you can wrap it up as you had previously (!).

Good luck,
Simon.