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)

asked 23 Feb, 18:23

Rolle's gravatar image

Rolle
492343650
accept rate: 0%

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.

(23 Feb, 18:40) Vlad

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

(23 Feb, 19:03) Rolle

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

(24 Feb, 11:09) Volker Barth

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

(24 Feb, 11:13) Rolle
Replies hidden

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

(24 Feb, 14:04) Mark Culp

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

(24 Feb, 14:09) Rolle
showing 5 of 6 show all flat view

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.

permanent link

answered 24 Feb, 14:10

Mark%20Culp's gravatar image

Mark Culp
24.1k9134285
accept rate: 40%

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.

permanent link

answered 24 Feb, 09:45

Breck%20Carter's gravatar image

Breck Carter
28.9k481645941
accept rate: 20%

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
permanent link

answered 25 Feb, 04:10

Volker%20Barth's gravatar image

Volker Barth
33.5k330483711
accept rate: 32%

edited 25 Feb, 05:27

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×13

question asked: 23 Feb, 18:23

question was seen: 170 times

last updated: 25 Feb, 05:27