Search code examples
sqlibm-midrangedb2-400

I need help defining a case statement in SQL db2-400

I am using SQL, IBM Data Studio, db2-400. I need help writing a case statement that looks for the length of a field and returns a blank if it is not equal to 14, I have tried many ways without success, below is my latest attempt.

CASE 
   WHEN LENGTH(TRIM(FFIUPCN) <> '14' 
     THEN '' 
   ELSE 
     FFIUPCN 
 END AS "UPC (if applicable)"

The error I get is that the token "<>" is not valid.

Solution

  • Main issue is that you are missing a )

    CASE 
   WHEN LENGTH(TRIM(FFIUPCN)) <> 14 
     THEN '' 
   ELSE 
     FFIUPCN 
 END AS "UPC (if applicable)"

    but you shouldn't be comparing a numeric, returned by length(), to a sring '14'. But Db2 is implicitly converting it.