Please be aware that the SAP SQL Anywhere Forum will be shut down on August 29th, 2024 when all it's content will be migrated to the SAP Community.

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?

    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 '19, 18:23

Rolle's gravatar image

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 '19, 18:40) Vlad

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

(23 Feb '19, 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 '19, 11:09) Volker Barth

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

(24 Feb '19, 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 '19, 14:04) Mark Culp

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

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

What about trying something like this:

    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 
FROM dba.Company

Note, this code is not tested.

permanent link

answered 24 Feb '19, 14:10

Mark%20Culp's gravatar image

Mark Culp
accept rate: 41%

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

    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 '19, 09:45

Breck%20Carter's gravatar image

Breck Carter
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":

permanent link

answered 25 Feb '19, 04:10

Volker%20Barth's gravatar image

Volker Barth
accept rate: 34%

edited 25 Feb '19, 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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 23 Feb '19, 18:23

question was seen: 3,825 times

last updated: 25 Feb '19, 05:27