|
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.?