i try to use CASE WHEN in sql like this (only an example):
and i got this errr
any one know how to use CASE WHEN, i tried to use logic operator like AND and OR but it's not work with more than 3 case
Hi tuyenhx,
First of all, ignore all the replies above. They're all terribly wrong, and the authors of these posts should be very ashamed they posted these "answers". For example, the "ELSE" part is not mandatory in a CASE, there is no "switch" command, suggesting IF/ELSE with different query's in between is just a very bad solution for multiple reasons, and using ORs like that is convoluted, and again bad programming.
That said, let's get back to the basics: the CASE statement ultimately returns a value. You attempted not to return a value, but to compare or assign something, which doesn't work, as you've noted. I can think of two solutions. In your specific case, having these four conditions, and comparing the project Id to the same value, this should do it, no CASE needed:
WHERE {Project}.[Id] = @Condition
As you'll notice, the "@Condition" value is equal to the number you're comparing "{Project}.[Id]" with, so that works. If there's not always such a 1:1 correspondence, use the following CASE:
WHERE {Project}.[Id] = CASE @Condition WHEN 1 THEN 1 WHEN 2 THEN 2 WHEN 3 THEN 3 WHEN 4 THEN 4 END
Notice I've used the alternative CASE syntax by specifying the variable directly after the CASE. This is fully equivalent, but shorter code, to this:
WHERE {Project}.[Id] = CASE WHEN @Condition = 1 THEN 1 WHEN @Condition = 2 THEN 2 WHEN @Condition = 3 THEN 3 WHEN @Condition = 4 THEN 4 END
A final word: you really should never compare Ids with literal values like that. Ids should not be trusted to have a specific value, as they are typically autonumbers.
masterclass
Hi Kilian Hekhuis,
Your solution work perfectly with the problem I raised. It is only my sample problem but I'm now more knowledgeable about CASE WHEN of Outsystem from your answer. So, my real problem is about the way which I can 'Concatenate the conditions in the where statement' depending on the given conditions, like this:
sql = " "
sql += " SELECT * FROM Table1 WHERE condition_filter "
if ( condition1 ) then: sql += " AND column1 = input_variable1 "
if ( condition2 ) then: sql += " AND column2 = input_variable2 "
else if ( condition3 ) then: sql += " AND column3 = input_variable3 "
.....
FunctionToExcuteSQL (sql) ;
I need to apply that code in Outsystem, using sql, is there anyway
In general, you shouldn't use dynamic conditions like this, so if you can avoid them you should. Also, entirely dynamic SQL (so including the SELECT) is not something that you would want, ever, in OutSystems, as you need to specify the output structure in the SQL node, and with fully dynamic SQL that would break quickly.
That said, focussing solely on the conditions, the typical way to handle dynamic conditions, is something like this:
WHERE (@InputVariable1 = "" OR {MyTable}.[MyAttribute1] = @InputVariable1) AND (@InputVariable2 = "" OR {MyTable}.[MyAttribute1] = @InputVariable2) AND (@InputVariable3 = "" OR {MyTable}.[MyAttribute1] = @InputVariable3)
and so on. This is in general better than constructing these conditions on the fly, as in that case you'd construct a new query every time, which is bad for performance (the database works faster if it can reuse the query plan, instead of creating a new one every time).
However, if you really must use dynamic WHERE clauses, here's how to do it:
Example:
If(Condition1, "AND {MyTable}.[MyAttribute1] = '" + Variable1 + "'", "") + If(Condition2, "AND {MyTable}.[MyAttribute2] = '" + Variable2 + "'", "")
Hey tuyenhx ,
Is it mandatory to use CASE and THEN? you can also use switch .
Thanks & Regards,
Sudip Pal
Hi there,
Here's the case syntax:
(https://www.w3schools.com/sql/sql_case.asp)
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
WHEN conditionN THEN resultN
ELSE result
END;
in your case, you are missing the Else part.
Kind Regards,
João
Hi @tuyenhx
You are missing else part in sql please find below format to use case when
SELECT
order_id,
order_amount,
WHEN order_amount < 100 THEN 'Low'
WHEN order_amount >= 100 AND order_amount < 1000 THEN 'Medium'
ELSE 'High'
END AS order_category
FROM
Orders;
Hope it helps
Thanks
Arun
Use the following approach instead of CASE and THEN
IF (@Condition=1)
SELECT {Project}.* FROM {Project} WHERE {Project}.[Id]=1
ELSE IF (@Condition=2)
SELECT {Project}.* FROM {Project} WHERE {Project}.[Id]=2
ELSE IF (@Condition=3)
SELECT {Project}.* FROM {Project} WHERE {Project}.[Id]=3
ELSE IF (@Condition=4)
SELECT {Project}.* FROM {Project} WHERE {Project}.[Id]=4
Hi,
A SQL case can only return a value, So you have to compare a value to it.
An alternative way to make this with OR statements.
SELECT {Project}.*
FROM {Project}
WHERE
(
(@Condition = 1 AND {Project}.[Id] = 1)
OR (@Condition = 2 AND {Project}.[Id] = 2)
OR (@Condition = 3 AND {Project}.[Id] = 3)
OR (@Condition = 4 AND {Project}.[Id] = 4)
)