1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Using When MIN() in a Case Statement gets "ORA-00920: invalid relational operator"

Discussion in 'SQL PL/SQL' started by OldSchoolCoder, Mar 6, 2014.

  1. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    I'm fairly new to writing SQL so I'm not 100% sure about syntax. I get "ORA-00920: invalid relational operator" when I run my query. I believe it is due to the MIN() in my Case Statement. The goal of the Case statement is add the 2 columns together (COUNT(cl.C_TCN_NUM) + COUNT(cl.C_LI_NUM)) when the cl.T_INVC_SENT_DT is the oldest date in the database. Since there are so many other variables for other columns that I want in my results, I thought Case was the best way to tackle this. If there's an alternative, I'm open to rewriting my query. I'm aware that there are other issues in my query - if you see them, I'll gladly take advice. Thank you!

    Code (SQL):
    SELECT cl.T_INVC_NUM, MIN(ist.T_INVC_SENT_DT) AS "Orig Inv Mnth/Yr",
           CASE
             WHEN h.C_TY_CD <> 'P' THEN SUM(h.C_TOT_CHRG_AMT)  
             WHEN rxh.C_TY_CD = 'P' THEN SUM(rxh.C_TOT_CHRG_AMT)
           END AS "Prov Billed Amt",
           inv.T_INVC_TOT_ORIG_BLNG_AMT AS "Amt Req",
           CASE
             WHEN MIN(cl.T_INVC_SENT_DT)
             THEN COUNT(cl.C_TCN_NUM) + COUNT(cl.C_LI_NUM)
           END "LIcnt",
           CASE
             WHEN (rsp.T_BLNG_RESP_RSN_CD LIKE 'R%'
                  AND rsp.T_BLNG_RESP_RSN_CD <> 'R99')
                  AND MAX(rsp.T_BLNG_RESP_DT)
             THEN COUNT(DISTINCT rsp.C_TCN_NUM) + COUNT(rsp.C_LI_NUM)
           END "CarrierPayCnt",
           SUM(rsp.T_BLNG_RESP_AMT) AS "Carrier Pay Amt",
           CASE
             WHEN (rsp.T_OVRG_AMT IS NOT NULL
              AND rsp.T_OVRG_AMT > 0)
             THEN COUNT(DISTINCT rsp.C_TCN_NUM) + COUNT(rsp.C_LI_NUM)
           END "OverPayCnt",
           SUM(rsp.T_OVRG_AMT) AS "Over-pay Amt",
           CASE
             WHEN rsp.T_BLNG_RESP_RSN_CD LIKE 'D%'
                  AND MAX(T_BLNG_RESP_DT)
             THEN COUNT(rsp.C_TCN_NUM) + COUNT(rsp.C_LI_NUM)
           END "CarrDenyCnt",  
           CASE
             WHEN rsp.T_BLNG_RESP_RSN_CD LIKE 'D%'
                  AND MAX(T_BLNG_RESP_DT)
             THEN SUM(rsp.T_BLNG_AMT)
           END AS "Carr Deny Amt",  
           CASE
             WHEN rsp.T_BLNG_RESP_RSN_CD = 'R99'
                  AND MAX(T_BLNG_RESP_DT)
             THEN COUNT(rsp.C_TCN_NUM) + COUNT(rsp.C_LI_NUM)
           END "WriteOffCnt",
           CASE
             WHEN rsp.T_BLNG_RESP_RSN_CD = 'R99'
                  AND MAX(T_BLNG_RESP_DT)
             THEN SUM(cl.T_BLNG_AMT)
           END AS "Write-off Amt"
    FROM T_BLNG_INVC_TB inv
    JOIN T_CARR_TB c
      ON c.T_CARR_ID = inv.T_CARR_ID
    JOIN T_BLNG_INVC_SENT_TB ist
      ON ist.T_INVC_NUM = inv.T_INVC_NUM
    JOIN T_BLNG_INVC_SENT_CLM_TB cl
      ON cl.T_INVC_NUM = ist.T_INVC_NUM
         AND cl.T_INVC_SENT_DT = ist.T_INVC_SENT_DT
    JOIN T_BLNG_RESP_TB rsp
      ON rsp.T_INVC_NUM = inv.T_INVC_NUM
    JOIN C_HDR_TB h
      ON h.C_TCN_NUM = rsp.C_TCN_NUM
    JOIN C_RX_HDR_TB rxh
      ON rxh.C_TCN_NUM = rsp.C_TCN_NUM
    WHERE inv.T_CARR_ID = '0000000000'
    AND (inv.T_INVC_CLOSE_DT IS NULL
           OR inv.T_INVC_CLOSE_DT > TO_DATE('12/31/2099', 'MM/DD/YYYY'))
      AND inv.T_INVC_CLOSE_RSN_CD IS NULL  
    ORDER BY MIN(ist.T_INVC_SENT_DT);
     
  2. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Re: Using When MIN() in a Case Statement gets "ORA-00920: invalid relational operator

    Hmm - I'm still working on this and I believe I've overcome the error. I replaced the Case statement in question with a Select statement (
    Code (SQL):
    (SELECT (COUNT(cl2.C_TCN_NUM) + COUNT(cl2.C_LI_NUM))
           FROM T_BLNG_INVC_SENT_CLM_TB cl2  
           WHERE MIN(cl.T_INVC_SENT_DT)
           ) LIcnt,
    but now I'm getting "ORA-00934: group function is not allowed here". I could use some help with that one...
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Re: Using When MIN() in a Case Statement gets "ORA-00920: invalid relational operator

    To begin with you have the beginnings of a WHERE clause without actually completing it. There MUST be a comparison operator and an operand for it to succeed and you have neither, as all your WHERE clause states is:


    WHERE MIN(cl.T_INVC_SENT_DT))


    which makes no sense to Oracle. You must compare it to some value, either with =, <>, >, <, or some other numeric comparison operator plus a reference value for that comparison. Also you can't use MIN(), MAX(), SUM(), AVG() or other such aggregate functions in a WHERE clause; that is why there is the HAVING clause for GROUP BY queries:


    Code (SQL):

    SQL> SELECT store_name, location, SUM(sales) ttl_sales, MAX(salesdate) last_sales_dt
      2  FROM store_information
      3  GROUP BY store_name, location
      4  HAVING SUM(sales) <
      5  (SELECT avg(sales) FROM store_information)
      6  ORDER BY 4;


    STORE_NAME           LOCATION              TTL_SALES LAST_SALE
    -------------------- -------------------- ---------- ---------
    Dimp Estranoz        Yardley                       0 01-MAR-14


    SQL>
     

    Notice that the HAVING clause was used to 'compare' the sales sum to the sales average. A WHERE clause can't do that directly.
     
    OldSchoolCoder likes this.
  4. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Re: Using When MIN() in a Case Statement gets "ORA-00920: invalid relational operator

    Thank you very much for your reply and your assistance. I will apply that logic to my query and give it a whirl.