I want a query something similar to the below condition . This does not work...how should I do?

WHERE
    tblMaterial.mat_type IN(1,3,7) AND

    CASE Len(@Mat2)
    WHEN 0 THEN
        tblMaterial.mat_nr = @Mat1
    ELSE
        tblMaterial.mat_nr Between @Mat1 And @Mat2
    END

ORDER BY
    tblMaterial.mat_nr

asked 12 Dec '14, 15:22

Rolle's gravatar image

Rolle
558495161
accept rate: 0%


Sadly, the CASE operator must return an expression of some valid SQL Anywhere data type, something that (in theory) can be assigned to a variable of that data type, and there is no boolean (TRUE/FALSE/UNKNOWN) data type that can be stored in a variable.

That means you can't code this:

WHERE
    tblMaterial.mat_type IN(1,3,7) AND
    CASE Len(@Mat2)
    WHEN 0 THEN
        tblMaterial.mat_nr = @Mat1
    ELSE
        tblMaterial.mat_nr Between @Mat1 And @Mat2
    END

The following should work, and although it looks quite different from the CASE, it closely agrees with your English explanation of the semantics:

WHERE
    tblMaterial.mat_type IN(1,3,7) 
    AND (    ( Len(@Mat2) = 0  AND tblMaterial.mat_nr = @Mat1 )
          OR ( Len(@Mat2) <> 0 AND tblMaterial.mat_nr Between @Mat1 And @Mat2 ) )
permanent link

answered 13 Dec '14, 11:30

Breck%20Carter's gravatar image

Breck Carter
32.5k5417261050
accept rate: 20%

edited 15 Dec '14, 05:39

Volker%20Barth's gravatar image

Volker Barth
40.1k361549819

1

If @Mat2 has no value

The above will work unless "@Mat2 has no value" should read "@Mat2 is null" - in the latter case len(@Mat2) will return NULL, not 0.

In case @Mat2 cannot be null, Breck's condition might be simplified to

WHERE
    tblMaterial.mat_type IN(1,3,7) 
    AND (    tblMaterial.mat_nr = @Mat1
          OR ( Len(@Mat2) <> 0 AND tblMaterial.mat_nr Between @Mat1 And @Mat2 ) )

since then "tblMaterial.mat_nr = @Mat1" is valid independent of the length of @Mat2.

Note: I do not say that the shorter condition is easier to understand or maintain, YMMV:)

(15 Dec '14, 04:14) Volker Barth

I have problems with this when I use numeric fields. The logic I wish is:

If @Mat2 is null, should the condition be: tblMaterial.matnr = @Mat1. If there is any value in @Mat2 should the condition be: tblMaterial.matnr Between @Mat1 And @Mat2. If both @Mat1 and @Mat2 is null will it not be any condition on tblMaterial.matnr.

   WHERE
    tblMaterial.mat_type IN(1,3,7) 
    AND (    ( IsNull(@Mat2, 0) = 0  AND tblMaterial.mat_nr = @Mat1 )
          OR ( IsNull(@Mat2, 0) <> 0 AND tblMaterial.mat_nr Between @Mat1 And @Mat2 ) )
(29 Feb '16, 13:29) Rolle

Abowe works only if the value is on @Mat1 and/or @Mat2. If both are null it will not work. If both @Mat1 and @Mat2 are null there will not be any condition on tblMaterial.mat_nr. How do I do that?

(29 Feb '16, 15:00) Rolle

BETWEEN is a comparison operator, it doesn't assign values, so it's not clear what you want to happen when Len(@Mat2) is not 0.

Perhaps it would be best to explain how you want the WHERE clause to work.

permanent link

answered 12 Dec '14, 16:05

Justin%20Willey's gravatar image

Justin Willey
7.6k137179249
accept rate: 20%

If @Mat2 has no value, should the condition be: tblMaterial.matnr = @Mat1. If there is any value in @Mat2 should the condition be: tblMaterial.matnr Between @Mat1 And @Mat2

(12 Dec '14, 16:15) Rolle
Comment Text Removed
Comment Text Removed

I've deleted my code sample as it was incorrect - see Breck's / Volker's suggestion

(15 Dec '14, 05:36) Justin Willey

You can apply the logic you are attempting, but it is done without the CASE. Instead, you need to create logical groupings of OR/AND to combine the BETWEEN with the other matching condition from your case.

This is because CASE is designed to return a value, rather than to dynamically construct the SQL inside it.

SELECT * FROM table_name WHERE pricekey = 'JUF' AND ( -- Condition 1 (to_char(to_date(sysdate,'DD-MON-YY'), 'DY') = 'MON' AND pydate BETWEEN to_date(sysdate-12,'DD-MON-YY') AND to_date(sysdate-2,'DD-MON-YY')) -- Condition 2 OR (to_char(to_date(sysdate,'DD-MON-YY'), 'DY')='TUE' AND pydate BETWEEN to_date(sysdate-11,'DD-MON-YY') AND to_date(sysdate-1,'DD-MON-YY')) -- ELSE case, matching neither of the previous 2 OR (to_char(to_date(sysdate,'DD-MON-YY'), 'DY') NOT IN ('MON', 'TUE') AND pydate = 'sysdate') )

permanent link

answered 01 Mar '16, 00:52

xavipirlo10's gravatar image

xavipirlo10
261
accept rate: 0%

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: 12 Dec '14, 15:22

question was seen: 16,130 times

last updated: 20 Apr '20, 06:20