sql - on 子句的 where 条件

Question

我需要使用案例条件修改此 sql，如果 serial_number 表有可用的库存标签（serial_number.tag_id = inventory.tag_id），我需要将 qty 填充为“1”，否则需要从查询结果中填充 qty 会怎样是最好的方法吗？

SELECT
    * from ( SELECT
           inventory.tag_id                 PalletNumber,
           inventory.sku_id                  sku,
           inventory.batch_id                batch,
           **inventory.qty_on_hand             qty,**
           inventory.condition_id            status,
           inventory.user_def_type_1         reasoncode,
           serial_number.serial_number       serialnumber,
           TO_CHAR(inventory.expiry_dstamp, 'YYYY-MM-DD') Expiry_Date,
           pre_advice_header.pre_advice_id   asn,
           pre_advice_header.status          asnstatus,
           inventory.supplier_id             vendor,
           TO_CHAR(inventory.receipt_dstamp, 'YYYY-MM-DD') ArrivalDate,
           pre_advice_line.host_pre_advice_id PONumber
       FROM
           inventory
            JOIN pre_advice_header ON pre_advice_header.pre_advice_id = inventory.receipt_id
                                                AND pre_advice_header.site_id = inventory.site_id
                                                AND pre_advice_header.client_id = inventory.client_id
                                                AND pre_advice_header.status in ('Complete')
                                                AND pre_advice_header.status is not NULL
           LEFT OUTER JOIN pre_advice_line ON pre_advice_line.pre_advice_id = inventory.receipt_id
                                              AND pre_advice_line.sku_id = inventory.sku_id
           LEFT JOIN serial_number ON **serial_number.tag_id = inventory.tag_id**
                                      AND serial_number.site_id = inventory.site_id
                                      AND serial_number.client_id = inventory.client_id
                                      AND serial_number.receipt_id = inventory.receipt_id
                                      AND serial_number.sku_id = inventory.sku_id

       WHERE
           inventory.site_id = 'UK-CBY-04'AND inventory.client_id = 'MLC796'
           AND (inventory.condition_id != 'SC1' or inventory.condition_id is null)
          AND (inventory.zone_1 <> '80SHP01' or inventory.zone_1 is null)
       GROUP BY
           inventory.tag_id,
           inventory.sku_id,
           inventory.batch_id,
           inventory.qty_on_hand,
           inventory.condition_id,
           inventory.user_def_type_1,
           serial_number.serial_number,
           inventory.expiry_dstamp,
           pre_advice_header.pre_advice_id,
           pre_advice_header.status,
           inventory.supplier_id,
           inventory.receipt_dstamp,
           pre_advice_line.host_pre_advice_id
       ORDER BY
           inventory.sku_id DESC,
           inventory.tag_id ASC
)

标签： sqloracle

Answer 1

使用 CASE 的解决方案如下：

案例当 serial_number.serial_number 不为 NULL THEN 1 ELSE inventory.qty_on_hand END qty