有没有办法检查结果是否是十进制的？

Question

我正在尝试从我们的Oracle DB创建一个报告。

是否有一种方法来检查结果是否为十进制，如果是，将其放在另一列中？

我想做的是：

1. 如果排序项的数量在一个范围内，则将其放在指定的列中。
2. 如果项目的数量是以小数为单位的，那就在小数列中。

到目前为止我的代码：

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

我需要：我需要添加一个条件，如果结果是十进制，那么它应该完全进入另一个文件夹。因此，为了加入现有的案例陈述，我需要这样的东西..

, 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

最后，我需要添加一个CASE语句，该语句将十进制值放在该列中。

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

因此，我的理想结果应该如下所示：

+--------+------------+-----------+-----------+-----------+------------+
|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|
+--------+------------+-----------+-----------+-----------+------------+

Answer 1

您没有发布表结构，所以我创建了一个简化版本来显示生成它的逻辑：

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

从这里出发应该是件好事。如果您需要进一步的帮助，请提供一些create和insert语句，我们将能够更接近您的示例结果。

Answer 2

有几种方法可以做到这一点：

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

第一：

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

第二：

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

Answer 3

马修斯提供的MOD不起作用，不知道原因。

这就是对我有用的东西：

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