SQL IF statement
-
We know there's conditional branching facilities in T-SQL and PL-SQL etc. Is there a similar IF clause available for use in flat SQL statements? I can't use stored procs for certain restriction reasons, and my scenario is this. I have one table, with columns [a, b, c, d, e, f] I will pick rows, where column c = column d. However, if column d is blank (null), then column e will be used for the comparison, if e is blank (null), then column f will be used for the comparison. This is easy as pie in T-SQL, PL-SQL etc. Is there a way to do this in flat SQL statements? Thanks.
-
We know there's conditional branching facilities in T-SQL and PL-SQL etc. Is there a similar IF clause available for use in flat SQL statements? I can't use stored procs for certain restriction reasons, and my scenario is this. I have one table, with columns [a, b, c, d, e, f] I will pick rows, where column c = column d. However, if column d is blank (null), then column e will be used for the comparison, if e is blank (null), then column f will be used for the comparison. This is easy as pie in T-SQL, PL-SQL etc. Is there a way to do this in flat SQL statements? Thanks.
Hi, you can use AND and OR operators and effectively build a WHERE clause that calculates this (pseudo-code):
c=d OR (d=null AND (c=e OR (e=null AND c=f)))
:)
-
Hi, you can use AND and OR operators and effectively build a WHERE clause that calculates this (pseudo-code):
c=d OR (d=null AND (c=e OR (e=null AND c=f)))
:)
-
You're welcome. :)
-
We know there's conditional branching facilities in T-SQL and PL-SQL etc. Is there a similar IF clause available for use in flat SQL statements? I can't use stored procs for certain restriction reasons, and my scenario is this. I have one table, with columns [a, b, c, d, e, f] I will pick rows, where column c = column d. However, if column d is blank (null), then column e will be used for the comparison, if e is blank (null), then column f will be used for the comparison. This is easy as pie in T-SQL, PL-SQL etc. Is there a way to do this in flat SQL statements? Thanks.
-
Yo can also use the CASE statement or (depending on exactly what you want) possibly the COALESCE statement in SQL Server.
Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP
-
Sorry if this sounds dumb, but I thought CASE decision making was only available on the procedure/function side, and not in flat SQL? If not, can you point me to some sample flat SQL code with CASE usage demonstrated? That'll be very helpful.
You can use it anywhere, I'm not sure what you mean by Flat SQL - T-SQL is T-SQL, which runs trhe same in stored procs as out of them - the only execeptions are triggers and functions which are more restrictive.
select NonEoM = case when [Receive side type] = 'floating' and [Receive Convention] = 'EOM' then 0 when [Pay side type] = 'floating' and [Pay Convention] = 'EOM' then 0 else 1 end from table1Bob Ashfield Consultants Ltd Proud to be a 2009 Code Project MVP