Using BETWEEN in SQL CASE where clause

[Former Member]

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

[Breck_Carter · Accepted solution]

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

[VolkerBarth · Accepted solution]

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

[Former Member · Accepted solution]

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

[Former Member · Accepted solution]

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?