Cannot implement if then logic to sql code

Question

Wanted to change the SAS code to SQL, but did not know which way should I follow, but I tried with case when, it gave me error.

SAS Code:

length list_of_Fields $400.;
list_of_Fields = "";

if missing(column_a)        eq 1 then do; if calc_of_count <= limit_of_Count then list_of_Fields = catx(" ;", calculation_of_columnName, "Company1"); calc_of_count + 1; end;
if missing(column_b)        eq 1 then do; if calc_of_count <= limit_of_Count then list_of_Fields = catx(" ;", calculation_of_columnName, "Company2"); calc_of_count + 1; end;


if list_of_Fields eg "" then list_of_Fields = &strNone

SQL CODE:

case 
    when column_a is null then case when calc_of_count <= limit_of_Count then ist_of_Fields = catx(" ;", calculation_of_columnName, "Company1") 
    when column_b is null then case when calc_of_count <= limit_of_Count then list_of_Fields = catx(" ;", calculation_of_columnName, "Company2")

Unfortunately was not able to finish it accordingly, could you please help me to find the way?

catx I guess is sas equivalent of concat, every case needs and END — P.Salmon
– P.Salmon, Commented Dec 4, 2019 at 8:36
jarlh, could you please tell me which product is not involved? — Kanan Mehdizade
– Kanan Mehdizade, Commented Dec 4, 2019 at 8:40
@KananMehdizade it's you who should tell us which product is involved. MySQL? PostgreSQL? SQL Server? They are different databases with different syntax. All databases use extensions to the SQL standard while none of them fully implements it beyond basic compliance - it's just too complicated — Panagiotis Kanavos
– Panagiotis Kanavos, Commented Dec 4, 2019 at 8:43
As in the SAS code you increase calc_of_count and use it in your next evaluation, this is an iterative process. This is not easily done in SQL. Maybe with some recursive query, but I would rather stick to a programming language. But you are only showing a small part of code. What is it for? Maybe the overall task is easily achievable in SQL while the sub part isn't. — Thorsten Kettner
– Thorsten Kettner, Commented Dec 4, 2019 at 9:34

Chella · Accepted Answer · 2019-12-04 08:57:41Z

1

You can do that in the following way:

CASE 
    WHEN (column_a is null AND calc_of_count <= limit_of_Count) THEN ist_of_Fields = CONCAT(" ;", calculation_of_columnName, "Company1") 
    WHEN (column_b is null AND calc_of_count <= limit_of_Count) THEN list_of_Fields = CONCAT(" ;", calculation_of_columnName, "Company2") 
    ELSE list_of_Fields = &strNone
END

You can also try IF CLAUSE in below format:

IF(condn, TRUE_STATEMENT, FALSE_STATEMENT);

answered Dec 4, 2019 at 8:57

Chella

1,5624 gold badges23 silver badges43 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Kanan Mehdizade Over a year ago

Thanks a lot for the answer, could you please tell me where should I code this part? calc_of_count + 1;

Thom A Over a year ago

THEN ist_of_Fields = CONCAT(" ;", calculation_of_columnName, "Company1")? A CASE expression cannot return a boolean result in T-SQL, it returns a Scalar Value. This will only error.

Chella Over a year ago

You can try - SELECT @calc_of_count:=@calc_of_count+1 calc_of_count, CASE WHEN (column_a is null AND calc_of_count <= limit_of_Count) THEN ist_of_Fields = CONCAT(" ;", calculation_of_columnName, "Company1") WHEN (column_b is null AND calc_of_count <= limit_of_Count) THEN list_of_Fields = CONCAT(" ;", calculation_of_columnName, "Company2") ELSE list_of_Fields = &strNone END FROM YOUR_TABLE_1, (SELECT @calc_of_count:= 0) AS CalcCount;

Collectives™ on Stack Overflow

Cannot implement if then logic to sql code

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related