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!

Sum function in Case statement

Discussion in 'SQL PL/SQL' started by aliahad, Jul 24, 2014.

  1. aliahad

    aliahad Guest

    I am trying to add a field based on criteria but I am getting inconsistent data type error:

    Error Message:

    ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
    00932. 00000 - "inconsistent datatypes: expected %s got %s"
    *Cause:
    *Action:
    Error at Line: 35 Column: 19

    here is the sum statement:

    SUM(
    case
    when
    TRANSCLAIMAUTHINWARDS.MOVEMENTTYPE1 = 'FEE' and
    TRANSCLAIMAUTHINWARDS.MOVEMENTTYPE2 = 'MGF' and
    transclaimauthinwards.RevenueCode = 'MCF'
    then
    TRANSCLAIMAUTHINWARDS.MOVEMENTAMTORIG
    else '0'
    end) as NACHA_FEES

    Any help is appreciated...
     
  2. jagadekara

    jagadekara Forum Guru

    Messages:
    1,126
    Likes Received:
    145
    Trophy Points:
    1,805
    Location:
    Pulivendula
    Hi,

    Use this....

    SUM(
    case
    when
    TRANSCLAIMAUTHINWARDS.MOVEMENTTYPE1 = 'FEE' and
    TRANSCLAIMAUTHINWARDS.MOVEMENTTYPE2 = 'MGF' and
    transclaimauthinwards.RevenueCode = 'MCF'
    then
    TRANSCLAIMAUTHINWARDS.MOVEMENTAMTORIG
    else 0
    end) as NACHA_FEES
     
  3. ocprep

    ocprep Forum Advisor

    Messages:
    277
    Likes Received:
    76
    Trophy Points:
    410
    Location:
    Orlando, Florida
    ...this, of course, assumes that TRANSCLAIMAUTHINWARDS.MOVEMENTAMTORIG is a NUMBER column. If it is a character column, then additional syntax may be required -- especially if it contains non-numeric data.
     
  4. Rajesh Sinha

    Rajesh Sinha Active Member

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    HYderabad
    should be number which needs to be summed..up