UserImage.jpg
tuyenhx
60Views
9Comments
Solved
How to use CASE WHEN in Sql
Question
SQL

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

0
0
27 Aug
Solution

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.

See solution in context
4
0
01 Sep

masterclass

1
0
01 Sep

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

0
0
03 Sep

Replying to tuyenhx's comment on 03 Sep 2023 13:29:57

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:

  1. Create a SQL parameter of type Text with the "Expand Inline" property set to True;
  2. The input dynamic SQL Text should use the OutSystems SQL format, so enclose entities in {} and attributes in [].

Example:

If(Condition1, "AND {MyTable}.[MyAttribute1] = '" + Variable1 + "'", "") +
If(Condition2, "AND {MyTable}.[MyAttribute2] = '" + Variable2 + "'", "")


0
0
04 Sep

Hey tuyenhx ,

Is it mandatory to use CASE and THEN? you can also use switch .

Thanks & Regards,

Sudip Pal

5
1
27 Aug

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

0
3
27 Aug
2023-05-25 14-12-43
Arun Rajput
Champion

Hi @tuyenhx 

You are missing else part in sql please find below format to use case when

SELECT

    order_id,

    order_amount,

    CASE

        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

0
3
27 Aug

Hey tuyenhx , 


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



Thanks & Regards,

Sudip Pal

4
0
27 Aug

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)

    )


0
3
01 Sep
Solution

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.

4
0
01 Sep

masterclass

1
0
01 Sep

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

0
0
03 Sep

Replying to tuyenhx's comment on 03 Sep 2023 13:29:57

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:

  1. Create a SQL parameter of type Text with the "Expand Inline" property set to True;
  2. The input dynamic SQL Text should use the OutSystems SQL format, so enclose entities in {} and attributes in [].

Example:

If(Condition1, "AND {MyTable}.[MyAttribute1] = '" + Variable1 + "'", "") +
If(Condition2, "AND {MyTable}.[MyAttribute2] = '" + Variable2 + "'", "")


0
0
04 Sep
Community GuidelinesBe kind and respectful, give credit to the original source of content, and search for duplicates before posting.