在满足条件时使用CASE语句创建包含数据的列

Question

我需要帮助填充'DAYTIME‘值为列创建使用下面的用例语句与oracle。

我的脚本

WITH your_query
        AS (SELECT SLMCU AS "BUSINESSUNIT",
                   SLDOCO AS "ORDERNO",
                   SLDCTO AS "ORDERTYPE",
                   SLAN8 AS "CUSTOMERNO",
                   ABALPH AS "CUSTOMERNAME",
                   SLLITM AS "ITEMNO",
                   SLDSC1 AS "DESCRIPTION",
                   SLDSC2 AS "DESCRIPTION2",
                   SLLTTR AS "LAST STATUS",
                   SLNXTR AS "NEXT STATUS",
                   CASE SLTRDJ
                      WHEN 0 THEN TO_DATE (TO_CHAR (1 + 1900000), 'YYYYDDD')
                      ELSE TO_DATE (TO_CHAR (SLTRDJ + 1900000), 'YYYYDDD')
                   END
                      AS "ORDER DATE",
                   CASE SLADDJ
                      WHEN 0 THEN TO_DATE (TO_CHAR (1 + 1900000), 'YYYYDDD')
                      ELSE TO_DATE (TO_CHAR (SLADDJ + 1900000), 'YYYYDDD')
                   END
                      AS "SHIPPED DATE",
                   SLTDAY AS "DAYTIME",
                   SLUORG / 10000 AS "ORDER QUANTITY",
                   SLSOQS / 10000 AS "SHIPPED QUANTITY"
              FROM PRODDTA.F42199 INNER JOIN PRODDTA.F0101 ON SLAN8 = ABAN8
             WHERE SLTRDJ = 119327 AND SLLNID = 1000 AND ROWNUM <= 200)
SELECT *
  FROM (SELECT t.*,
               CASE
                  WHEN "NEXT STATUS" = 540 THEN "DAYTIME" 
                  END
                  AS time_of_day,
               CASE
                  WHEN "NEXT STATUS" = 580
                  THEN
                     "DAYTIME"
               END
                  AS shipped_time
          FROM (SELECT q.*
                  FROM your_query q) t
         WHERE "NEXT STATUS" IN (540, 580, 620))
 WHERE "NEXT STATUS" = 620

结果

​

​

我试图实现的是，从'time_of_day‘和'shipped_time’列中的空值判断，当满足'NEXT STATUS‘条件但查询失败时，内部查询返回的每一行的'DAYTIME’值。

期望输出

​

​

编辑:数据模型

ORDER_NO ORDER_TYPE CUSTOMER_NO CUSTOMER_NAME LAST_ STATUS NEXT_ STATUS ORDER_ DATE SHIPPED_DATE DAYTIME ORDER_ QTY SHIPPED_ QTY 19440263 SO 295042 MERU站点520 540 2019年12月5日140556 8 19440263 SO 295042 MERU站点540 580 2019年12/5 140745 8 8 19440263 SO 295042 MERU站点582 2019年12/5 12/2019 140822 8 8 19440263 SO 295042 MERU站点582 620 12/5/2019年5月12日140837 8 8 19440263 SO 295042梅鲁仓库620 999 2019年5月12日90333 8 8

19440270 SO 295042 MERU车厂520 540 2019年12/5/2019年1/1/1900 144529 2 2 19440270 SO 295042 MERU车厂540 580 2019年12/5/ 144725 2 19440270 SO 295042 MERU车厂580 582 2019年12/5/2019144757 2 2 19440270 SO 295042 MERU车厂582 620 1220202012/5/2019 144813 2 2 19440270 SO 295042 MERU车厂620 999 2019年12/5/2

19562535 ST 295046纳库鲁车辆段520 540 2019年12月5日202705 11 19562535 ST 295046纳库鲁车辆段540 560 2019年12月5日203058 11 11 19562535 ST 295046纳库鲁车辆段560 580 2019年12/5/2019233630 11 11 19562535 ST 295046纳库鲁车辆段580 582 2019年12/5 12/12 234505 11 11 19562535 ST 295046纳库鲁车辆段582 620 12/5/12/2019234627 11 11 11

19759237 SV 200977 D控股有限公司520 540 2019年5月12日134200 12 19759237 SV 200977 D控股有限公司540 560 2019年12月1日134800 12 19759237 SV 200977 D控股有限公司560 580 2019年12 /5 160704 12 12 19759237 SV 200977 D控股有限公司580 582 2019年12 /5160911 12 19759237 SV 200977 D控股有限公司582 620 12 2019年5月12 161034 12 12 19759237 SV 200977 D控股有限公司620 999 12 2019年5月12 90340 12

19759240 SV 200007奈瓦沙基因520 540 2019年1月1日155110 11 11 19759240 SV 200007奈瓦沙基因540 560 2019年12月1日155506 11 11 19759240 SV 200007奈瓦沙基因560 580 2019年12/5 12/2019173205 11 11 19759240 SV 200007奈瓦沙基因580 582 12/5 2019173424 11 1119759240 SV 200007奈瓦沙Gen 582 620 2019年5月12日173526 11 11 19759240 SV 200007奈瓦沙Gen 620 999 2019年5月12日90346 11 11 11

Answer 1

根据原始查询和注释，您似乎希望输出子查询中的所有列以及两个派生列: TIME_OF_DAY和SHIPPED。在查询中，这两列都包含子查询中的DAYTIME值。因此，下面这个简单得多的查询应该能满足这些需求。您也只想要下一个状态为620的记录(540和580似乎一点也不相关)。

WITH your_query
        AS (...your original sub-query...)
SELECT q.*,
       "DAYTIME" AS time_of_day,
       "DAYTIME" AS shipped_time
  FROM your_query q
 WHERE "NEXT STATUS" = 620

编辑:所以这是一个与你最初想要的非常不同的要求。该查询将3个slnxtr值分解为它们自己的子查询，然后将它们连接起来。如果不正确了解您的数据模型/数据，我就不能确定连接条件，但从概念上讲，这应该是最简单的。可能还有其他使用解析函数的方法，但在不知道状态转换规则的情况下，我不想推测。

WITH row_620
        AS (SELECT SLMCU AS "BUSINESSUNIT",
                   SLDOCO AS "ORDERNO",
                   SLDCTO AS "ORDERTYPE",
                   SLAN8 AS "CUSTOMERNO",
                   ABALPH AS "CUSTOMERNAME",
                   SLLITM AS "ITEMNO",
                   SLDSC1 AS "DESCRIPTION",
                   SLDSC2 AS "DESCRIPTION2",
                   SLLTTR AS "LAST STATUS",
                   SLNXTR AS "NEXT STATUS",
                   CASE SLTRDJ
                      WHEN 0 THEN TO_DATE (TO_CHAR (1 + 1900000), 'YYYYDDD')
                      ELSE TO_DATE (TO_CHAR (SLTRDJ + 1900000), 'YYYYDDD')
                   END AS "ORDER DATE",
                   CASE SLADDJ
                      WHEN 0 THEN TO_DATE (TO_CHAR (1 + 1900000), 'YYYYDDD')
                      ELSE TO_DATE (TO_CHAR (SLADDJ + 1900000), 'YYYYDDD')
                   END AS "SHIPPED DATE",
                   SLTDAY AS "DAYTIME",
                   SLUORG / 10000 AS "ORDER QUANTITY",
                   SLSOQS / 10000 AS "SHIPPED QUANTITY"
              FROM PRODDTA.F42199 INNER JOIN PRODDTA.F0101 ON SLAN8 = ABAN8
             WHERE SLTRDJ = 119327 AND SLLNID = 1000 AND ROWNUM <= 200
               AND SLNXTR = 620)
      row_540
        AS (SELECT sldoco, sltday     
              FROM proddta.f42199
                   INNER JOIN PRODDTA.F0101 ON SLAN8 = ABAN8
             WHERE SLTRDJ = 119327 AND SLLNID = 1000
               AND SLNXTR = 540)
      row_580
        AS (SELECT sldoco, sltday     
              FROM proddta.f42199
                   INNER JOIN PRODDTA.F0101 ON SLAN8 = ABAN8
             WHERE SLTRDJ = 119327 AND SLLNID = 1000
               AND SLNXTR = 580)
SELECT t.*,
       b.sltday time_of_day,
       c.sltday shipped_time
  FROM row_640 a
       LEFT OUTER JOIN
       row_540 b ON a.sldoco = b.sldoco
       LEFT OUTER JOIN
       row_580 c ON a.sldoco = c.sldoco