ORA-00904无效标识符错误

Question

当我向代码中添加聚合函数时，我在识别代码中的错误时遇到了困难。每个submitterclaimnumber项都有多个行，每个行的值在seqnumber列下都在增加。

我正在尝试为每个特定的seqnumber提取具有最高submitterclaimnumber的行。

例如。我有一个多行的submitterclaimnumber 999。每一行在seqnumber下被区分为不同的值，因此我有：submitterclaimnumber = 999 seqnumber =1 submitterclaimnumber = 999 seqnumber =2 submitterclaimnumber = 999 seqnumber =3

我只想要显示submitterclaimnumber = 999 seqnumber =3。

如果需要，我可以提供关于如何设置表的更多信息。

我得到的错误是：

“选择失败。904: ORA-00904："MAX_SEQ"：无效标识符”

SELECT
    CR.CLAIMNUMBER  
    ,CR.LINENUMBER  
    ,SUBSTR(CLM.SUBMITTERCLAIMNUMBER,3,11) AS CLM_NBR
    ,CR.SEQNUMBER   
    ,MAX(CR.SEQNUMBER) AS MAX_SEQ 
    ,CR.VOLUME  
    ,CR.VOLUMETYPE  
    ,CR.RATE    
    ,CR.RATETYPE    
    ,CR.ALLOWED     
FROM PRICER.CLMREPRICINGDETAIL CR   
INNER JOIN PRICER.CLMCLAIMS CLM 
    ON  CLM.CLAIMNUMBER = CR.CLAIMNUMBER    
INNER JOIN PRICER.CLMCLAIMITEMS CLMP 
    ON CLM.CLAIMNUMBER = CLMP.CLAIMNUMBER 
        AND CLM.OWNERID = CLMP.OWNERID   
INNER JOIN PRICER.CLMINSURED CLMI 
    ON CLM.CLAIMNUMBER = CLMI.CLAIMNUMBER 
        AND CLM.OWNERID = CLMI.OWNERID      
WHERE 
    TRADEPARTNERID IN ('NationalNonHouse','NationalHouse')
    AND SUBSTR(CLM.SUBMITTERCLAIMNUMBER,3,11) IN ('17304CD2083','17297CA4107')  
GROUP BY 1,2,3,4,5,6,7,8,9,10   
HAVING CR.SEQNUMBER =  MAX_SEQ 
; 

Answer 1

您可能正在使用Teradata助手查询Oracle系统，但没有针对Teradata :-)使用它。

要获得具有最大SEQNUMBER的行，您不能使用拥有，因为在聚合之后，没有更多的单独行可与之相比。但是有窗口的集合允许这样做：

SELECT *
FROM
 (
    SELECT
        CR.CLAIMNUMBER  
        ,CR.LINENUMBER  
        ,SUBSTR(CLM.SUBMITTERCLAIMNUMBER,3,11) AS CLM_NBR
        ,CR.SEQNUMBER   
        ,MAX(CR.SEQNUMBER) OVER (PARTITON BY SUBMITTERCLAIMNUMBER) AS MAX_SEQ 
        ,CR.VOLUME  
        ,CR.VOLUMETYPE  
        ,CR.RATE    
        ,CR.RATETYPE    
        ,CR.ALLOWED     
    FROM PRICER.CLMREPRICINGDETAIL CR   
    INNER JOIN PRICER.CLMCLAIMS CLM 
        ON  CLM.CLAIMNUMBER = CR.CLAIMNUMBER    
    INNER JOIN PRICER.CLMCLAIMITEMS CLMP 
        ON CLM.CLAIMNUMBER = CLMP.CLAIMNUMBER 
            AND CLM.OWNERID = CLMP.OWNERID   
    INNER JOIN PRICER.CLMINSURED CLMI 
        ON CLM.CLAIMNUMBER = CLMI.CLAIMNUMBER 
            AND CLM.OWNERID = CLMI.OWNERID      
    WHERE 
        TRADEPARTNERID IN ('NationalNonHouse','NationalHouse')
        AND SUBSTR(CLM.SUBMITTERCLAIMNUMBER,3,11) IN ('17304CD2083','17297CA4107')  
 -- no more GROUP BY
 ) dt
WHERE SEQNUMBER =  MAX_SEQ 
;

顺便说一句，在Teradata中，您可以删除派生表，只需使用QUALIFY。然后，它看起来非常接近您的原始语法：

QUALIFY
   CR.SEQNUMBER
   = MAX(CR.SEQNUMBER) OVER (PARTITON BY CLM.SUBMITTERCLAIMNUMBER) 

Answer 2

1. 不能在查询中使用别名，必须在MAX(CR.SEQNUMBER)子句中使用HAVING
2. 不能将非聚合行(如CR.SEQNUMBER )在HAVING中进行比较

SELECT
    CR.CLAIMNUMBER  
    ,CR.LINENUMBER  
    ,SUBSTR(CLM.SUBMITTERCLAIMNUMBER,3,11) AS CLM_NBR
    ,CR.SEQNUMBER   
    ,MAX(CR.SEQNUMBER) AS MAX_SEQ 
    ,CR.VOLUME  
    ,CR.VOLUMETYPE  
    ,CR.RATE    
    ,CR.RATETYPE    
    ,CR.ALLOWED     
FROM PRICER.CLMREPRICINGDETAIL CR   
INNER JOIN PRICER.CLMCLAIMS CLM 
    ON  CLM.CLAIMNUMBER = CR.CLAIMNUMBER    
INNER JOIN PRICER.CLMCLAIMITEMS CLMP 
    ON CLM.CLAIMNUMBER = CLMP.CLAIMNUMBER 
        AND CLM.OWNERID = CLMP.OWNERID   
INNER JOIN PRICER.CLMINSURED CLMI 
    ON CLM.CLAIMNUMBER = CLMI.CLAIMNUMBER 
        AND CLM.OWNERID = CLMI.OWNERID      
WHERE 
    TRADEPARTNERID IN ('NationalNonHouse','NationalHouse')
    AND SUBSTR(CLM.SUBMITTERCLAIMNUMBER,3,11) IN ('17304CD2083','17297CA4107')  
GROUP BY
     CR.CLAIMNUMBER
    ,CR.LINENUMBER
    ,SUBSTR(CLM.SUBMITTERCLAIMNUMBER,3,11)
    ,CR.SEQNUMBER
    ,CR.VOLUME
    ,CR.VOLUMETYPE
    ,CR.RATE
    ,CR.RATETYPE
    ,CR.ALLOWED
-- HAVING CR.SEQNUMBER =  MAX(CR.SEQNUMBER) -- not allowed
;