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

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