Question Re: Using BETWEEN in SQL CASE where clause in Technology Q&A

Using BETWEEN in SQL CASE where clause

Former Member — Fri, 12 Dec 2014 15:22:19 GMT

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

Re: Using BETWEEN in SQL CASE where clause

justin_willey — Fri, 12 Dec 2014 16:05:59 GMT

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.

Re: Using BETWEEN in SQL CASE where clause

Former Member — Fri, 12 Dec 2014 16:15:34 GMT

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

Re: Using BETWEEN in SQL CASE where clause

Breck_Carter — Sat, 13 Dec 2014 11:30:42 GMT

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

Re: Using BETWEEN in SQL CASE where clause

VolkerBarth — Mon, 15 Dec 2014 04:14:36 GMT

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

Re: Using BETWEEN in SQL CASE where clause

justin_willey — Mon, 15 Dec 2014 05:36:26 GMT

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

Re: Using BETWEEN in SQL CASE where clause

Former Member — Mon, 29 Feb 2016 13:29:31 GMT

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

Re: Using BETWEEN in SQL CASE where clause

Former Member — Mon, 29 Feb 2016 15:00:44 GMT

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?

Re: Using BETWEEN in SQL CASE where clause

Former Member — Tue, 01 Mar 2016 00:52:08 GMT

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