Use of SQL Case

[Viking]

I have a file with a field for account number, and then nine similar fields that can contain a 3 letter code.

For a set of account numbers that I already have in a cursor, I need to find a certain code (in this case 'GNS') that can exist in any one of those nine code fields, and remove it.

Can I do something like this? What is the correct syntax to do what I would like to do?

Exec Sql
update MYFILE
case CODE1 = 'GNS'
set CODE1 = ' ',
case CODE2 = 'GNS'
set CODE2 = ' ',
case CODE3 = 'GNS'
set CODE3 = ' ',
case CODE4 = 'GNS'
set CODE4 = ' ',
case CODE5 = 'GNS'
set CODE5 = ' ',
case CODE6 = 'GNS'
set CODE6 = ' ',
case CODE7 = 'GNS'
set CODE7 = ' ',
case CODE8 = 'GNS'
set CODE8 = ' ',
case CODE9 = 'GNS'
set CODE9 = ' ',
where ACCOUNT = :custAcct;

Thanks in advance!

[tomliotta]

A basic statement would be something like this:

UPDATE MYFILE
 SET CODE1 = case when CODE1 = 'GNS'
                  then ' '
                  else CODE1 end
 where ACCOUNT = :custAcct;

You might prefer it to be like this rather than referencing the column name in the ELSE clause:

UPDATE MYFILE
 SET CODE1 = case when CODE1 = 'GNS'
                  then ' '
                  else 'GNS' end
 where ACCOUNT = :custAcct;

I don't know if one would be better than the other.

From the basic form, it could be expanded to cover two columns like this:

update MYFILE set (CODE1, CODE2) = (
    case when CODE1 = 'GNS'
                  then ' '
                  else CODE1 end,
    case when CODE2 = 'GNS'
                  then ' '
                  else CODE2 end
   )
  where ACCOUNT = :custAcct;

Each additional column should be added similarly. By using 'GNS' in the ELSE clause, there'd be a slightly smaller risk of a typo, I suppose.

In the SET clause, I chose to list column names separately from each associated CASE statement. They could be written as "SET CODE1 = CASE ... , CODE2 = CASE ... , CODE3 = CASE ..." , etc. For me, it's easier to list the two groups, both groups enclosed in parentheses.

[Viking]

Thank you, that worked very well!