Conditional SQL query
Question
Application Type
Reactive
Platform Version
11.13.0 (Build 30897)

Hi

I need to design a SQL query with operators that depend on a variable value

CASE
    WHEN @condition = 16 THEN
        SELECT * FROM {Contractors} WHERE {Contractors}.[status] NOT LIKE @status
    WHEN @condition = 15 THEN
        SELECT * FROM {Contractors} WHERE {Contractors}.[status] = @status
    WHEN @condition = 14 THEN
        SELECT * FROM {Contractors} WHERE {Contractors}.[status] <> @status
    WHEN @condition = 13 THEN
        SELECT * FROM {Contractors} WHERE {Contractors}.[status] < @status
    WHEN @condition =  12 THEN
        SELECT * FROM {Contractors} WHERE {Contractors}.[status] <= @status
    WHEN @condition =  9 THEN
        SELECT * FROM {Contractors} WHERE {Contractors}.[status] IS NULL
    WHEN @condition =  7 THEN 
        SELECT * FROM {Contractors} WHERE {Contractors}.[status] > @status
    WHEN @condition =  6 THEN
        SELECT * FROM {Contractors} WHERE {Contractors}.[status] = 1
    WHEN @condition =  5 THEN 
        SELECT * FROM {Contractors} WHERE {Contractors}.[status] NOT LIKE @status
    WHEN @condition =  4 THEN
        SELECT * FROM {Contractors} WHERE {Contractors}.[status]  IS NOT NULL
    WHEN @condition =  3 THEN
        SELECT * FROM {Contractors} WHERE {Contractors}.[status] NOT LIKE  @status
    WHEN @condition =  2 THEN
        SELECT * FROM {Contractors} WHERE {Contractors}.[status] = 0
    WHEN @condition =  1 THEN
        SELECT * FROM {Contractors} WHERE {Contractors}.[status] >= @status
    ELSE
        SELECT * FROM {Contractors} WHERE {Contractors}.[status] LIKE @status
END;

This is what I came up with but it doens't work. I get incorrect syntax errors

Is something like this even possible to achieve with Outsystems?

Hi,

You can achieve it with a Data Action. Inside the data action you can use a SQL Statement and input parameter (with expand inline).

If you use an expanded inline parameter, it is important you understand the security implications in order to prevent the SQL injection (https://success.outsystems.com/Documentation/Best_Practices/Security/Injection_and_Cross_Site_Script_(XSS)).

Kind regards, David.

Hi,

This is more SQL related that OutSystems. Your SQL statement is not syntax valid.

For how to use the CASE you can check SQL Server (assuming you are using SQL SERVER) docs: CASE


You can try something like this:


SELECT *

FROM {Contractors}

WHERE

@condition = 16 AND {Contractors}.[status] NOT LIKE @status

OR @condition = 15 AND {Contractors}.[status] = @status

OR @condition = 14 AND {Contractors}.[status]<> @status

OR @condition = 13 AND {Contractors}.[status] < @status

OR @condition = 12 AND {Contractors}.[status] <= @status

OR @condition = 9 AND {Contractors}.[status] IS NULL

OR @condition = 7 AND {Contractors}.[status] > @status

OR @condition = 6 AND {Contractors}.[status] = 1

OR @condition = 5 AND {Contractors}.[status] NOT LIKE @status

OR @condition = 4 AND {Contractors}.[status] IS NOT NULL

OR @condition = 3 AND {Contractors}.[status] NOT LIKE  @status

OR @condition = 2 AND {Contractors}.[status] = 0

OR @condition = 1 AND {Contractors}.[status] >= @status


OR @condition = 0 AND {Contractors}.[status] LIKE @status 


Best,

Tiago

Hi Gleb Kartashov,

Instead of using the case query, you can follow the David Vaello suggestion.

step1 : use switch condition 

Note: Avoid using hardcoded value(ie 1,2,3,4) best practice

Step2 : assign the dynamic query to the variable

Step3: create the input parameter to the sql and set the inproperty to yes

Step4 : add the query

Hope this helps!

Ellakkiya.S

Hello Gleb,

I do like David Vaello's suggestion myself.

If you must use Advanced SQL, instead of CASE WHEN THEN, simply using 'IF' conditions works:

IF @condition = 1
        SELECT something
IF @condition = 2
        SELECT something
IF @condition = 3
        SELECT something

Regards, 

AJ

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