Case statement in a where clause with IN

[former_SQLA_member1694869]

I try to use case statement in a where clause, but I don't get it working properly. This is how I want it to work, but this is not possible. How do I do this in the best way?

where 
    Matnr not like 'A%' and
    CustomerCode not in( case (select trim(CompanyName) from dba.Company) 
                           when 'Company 1' then string('1','2','3')
                           when 'Company 2' then string('100','300','400') 
                           when 'Company 3' then string('1000','2000','3000','4000') 
                           else '-1' end)

[Vlad]

Is this question (and few others) related to SqlAnywhere, or a general study of SQL? I am just curious, looks like a free consulting work.

[former_SQLA_member1694869]

Of course, this applies to Sql Anywhere. And it's for a report I'm trying to create.

[VolkerBarth]

Note, the STRING function concatenates strings to one longer one. It won't build a list of strings that can be used with an IN clause...

For example you will get the test IN ('123') for company 1. Is that the desired outcome or IN (1, 2, 3)?

[former_SQLA_member1694869]

I want to get results IN (1, 2, 3). So it's wrong of me to use string.

[MarkCulp]

Correct, you do not want to use string() since this function simply concatenates all of its input parameters.

[former_SQLA_member1694869]

How do I then if the condition should be set to IN (1, 2, 3) etc.?

[MarkCulp · Accepted solution]

What about trying something like this:

WHERE 
    Matnr not like 'A%' and
    1 = CASE TRIM ( CompanyName ) 
     WHEN 'Company 1' then
       if CustomerCode NOT IN (1,2,3) then 1 else 0 endif
     WHEN 'Company 2' then
       if CustomerCode NOT IN (100,200,300) then 1 else 0 endif   
     WHEN 'Company 3' then
       if CustomerCode NOT IN (1000,2000,3000) then 1 else 0 endif  
     ELSE 0 
    END CASE
FROM dba.Company

Note, this code is not tested.

[Breck_Carter]

You can use a CASE expression (not CASE statement) as the single item in a SELECT list, and then use that SELECT in a NOT IN ( subquery ) predicate.

CAUTION: The following code HAS NOT been tested...

where 
    Matnr not like 'A%' and
    CustomerCode NOT IN ( SELECT CASE TRIM ( CompanyName ) 
                                    WHEN 'Company 1' then string('1','2','3')
                                    WHEN 'Company 2' then string('100','300','400') 
                                    WHEN 'Company 3' then string('1000','2000','3000','4000') 
                                    ELSE '-1' 
                                 END CASE
                            FROM dba.Company ) 

Note that CASE expressions are far more useful than CASE statements.

[VolkerBarth]

A different approach would be to build a virtual vable (for example via a common table expression or a derived table) that joins CompanyName with the excluding CustomerCode and use a LEFT JOIN to exclude those rows from the result set.

Something like (assuming your table is named X):

from X ... 
left join 
   (select 'Company1' as CompanyName, row_value as CustomerCode
       from sa_split_list('1, 2, 3', ', ') C1
    union all 
    select 'Company2', row_value
       from sa_split_list('100, 300, 400', ', ') C2
    union all 
    select 'Company3', row_value
       from sa_split_list('1000, 2000, 3000, 4000', ', ') C3
    order by 1, 2) ExcludeCodes
on X.CompanyName = ExcludeCodes.CompanyName and X.CustomerCode = ExcludeCodes.CustomerCode
where Matnr not like 'A%' and ExcludeCodes.CompanyName is null

sa_split_list() is handy to build tables from a string list.

Here's the contents of the derived table "ExcludeCodes":

CompanyName,CustomerCode
Company1,1
Company1,2
Company1,3
Company2,100
Company2,300
Company2,400
Company3,1000
Company3,2000
Company3,3000
Company3,4000