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.
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