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!

ORA-00909: invalid number of arguments

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

  1. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    The SQL query included here ends with the ORA-00909: invalid number of arguments Error. Everything I've read both on the net and on this site, seems to point towards the concatenation portion of my query. I copied that portion of my query from another query that actually runs and produces results. So, I'm having doubts that it is the source of the problem. Can you help? Thanks in advance!

    Code (SQL):
    SELECT rcs.T_CASE_ID, rcs.T_CASE_OPEN_DT, d.B_CURR_ALT_ID,
           d.B_LAST_NAM || ' ' || d.B_SFX_NAM || ', ' ||
           d.B_FIRST_NAM || ' ' || d.B_MID_NAM AS Name,
           CASE
             WHEN x.G_SPEC_ENTY_TY_CD = 'I'
             THEN x.T_INTRSTD_PARTY_REP_NAM_TEXT
           END AS Interested,
            CASE
              WHEN b.T_CASE_BLNG_AMT > 0
                   AND MIN(b.T_CASE_BLNG_DT) >
                       TO_DATE('01/01/0001', 'MM/DD/YYYY')
              THEN b.T_CASE_BLNG_AMT
            END AS OrigReqClm,
           CASE
             WHEN c.T_RCVRY_CLM_IE_CD <> 'E'
             THEN SUM(c.T_RCVRY_CLM_REQ_AMT)
           END AS InclClmTotAmt,
           SUM(crsp,T_CASE_RESP_AMT) TotRecvdAmt
    FROM T_RCVRY_CASE_TB rcs
    JOIN G_USER_TB u
      ON rcs.T_CASE_RSPNSBL_USER_ID = u.G_USER_ID
    JOIN B_DTL_TB d
      ON d.B_SYS_ID = rcs.B_SYS_ID
    JOIN T_RCVRY_CASE_ENTY_XREF_TB x
      ON x.T_CASE_ID = rcs.T_CASE_ID
    JOIN T_RCVRY_CASE_BLNG_TB b
      ON b.T_CASE_ID = rcs.T_CASE_ID
    JOIN T_RCVRY_CASE_CLM_TB c
      ON c.T_CASE_ID = rcs.T_CASE_ID
    JOIN T_RCVRY_CASE_RESP_TB crsp
      ON crsp.T_CASE_ID = c.T_CASE_ID
         AND crsp.C_TCN_NUM = c.C_TCN_NUM
         AND crsp.LI_NUM = c.LI_NUM
    WHERE T_CASE_RSPNSBL_USER_ID = 'DHAYES05'
      AND rcs.T_CASE_STAT_CD = 'O'
      AND rcs.T_RCVRY_TY_CD = '1';
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Please post the entire error text.
     
  3. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Error at line 18
    ORA-00909: invalid number of arguments
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You have a comma where there should be a period:


    SUM(crsp,T_CASE_RESP_AMT) TotRecvdAmt


    It should be:


    SUM(crsp.T_CASE_RESP_AMT) TotRecvdAmt


    In this case the line number does point to the actual error.
     
  5. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Okay, good grief! Can't believe I missed that comma! Thank you!
    After I fixed it, I got ORA-00937: not a single-group group function. When I add the following to the end of my query:
    Code (SQL):
    GROUP BY rcs.T_CASE_ID, rcs.T_CASE_OPEN_DT, d.B_CURR_ALT_ID,
           Name, Interested, OrigReqClm, InclClmTotAmt
    I get: ORA-00904: "INCLCLMTOTAMT": invalid identifier. Am I right in thinking I have to do a nested Select since Group By is the first thing to be evaluated and it doesn't know any of the "As" names?
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    You don't need to do a 'nested select' but you do need to use the actual column name or full function code from the select in the GROUP BY:


    Code (SQL):

    SELECT rcs.T_CASE_ID, rcs.T_CASE_OPEN_DT, d.B_CURR_ALT_ID,
           d.B_LAST_NAM || ' ' || d.B_SFX_NAM || ', ' ||
           d.B_FIRST_NAM || ' ' || d.B_MID_NAM AS Name,
           CASE
             WHEN x.G_SPEC_ENTY_TY_CD = 'I'
             THEN x.T_INTRSTD_PARTY_REP_NAM_TEXT
           END AS Interested,
            CASE
              WHEN b.T_CASE_BLNG_AMT > 0
                   AND MIN(b.T_CASE_BLNG_DT) >
                       TO_DATE('01/01/0001', 'MM/DD/YYYY')
              THEN b.T_CASE_BLNG_AMT
            END AS OrigReqClm,
           CASE
             WHEN c.T_RCVRY_CLM_IE_CD <> 'E'
             THEN SUM(c.T_RCVRY_CLM_REQ_AMT)
           END AS InclClmTotAmt,
           SUM(crsp.T_CASE_RESP_AMT) TotRecvdAmt
    FROM T_RCVRY_CASE_TB rcs
    JOIN G_USER_TB u
      ON rcs.T_CASE_RSPNSBL_USER_ID = u.G_USER_ID
    JOIN B_DTL_TB d
      ON d.B_SYS_ID = rcs.B_SYS_ID
    JOIN T_RCVRY_CASE_ENTY_XREF_TB x
      ON x.T_CASE_ID = rcs.T_CASE_ID
    JOIN T_RCVRY_CASE_BLNG_TB b
      ON b.T_CASE_ID = rcs.T_CASE_ID
    JOIN T_RCVRY_CASE_CLM_TB c
      ON c.T_CASE_ID = rcs.T_CASE_ID
    JOIN T_RCVRY_CASE_RESP_TB crsp
      ON crsp.T_CASE_ID = c.T_CASE_ID
         AND crsp.C_TCN_NUM = c.C_TCN_NUM
         AND crsp.LI_NUM = c.LI_NUM
    WHERE T_CASE_RSPNSBL_USER_ID = 'DHAYES05'
      AND rcs.T_CASE_STAT_CD = 'O'
      AND rcs.T_RCVRY_TY_CD = '1'
    GROUP BY rcs.T_CASE_ID, rcs.T_CASE_OPEN_DT, d.B_CURR_ALT_ID,
           d.B_LAST_NAM || ' ' || d.B_SFX_NAM || ', ' || d.B_FIRST_NAM || ' ' || d.B_MID_NAM ,
           CASE
             WHEN x.G_SPEC_ENTY_TY_CD = 'I'
             THEN x.T_INTRSTD_PARTY_REP_NAM_TEXT
           END, CASE
              WHEN b.T_CASE_BLNG_AMT > 0
                   AND MIN(b.T_CASE_BLNG_DT) >
                       TO_DATE('01/01/0001', 'MM/DD/YYYY')
              THEN b.T_CASE_BLNG_AMT
            END, CASE
             WHEN c.T_RCVRY_CLM_IE_CD <> 'E'
             THEN SUM(c.T_RCVRY_CLM_REQ_AMT)
           END
    ;
     

    The above code should work.
     
  7. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    I executed that and now get ORA-00934: group function is not allowed here. I thought that might be caused due to the MIN() and the SUM() aggragates being present in the last 2 CASE() 's, but after removing those 2 Cases from the Group By and executing the query, I got ORA-00979: not a GROUP BY expression. I appreciate your perseverance!
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I truly believe that the date comparison is useless, as I expect the company has been in business LONG since 01/01/0001 thus this might work for you:


    Code (SQL):

    WITH recovery_claim_req AS(
     SELECT SUM(t_rcvry_clm_req_amt) InclClmTotAmt
     FROM T_RCVRY_CASE_CLM_TB
     WHERE T_RCVRY_CLM_IE_CD <> 'E'
     UNION
     SELECT 0
     FROM T_RCVRY_CASE_CLM_TB
     WHERE T_RCVRY_CLM_IE_CD = 'E'
    )
    SELECT rcs.T_CASE_ID, rcs.T_CASE_OPEN_DT, d.B_CURR_ALT_ID,
           d.B_LAST_NAM || ' ' || d.B_SFX_NAM || ', ' ||
           d.B_FIRST_NAM || ' ' || d.B_MID_NAM AS Name,
           CASE
             WHEN x.G_SPEC_ENTY_TY_CD = 'I'
             THEN x.T_INTRSTD_PARTY_REP_NAM_TEXT
           END AS Interested,
            CASE
              WHEN b.T_CASE_BLNG_AMT > 0
              THEN b.T_CASE_BLNG_AMT
            END AS OrigReqClm,
           InclClmTotAmt,
           SUM(crsp.T_CASE_RESP_AMT) TotRecvdAmt
    FROM recovery_claim_req, T_RCVRY_CASE_TB rcs
    JOIN G_USER_TB u
      ON rcs.T_CASE_RSPNSBL_USER_ID = u.G_USER_ID
    JOIN B_DTL_TB d
      ON d.B_SYS_ID = rcs.B_SYS_ID
    JOIN T_RCVRY_CASE_ENTY_XREF_TB x
      ON x.T_CASE_ID = rcs.T_CASE_ID
    JOIN T_RCVRY_CASE_BLNG_TB b
      ON b.T_CASE_ID = rcs.T_CASE_ID
    JOIN T_RCVRY_CASE_CLM_TB c
      ON c.T_CASE_ID = rcs.T_CASE_ID
    JOIN T_RCVRY_CASE_RESP_TB crsp
      ON crsp.T_CASE_ID = c.T_CASE_ID
         AND crsp.C_TCN_NUM = c.C_TCN_NUM
         AND crsp.LI_NUM = c.LI_NUM
    WHERE T_CASE_RSPNSBL_USER_ID = 'DHAYES05'
      AND rcs.T_CASE_STAT_CD = 'O'
      AND rcs.T_RCVRY_TY_CD = '1'
    GROUP BY rcs.T_CASE_ID, rcs.T_CASE_OPEN_DT, d.B_CURR_ALT_ID,
           d.B_LAST_NAM || ' ' || d.B_SFX_NAM || ', ' || d.B_FIRST_NAM || ' ' || d.B_MID_NAM ,
           CASE
             WHEN x.G_SPEC_ENTY_TY_CD = 'I'
             THEN x.T_INTRSTD_PARTY_REP_NAM_TEXT
           END, InclClmTotAmt
    ;
     
     
  9. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    Here's where we go over my head! I've never used "UNION" "WITH" before, but I did execute your query and got ORA-00979: not a GROUP BY expression. You are right in your assumption regarding the useless date compare. I needed the MIN(b.T_CASE_BLNG_DT) as a condition of the Case only. Once again, thank you for your efforts in helping me.
     
  10. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    I'm trying, honestly. I apparently missed the boat in the subquery factoring example (the WITH syntax) as I should have made it a join to provide a way to link that data to the rest of the joined tables. My apologies.


    It may be necessary to do a complete re-write of the query using subqueries or subquery factoring. Not having the tables or sample data it's difficult to provide usable examples.
     
    OldSchoolCoder likes this.
  11. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    I appreciate you trying to help. Perhaps I can figure a way from here. Thank you again!
     
  12. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    For #7 th thread...

    Try this... It may help you....

    SELECT a.T_CASE_ID,a.T_CASE_OPEN_DT,a.B_CURR_ALT_ID,a.Name,a.Interested,a.OrigReqClm,a.InclClmTotAmt
    ,sum(a.TotRecvdAmt)
    FROM
    (SELECT rcs.T_CASE_ID, rcs.T_CASE_OPEN_DT, d.B_CURR_ALT_ID,
    d.B_LAST_NAM || ' ' || d.B_SFX_NAM || ', ' ||d.B_FIRST_NAM || ' ' || d.B_MID_NAM AS Name,
    CASE WHEN x.G_SPEC_ENTY_TY_CD = 'I' THEN x.T_INTRSTD_PARTY_REP_NAM_TEXT END AS Interested,
    CASE WHEN b.T_CASE_BLNG_AMT > 0 AND MIN(b.T_CASE_BLNG_DT) > TO_DATE('01/01/0001', 'MM/DD/YYYY')
    THEN b.T_CASE_BLNG_AMT END AS OrigReqClm,
    CASE WHEN c.T_RCVRY_CLM_IE_CD <> 'E' THEN SUM(c.T_RCVRY_CLM_REQ_AMT) END AS InclClmTotAmt,
    crsp.T_CASE_RESP_AMT TotRecvdAmt
    FROM T_RCVRY_CASE_TB rcs
    JOIN G_USER_TB u
    ON rcs.T_CASE_RSPNSBL_USER_ID = u.G_USER_ID
    JOIN B_DTL_TB d
    ON d.B_SYS_ID = rcs.B_SYS_ID
    JOIN T_RCVRY_CASE_ENTY_XREF_TB x
    ON x.T_CASE_ID = rcs.T_CASE_ID
    JOIN T_RCVRY_CASE_BLNG_TB b
    ON b.T_CASE_ID = rcs.T_CASE_ID
    JOIN T_RCVRY_CASE_CLM_TB c
    ON c.T_CASE_ID = rcs.T_CASE_ID
    JOIN T_RCVRY_CASE_RESP_TB crsp
    ON crsp.T_CASE_ID = c.T_CASE_ID
    AND crsp.C_TCN_NUM = c.C_TCN_NUM
    AND crsp.LI_NUM = c.LI_NUM
    WHERE T_CASE_RSPNSBL_USER_ID = 'DHAYES05'
    AND rcs.T_CASE_STAT_CD = 'O'
    AND rcs.T_RCVRY_TY_CD = '1'
    ) a
    GROUP BY a.T_CASE_ID,a.T_CASE_OPEN_DT,a.B_CURR_ALT_ID,a.Name,a.Interested,a.OrigReqClm,a.InclClmTotAmt
    ;
     
    OldSchoolCoder likes this.
  13. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    I tried this query and got ORA-00937: not a single-group group function. The good news is that I've got it fixed. Just an FYI, I flipped things around and found something rather odd. All I did was change the InclClmTotAmt Case statement to this:
    Code (SQL):
    SUM(CASE
                 WHEN c.T_RCVRY_CLM_IE_CD <> 'E'
                 THEN c.T_RCVRY_CLM_REQ_AMT
               END) AS InclClmTotAmt,
    I don't understand why it worked this way and not with the sum inside of the Case. Can anyone explain that? Thanks for all your help!
     
  14. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Simply because it runs without error doesn't mean it returns the correct result; that construct will return the sum of T_RCVRY_CLM_REQ_AMT for the entire table, which may not be what you want.
     
    OldSchoolCoder likes this.
  15. OldSchoolCoder

    OldSchoolCoder Active Member

    Messages:
    42
    Likes Received:
    0
    Trophy Points:
    80
    I'm going to try to appear intelligent when I ask this question... If you have a Case statement that says you want an amount only on the condition specified in the When clause, wouldn't you get no amount if the When clause is not met? I don't see how the entire table would be summed. I apologize for my ignorance, I'm fairly new at this.
     
  16. DTSIGuy

    DTSIGuy Forum Advisor

    Messages:
    402
    Likes Received:
    58
    Trophy Points:
    410
    Location:
    Texas
    That should be easy enough to check...SUM the column WITHOUT the CASE statement. If you get the same results then you may have a problem. If it's larger then the CASE statement would be doing what you want... ;-)

    CJ
     
    OldSchoolCoder likes this.
  17. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    My confusion, I suppose, is that the sum() would be across the entire table for the conditions met; I would have expected you to want analytic sums based on a given record (a group by, if you will). My goal was to bring this to your attention to get the sum verified (that is, the sum you see is the sum you want).


    I really wasn't trying to be confusing. :)
     
    OldSchoolCoder likes this.