Using BETWEEN in SQL CASE where clause

[former_SQLA_member1694869]

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_SQLA_member1694869 · 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_SQLA_member1694869 · 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?

[justin_willey]

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.

[former_SQLA_member1694869]

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

[justin_willey]

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

[Former Member]

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