CODEWITHSUNDEEP
oracle-databasedecimal
user1777929
user1777929

Reputation: 797

Is there a way to check if a result is decimal?

I'm trying to create a report from our Oracle DB.

Is there a way to check if a result is decimal and if it is, put it in another column?

What I'm trying to do is:

  1. If the number of ordered items falls within a range, put it in that specified column.
  2. If that number of items is in decimals, put in a column for decimals.

My code so far:

SELECT 
  D5.ORD_NUM

, CASE
      WHEN (D5.ORD_QTY/M.ITEM_BKD_QTY) < 6
      THEN (D5.ORD_QTY/M.ITEM_BKD_QTY)
      ELSE 0
  END NO_DISCOUNT
, CASE
      WHEN ((D5.ORD_QTY/M.ITEM_BKD_QTY) >= 6 AND
            (D5.ORD_QTY/M.ITEM_BKD_QTY) <= 10)
      THEN (D5.ORD_QTY/M.ITEM_BKD_QTY)
      ELSE 0
  END DISCOUNT_1
, CASE
      WHEN ((D5.ORD_QTY/M.ITEM_BKD_QTY) >= 11 AND
            (D5.ORD_QTY/M.ITEM_BKD_QTY) <= 20)
      THEN (D5.ORD_QTY/M.ITEM_BKD_QTY)
      ELSE 0
  END DISCOUNT_2
, CASE
      WHEN ((D5.ORD_QTY/M.ITEM_BKD_QTY) >= 21 AND
            (D5.ORD_QTY/M.ITEM_BKD_QTY) <= 30)
          THEN (D5.ORD_QTY/M.ITEM_BKD_QTY)
      ELSE 0
      END DISCOUNT_3

, D5.ITEM_NO
, D5.ORD_LINE_CONF_DATE 

FROM
     ORD D5, ITEM M

WHERE D5.ITEM_NO = M.ITEM_CODE
  AND M.ITEM_BKD_LEV = 2
  AND D5.COMP_CODE = M.COMP_CODE
  AND D5.COMP_CODE = 'W1'
  AND D5.CUST_CODE = 'CUST1'
  AND D5.ITEM_STAT NOT LIKE 'dummy'
  AND D5.ORD_CONF_DATE IS NOT NULL

What I need: I need to add a condition that if the result is a decimal, then it should go into another folder altogether. So to add to the existing CASE statement, I would need something like this..

, CASE
      WHEN (D5.ORD_QTY/M.ITEM_BKD_QTY) < 6
           AND
           (D5.ORD_QTY/M.ITEM_BKD_QTY) IS NOT DECIMAL 
      THEN (D5.ORD_QTY/M.ITEM_BKD_QTY)
      ELSE 0
  END NO_DISCOUNT

And then finally, I need to add a CASE statement that puts the decimal value in that column..

, CASE
  WHEN (D5.ORD_QTY/M.ITEM_BKD_QTY) IS DECIMAL
  THEN (D5.ORD_QTY/M.ITEM_BKD_QTY)
  ELSE 0
  END EXTRA_CHARGE

So, my ideal result should look like this:

+--------+------------+-----------+-----------+-----------+------------+
|ORD_NUM |NO_DISCOUNT |DISCOUNT_1 |DISCOUNT_2 |DISCOUNT_3 |EXTRA_CHARGE|
+--------+------------+-----------+-----------+-----------+------------+
|ORDER 1 |           5|          0|          0|          0|           0|
+--------+------------+-----------+-----------+-----------+------------+
|ORDER 2 |           0|          8|          0|          0|           0|
+--------+------------+-----------+-----------+-----------+------------+
|ORDER 3 |           0|          0|         13|          0|           0|
+--------+------------+-----------+-----------+-----------+------------+
|ORDER 4 |           0|          0|          0|         25|           0|
+--------+------------+-----------+-----------+-----------+------------+
|ORDER 5 |           0|          0|          0|          0|         7.6|
+--------+------------+-----------+-----------+-----------+------------+

Upvotes: 0

Views: 4748

Answers (3)

user1777929
user1777929

Reputation: 797

The MOD supplied by Mathew didn't work, not sure why.

This is what worked for me:

CASE WHEN trunc(ord_qty / item_bkd_qty) = (ord_qty / item_bkd_qty)
     THEN IS NOT DECIMAL
END DISCOUNT
.
.
.
.
CASE WHEN trunc(ord_qty / item_bkd_qty) != (ord_qty / item_bkd_qty)
     THEN DECIMAL
END EXTRA_CHARGE

Upvotes: 1

Matthew McPeak
Matthew McPeak

Reputation: 17924

A few ways to do this:

(D5.ORD_QTY/M.ITEM_BKD_QTY) IS NOT DECIMAL

First: 

MOD(D5.ORD_QTY/M.ITEM_BKD_QTY,1) != 0

Second:

TRUNC((D5.ORD_QTY/M.ITEM_BKD_QTY)) != (D5.ORD_QTY/M.ITEM_BKD_QTY)

Upvotes: 2

Francisco Sitja
Francisco Sitja

Reputation: 1003

You didn't post your table structures, so I created a simplified version to display the logic that would generate it:

SQL> with t (ord_qty, item_bkd_qty) as (
  2  select 10, 2 from dual union all
  3  select 10, 3 from dual union all
  4  select 13, 13 from dual union all
  5  select 33, 11 from dual
  6  )
  7  SELECT ord_qty,
  8         item_bkd_qty,
  9         (ord_qty / item_bkd_qty) do_math,
 10         CASE WHEN trunc(ord_qty / item_bkd_qty) = (ord_qty / item_bkd_qty)
 11              THEN          'IS NOT DECIMAL' END case_test
 12    FROM t;
   ORD_QTY ITEM_BKD_QTY    DO_MATH CASE_TEST
---------- ------------ ---------- --------------
        10            2          5 IS NOT DECIMAL
        10            3 3,33333333 
        13           13          1 IS NOT DECIMAL
        33           11          3 IS NOT DECIMAL

It should be good to go from here. If you need further help please provide some create table and insert statements and we'll be able to move closer to what your sample result has to be.

Upvotes: 3

Related Questions