I want a query something similar to the below condition . This does not work...how should I do?
asked 12 Dec '14, 15:22
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 ) )
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.
answered 12 Dec '14, 16:05
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') )
answered 01 Mar '16, 00:52