Syntax for If Then ElseIf Expression
-
Anyone have an example on how to write an IF Then ElseIF statement?
I can't get the syntax.
Thanks!
-
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
-
If you mean an RS-expression its
=iif(condition, true-expression, false-expression)
-
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 . . . . . .
-
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
-
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.
-
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.
-
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!
-
How about:
if isdbnull(fields!start_date.value) OR fields!start_date.value = 0 then
mytextbox.text = "X"
end if
-
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"
-
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
-
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!
-
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?
-
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
-
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>
. 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.
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply