统计SKU并返回QTY<Full QTY的行及ORA-01722错误排查

嘿，我来拆解一下你遇到的问题：首先是ORA-01722: invalid number的错误，然后还要实现“返回库存数量小于满量的SKU行”的需求，咱们一步步来解决。

错误根源 #

你触发这个错误的核心原因是子查询返回的数据类型和主查询的匹配项不兼容：

• 你的子查询写的是SELECT count(*)，返回的是每个SKU的统计数字；
• 但主查询里WHERE inventory.sku_id IN (...)需要的是SKU编号的列表，把统计数字和SKU编号做对比，Oracle就会抛出“无效数字”的类型转换错误。

另外，你的原始SQL还没加上需求里最关键的筛选条件：库存数量（qty_on_hand）小于满量（FULL QTY）。

修正后的SQL语句 #

版本1：直接修正子查询 #

把子查询的返回列从count(*)改成inventory.sku_id，同时加上库存小于满量的筛选逻辑：

SELECT 
    inventory.sku_id, 
    inventory.location_id, 
    inventory.qty_on_hand, 
    inventory.tag_id, 
    inventory.full_pallet, 
    sku_config.ratio_1_to_2 as "FULL QTY" 
FROM inventory 
JOIN sku_sku_config ON inventory.sku_id = sku_sku_config.sku_id 
JOIN sku_config ON sku_sku_config.config_id = sku_config.config_id 
WHERE 
    -- 核心需求：库存小于满量
    inventory.qty_on_hand < sku_config.ratio_1_to_2
    -- 子查询返回符合条件的SKU编号列表，而非统计数
    AND inventory.sku_id IN (
        SELECT inventory.sku_id 
        FROM inventory 
        JOIN sku_sku_config ON inventory.sku_id = sku_sku_config.sku_id 
        JOIN sku_config ON sku_sku_config.config_id = sku_config.config_id 
        WHERE zone_1 NOT LIKE 'PROD' 
          AND lock_status = 'UnLocked' 
          AND full_pallet = 'N' 
        GROUP BY inventory.sku_id 
        HAVING count(*) >= 1
    );

版本2：用EXISTS优化性能（推荐） #

如果你的子查询只是用来判断SKU是否存在符合条件的记录，用EXISTS会比IN更高效，尤其是数据量较大的时候，而且逻辑更清晰：

SELECT 
    inv.sku_id, 
    inv.location_id, 
    inv.qty_on_hand, 
    inv.tag_id, 
    inv.full_pallet, 
    sc.ratio_1_to_2 as "FULL QTY" 
FROM inventory inv
JOIN sku_sku_config ssc ON inv.sku_id = ssc.sku_id 
JOIN sku_config sc ON ssc.config_id = sc.config_id 
WHERE 
    inv.qty_on_hand < sc.ratio_1_to_2
    AND EXISTS (
        SELECT 1
        FROM inventory inv_sub
        JOIN sku_sku_config ssc_sub ON inv_sub.sku_id = ssc_sub.sku_id 
        JOIN sku_config sc_sub ON ssc_sub.config_id = sc_sub.config_id 
        WHERE inv_sub.sku_id = inv.sku_id
          AND inv_sub.zone_1 NOT LIKE 'PROD' 
          AND inv_sub.lock_status = 'UnLocked' 
          AND inv_sub.full_pallet = 'N' 
        GROUP BY inv_sub.sku_id 
        HAVING count(*) >= 1
    );

小提示 #

• HAVING count(*) >= 1其实可以省略，因为只要GROUP BY能分组成功，count(*)肯定大于等于1，除非你的过滤条件可能返回空，但这里已经有明确的WHERE条件，所以去掉也没问题；
• 如果zone_1的取值是精确匹配（比如就是等于'PROD'或者不是），用zone_1 != 'PROD'会比NOT LIKE更高效，因为LIKE会触发模糊匹配的索引扫描（如果有索引的话）。

内容的提问来源于stack exchange，提问作者Kay