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) |
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. |
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. |
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 |
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.
Of course, this applies to Sql Anywhere. And it's for a report I'm trying to create.
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)?
I want to get results IN (1, 2, 3). So it's wrong of me to use string.
Correct, you do not want to use string() since this function simply concatenates all of its input parameters.
How do I then if the condition should be set to IN (1, 2, 3) etc.?