Syntax for If Then ElseIf Expression

Question

Post reply

Syntax for If Then ElseIf Expression

sol

SSCrazy

Points: 2294
More actions
July 27, 2005 at 2:29 pm

#110791

Anyone have an example on how to write an IF Then ElseIF statement?
I can't get the syntax.
Thanks!

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Ray M SSC-Insane Points: 21093 More actions · Answer 1

are you looking for tsql syntax for an if statement?

IF Boolean_expression

{ sql_statement | statement_block }

[ ELSE

{ sql_statement | statement_block } ]

IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') < $15

BEGIN

PRINT 'The following titles are excellent mod_cook books:'

PRINT ' '

SELECT SUBSTRING(title, 1, 35) AS Title

FROM titles

WHERE type = 'mod_cook'

END

ELSE

IF (SELECT AVG(price) FROM titles WHERE type = 'mod_cook') > $15

BEGIN

PRINT 'The following titles are expensive mod_cook books:'

PRINT ' '

SELECT SUBSTRING(title, 1, 35) AS Title

FROM titles

WHERE type = 'mod_cook'

END

Antoon Vansina Valued Member Points: 64 More actions · Answer 2

If you mean an RS-expression its

=iif(condition, true-expression, false-expression)

pg53 Ten Centuries Points: 1303 More actions · Answer 3

or do you mean VB?

If a=0 Then

s1="a is zero"

ElseIf b=2 Then

s1 = "b is two"

Else

s1 = "neither"

End If

or c#? or . . . . . .

bellis SSCarpal Tunnel Points: 4667 More actions · Answer 4

You also have Case statements...

CASE WHEN criteria_1 THEN result_1

WHEN criteria_2 THEN result_2

WHEN criteria_3 THEN result_3

etc

ELSE result_n

END

marsha s Valued Member Points: 55 More actions · Answer 5

Also in RS if using an IIf statement as a formula (like excel):

example: =IIF(SUM(Fields!Col.Value + Fields!Col2.Value) > 0, ((SUM(Fields!Col1.Value) - SUM(Fields!Col3.Value) - SUM(Fields!Col4.Value))/ Fields!Col3.Value)

If you need to export to excel this value will return as text (even if you have the properties set to numeric). To hold the numeric value you must start the expression with =0.00 + expression.

Tony John-224503 SSC Enthusiast Points: 112 More actions · Answer 6

Make sure you dont use THEN at then end of the IF statement because that's only in PL/SQL and *not* in T-SQL. Also, make sure you enclose the logic under the IF or ELSE condition between BEGIN and END statements to avoid logical leaks.

IF (condition)

BEGIN

execute logic
execute logic

END

ELSE IF (condition)

BEGIN

execute logic
execute logic

END

ELSE

BEGIN

execute logic
execute logic

END

Tony John.

sol SSCrazy Points: 2294 More actions · Answer 7

Can't you use a nested IIF like

=IIF(

Fields!Start_Date.Value = cstr("01/01/1900"), "X",

IIF(

isdbnull(Fields!Start_Date.Value),"X",""))

Basically, I have to check for a smalldatetimefield, and if it's "01/01/1900" or <NUL>, then the textbox has to have a value of "X" to let the user know they need to fill it in!

Thanks!

pg53 Ten Centuries Points: 1303 More actions · Answer 8

How about:

if isdbnull(fields!start_date.value) OR fields!start_date.value = 0 then

mytextbox.text = "X"

end if

sol SSCrazy Points: 2294 More actions · Answer 9

Just tried this for the expression:

=

if isdbnull( Fields!Start_Date.Value ) OR

Fields!Start_Date.Value = cstr("01/01/1900")

then textbox35.text = "X"

end if

....and it basically gave me an error of "Expression Expected"

pg53 Ten Centuries Points: 1303 More actions · Answer 10

PS - I've followed your example and assumed you want to do this in the application, in VBdotnet.

If you want to do it in the SQL (perhaps you're using bound controls) then check out CASE ..WHEN as bellis's post;

If it's an Access back end and you want it in the SQL then you will have t go for nested IIF's

sol SSCrazy Points: 2294 More actions · Answer 11

This is for a textbox on a report. Will that not take in the Expression Editor for SQL Server Reporting?

Do I need to go to: Report/Report Properties/Code and do a function instead?

Thanks!

pg53 Ten Centuries Points: 1303 More actions · Answer 12

Right - I'm looking at Whidbey, and it so far doesn't seem to have an IsNull ! - it's got Isempty, Isarray, etc but not IsNull.

So I'm not sure about that bit - certainly in the report you would use nested iif's; at the min I can only suggest you handle the null in the SQL or by setting a default value for the field in the table design.

Thus in the sql you could use a CASE to return zero when the field contains a null; then your report would then only have to deal with zeroes and you would only need one IIF eg

IIF(Fields!Start_Date.Value =0,"X",Fields!Start_Date.Value )

Anyone else know about nulls in RS?

pg53 Ten Centuries Points: 1303 More actions · Answer 13

OK just experimented further and found that Isnothing does work with nulls . .therefore something like this should work:

IIF(isnothing(fields!start_date.value),"X",IIF(fields!start_date.value = 0,"X",fields!start_date.value))

HTH

pg

stevefromOZ SSC-Forever Points: 43646 More actions · Answer 14

You could also use a switch statement. In vb.net you can do this in a single line statement, like Switch(A=B, <do something>, A=C, <do something else&gt . You can have as many cases as you like, but remember the syntax is <boolean test>, <action if true> so you'll always have an even number of parameters.

Using the exmple posted previously, your statement would be

Switch(IsNothing(Fields!start_Date.Value), "X", Fields!start_Date.Value = 0, "X", 1=1, Fields!start_Date.Value)

Steve.