I could use some help with some Oracle syntax. Below is statement in MS SQL and I would like to do the same in Oracle. I'm not sure what function in Oracle is equivalent to CASE and is usable with comparison operators? I know DECODE can be used for equal values, but what can I use for greater than and less than conditions? I've looked at the Oracle documentation, but have come up short. As well any suggestions for DATEADD and DATEDIFF? Any help is appreciated.

Thanks!

SELECT i.item_number, i.description,
SUM(CASE WHEN v.end_tran_date < '08/01/00' THEN v.delta_qty ELSE 0 END) AS "Beginning On Hand",
SUM(CASE WHEN v.delta_qty < 0 AND v.end_tran_date >= '08/01/00' THEN -v.delta_qty ELSE 0 END) AS Picks,
SUM(CASE WHEN v.delta_qty > 0 AND v.end_tran_date >= '08/01/00' THEN v.delta_qty ELSE 0 END) AS Receipts,
SUM(CASE WHEN v.end_tran_date < '08/01/00' THEN v.delta_qty ELSE 0 END) +
ROUND((SUM(CASE WHEN v.end_tran_date >= '08/01/00' THEN v.delta_qty * DATEDIFF(day,v.end_tran_date,'08/30/00') ELSE 0 END))/DATEDIFF(day,'08/01/00','08/30/00'),1) AS "Average Balance",
ROUND(DATEDIFF(day,'08/01/00','08/30/00') *
SUM(CASE WHEN v.delta_qty < 0 AND v.end_tran_date >= '08/01/00' THEN -v.delta_qty ELSE 0 END)
/(SUM(CASE WHEN v.end_tran_date < '08/01/00' THEN v.delta_qty * DATEDIFF(day,'08/01/00','08/30/00') ELSE 0 END) +
SUM(CASE WHEN v.end_tran_date >= '08/01/00' THEN v.delta_qty * DATEDIFF(day,v.end_tran_date,'08/30/00') ELSE 0 END)),1) AS Turns,
ROUND((SUM(CASE WHEN v.delta_qty < 0 AND v.end_tran_date >= '08/01/00' THEN -v.delta_qty ELSE 0 END)/DATEDIFF(day,'08/01/00','08/30/00')),1) AS "Average Use Per Day",
i.qty_on_hand AS "Current On Hand",
ROUND((i.qty_on_hand*DATEDIFF(day,'08/01/00','08/30/00')/SUM(CASE WHEN v.delta_qty < 0 AND v.end_tran_date >= '08/01/00' THEN -v.delta_qty ELSE 0 END)),1) AS "Days of Inventory on Hand"
FROM t_item_master i, v_inventory_log v
WHERE v.item_number = i.item_number
AND v.wh_id = i.wh_id
AND v.end_tran_date < DATEADD(day,1,'08/30/00')
GROUP BY i.item_number, i.description, i.qty_on_hand
HAVING SUM(CASE WHEN v.end_tran_date < '08/01/00' THEN v.delta_qty ELSE 0 END) +
ROUND((SUM(CASE WHEN v.end_tran_date >= '08/01/00' THEN v.delta_qty * DATEDIFF(day,v.end_tran_date,'08/30/00') ELSE 0 END))/DATEDIFF(day,'08/01/00','08/30/00'),1) > 0
AND SUM(CASE WHEN v.end_tran_date < '08/01/00' THEN v.delta_qty ELSE 0 END) >= 0
AND SUM(CASE WHEN v.delta_qty < 0 AND v.end_tran_date >= '08/01/00' THEN -v.delta_qty ELSE 0 END) > 0
ORDER BY 'Turns' DESC

10-04-2000, 03:37 PM

svk

If you are on ORACLE 8, there is CASE function available. Otherwise you will have to use DECODE and SIGN functions.