DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: MSSQL to Oracle syntax?

  1. #1
    Join Date
    Oct 2000
    Posts
    1

    Question

    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

  2. #2
    Join Date
    Jul 2000
    Posts
    521
    If you are on ORACLE 8, there is CASE function available. Otherwise you will have to use DECODE and SIGN functions.
    svk

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width