添加FETCH FIRST ROW WITH TIES后触发ORA-01790错误求助

ORA-01790错误的核心是UNION ALL前后两个子查询的对应列数据类型不匹配，具体问题点如下：

1. 行号列类型不一致

   • 第一个子查询中，rownum1是通过TO_CHAR(ROW_NUMBER() OVER (...))转换为字符串类型；
   • 第二个子查询中，rownumber直接是ROW_NUMBER() OVER (...)返回的数字类型；
     UNION ALL要求对应列类型完全一致，这种字符串+数字的组合会导致隐式转换冲突，而FETCH FIRST ROW WITH TIES需要严格校验排序列的类型一致性，因此触发错误。

2. TRANSACTION_COST列类型潜在不匹配
   第二个子查询中NVL(to_char((LN.UNIT_PRICE / UOM.CONVERSION_RATE),'FM99990.90'),LN.UNIT_PRICE)的两个参数类型不一致：第一个是字符串，第二个是数字（LN.UNIT_PRICE）。Oracle会隐式将字符串转为数字，但这会导致该列在UNION ALL时与第一个子查询的字符串类型TRANSACTION_COST不匹配，进一步加剧类型冲突。

针对上述问题，修改SQL的两个关键部分：

1. 统一UNION ALL对应列的数据类型 #

将两个子查询的行号列统一为数字类型，同时确保TRANSACTION_COST列统一为字符串类型：

修改第一个子查询的rownum1定义 #

把：

TO_CHAR(ROW_NUMBER() OVER (PARTITION BY A.INVENTORY_ITEM_ID, C.SUBINVENTORY_CODE ORDER BY A.INVENTORY_ITEM_ID, B.COST_DATE DESC)) rownum1

改为：

ROW_NUMBER() OVER (PARTITION BY A.INVENTORY_ITEM_ID, C.SUBINVENTORY_CODE ORDER BY A.INVENTORY_ITEM_ID, B.COST_DATE DESC) rownum1

修改第二个子查询的TRANSACTION_COST定义 #

把：

NVL(to_char((LN.UNIT_PRICE / UOM.CONVERSION_RATE),'FM99990.90'),LN.UNIT_PRICE) TRANSACTION_COST

改为：

NVL(to_char((LN.UNIT_PRICE / UOM.CONVERSION_RATE),'FM99990.90'),to_char(LN.UNIT_PRICE,'FM99990.90')) TRANSACTION_COST

确保NVL的两个参数都是字符串类型，与第一个子查询的TRANSACTION_COST类型一致。

2. 优化排序方式（可选） #

建议明确排序列名称而非使用位置序号，避免因列顺序变化导致错误，比如把ORDER BY 4 DESC改为ORDER BY rownumber DESC。

Select * from 
( ( 
SELECT item_number,item_DESCRIPTION     
      ,decode(substr(subinventory_code,1,3),'11C','11CCL'
              ,'11O','11OPR'
              ,'18O','18OPR'                  
              ,subinventory_code
             ) subinventory_code
      ,p_inv,
       to_char(round(Cost,2),'FM99990.90') Cost,           
       patient_charges, 
       rownum ivt_seqnum  -- 修正：删除多余逗号
       ,CONSIGNED_FLAG  
FROM (
SELECT DISTINCT   
                ESI.item_number,
                replace(replace(ESI.LONG_DESCRIPTION,chr(10)),chr(13)) item_DESCRIPTION,
                IOQD.subinventory_code,
                IOQD.inventory_item_id p_inv,
                to_char(TC.TRANSACTION_COST,'FM99990.99') Cost,                    
                ESI.attribute3, patient_charges,  -- 修正：添加缺失逗号
                ESI.CONSIGNED_FLAG 
                          
FROM   inv_onhand_quantities_detail IOQD,
       egp_system_items ESI,
       
       (SELECT * FROM 
         (SELECT A.INVENTORY_ITEM_ID
                ,C.SUBINVENTORY_CODE,                    
                to_char(B.TRANSACTION_COST,'FM99990.90') TRANSACTION_COST
               -- 关键修改：去掉TO_CHAR，统一为数字类型
               ,ROW_NUMBER() OVER (PARTITION BY A.INVENTORY_ITEM_ID, C.SUBINVENTORY_CODE ORDER BY A.INVENTORY_ITEM_ID, B.COST_DATE DESC) rownum1
                      FROM CST_ONHAND_V A,
                           CST_ITEM_COST_HISTORY_V B,
                           CST_TXN_LAYER_DTLS_V C
                     WHERE 1=1
                       AND A.INVENTORY_ITEM_ID= :p_inv_num
                       AND A.REC_TRXN_ID=B.TRANSACTION_ID                  
                       AND A.INVENTORY_ITEM_ID=B.INVENTORY_ITEM_ID                             
                       AND A.REC_TRXN_ID=C.REC_TRXN_ID                         
                       
                        
    ORDER BY B.COST_DATE DESC NULLS LAST
    ) 
    WHERE rownum1 = 1  AND TRANSACTION_COST IS NOT NULL 
    
    UNION ALL 
    
SELECT * FROM (  
SELECT INV.INVENTORY_ITEM_ID,    
        SEC.SECONDARY_INVENTORY_NAME SUBINVENTORY_CODE,
        -- 关键修改：NVL第二个参数转为字符串，统一类型
        NVL(to_char((LN.UNIT_PRICE / UOM.CONVERSION_RATE),'FM99990.90'),to_char(LN.UNIT_PRICE,'FM99990.90')) TRANSACTION_COST       
        
        ,ROW_NUMBER() OVER(PARTITION BY INV.INVENTORY_ITEM_ID , decode(substr(SEC.SECONDARY_INVENTORY_NAME,1,3),                                                           
                                                  '11C','11CCL'
                                                  ,'11O','11OPR'
                                                  ,'18O','18OPR'                                                                 
                                                 ,SEC.SECONDARY_INVENTORY_NAME ) 
                ORDER BY INV.INVENTORY_ITEM_ID, SEC.SECONDARY_INVENTORY_NAME ) rownumber
    FROM INV_ITEM_SUB_INVENTORIES INV
       ,inv_secondary_inventories SEC
         ,PO_LINES_ALL LN
        ,PO_HEADERS_ALL HDR
        ,INV_UOM_CONVERSIONS UOM       

     WHERE 
      SEC.SECONDARY_INVENTORY_NAME = INV.SECONDARY_INVENTORY         
      AND LN.ITEM_ID = INV.INVENTORY_ITEM_ID
      AND HDR.PO_HEADER_ID = LN.PO_HEADER_ID 
      AND UOM.INVENTORY_ITEM_ID(+) = INV.INVENTORY_ITEM_ID
      AND UOM.UOM_CODE(+) = LN.UOM_CODE
      AND INV.INVENTORY_ITEM_ID = :p_inv_num  )
     -- 优化：使用列名排序而非位置序号
     WHERE rownumber = 1 ORDER BY rownumber DESC FETCH FIRST ROW WITH TIES  
      )   TC
      
      WHERE  1 = 1
       AND IOQD.inventory_item_id = ESI.inventory_item_id
       AND ESI.inventory_item_id = :p_inv_num
        AND TC.subinventory_code(+) = IOQD.subinventory_code
          AND TC.INVENTORY_ITEM_ID(+) = IOQD.INVENTORY_ITEM_ID                   
     ) )   ) results     

原SQL中还有两处语法错误：

• patient_charges, ,rownum ivt_seqnum：多了一个逗号，已删除；
• ESI.attribute3 patient_charges：少了一个逗号，已添加。

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