Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52

    Angry Unanswered: Error: IS [NOT] NULL predicate may be used only with simple columns. (State:S1000, Na

    Anyone know of a way to use CASE with SUM and NOT NULL in an INFORMIX SQL SELECT statement?

    The below SQL works fine in SQL SERVER and DB2 but INFORMIX doesn't like it. (For info - there are no rows matching on bom for bomparent = 'XXX')


    SELECT CASE WHEN SUM(bomweight) IS NULL THEN 0 ELSE SUM(bomweight) END AS weight
    FROM bom WHERE bomparent = 'XXX'

    Error: IS [NOT] NULL predicate may be used only with simple columns. (State1000, Native Code: FFFFFEDB)

    Now I could use the ANSI standard COALESCE() function (similar to SQL SERVER ISNULL()) to obtain the same result but again unfortunately INFORMIX does not support this either.

    SELECT COALESCE(SUM(bomweight), 0) AS weight
    FROM bom WHERE bomparent = 'XXX'

    -Under SQLSERVER and DB2 the result is one row:
    WEIGHT
    0.0

    Thanks in advance for your help on this.

    Andy
    ahmatexeldotcodotuk

  2. #2
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    use the funciont NVL
    Code:
    SELECT NVL(SUM(bomweight) ,0) AS weight
    FROM bom WHERE bomparent = 'XXX'
    But this can retreive undesirable result ..

    the corret way to use is:
    Code:
    SELECT SUM(NVL(bomweight,0)) AS weight
    FROM bom WHERE bomparent = 'XXX'
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

  3. #3
    Join Date
    Dec 2003
    Location
    Nottingham, England
    Posts
    52

    RE: use the funciont NVL

    Thanks for that - your a star.

    Cheers.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    ceinma, what "undesirable result" does NVL(SUM(bomweight),0) produce?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Apr 2007
    Location
    Jundiai / SP - Brasil
    Posts
    311
    on reality, with sum and any other aggregate function the NULLs values are ignored...so, works too. But just to a good practice must avoid this kind of code... my opinion..
    ________________________________________
    César Inacio Martins
    Jundiai / SP - Brasil
    http://www.imartins.com.br/informix - em Português
    http://www.imartins.com.br/informix - English (translated by Google).
    ________________________________________

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •