August 31, 2003 at 5:35 pm
Hi ,
I am a first time user here.
I would like to find out how to convert IIf functions used in Access into SQLServer queries (without using MDX stuff) ?
August 31, 2003 at 6:15 pm
See SQL Server Books Online for the Case statement.
August 31, 2003 at 7:07 pm
Hi there
Be aware of this alternatives...
select case when mycol is null then 'value is null' else 'not null' end
..or this..
select case mycol when 'A' then 'value is A' else 'not A' end
this is important as checking for null in the second example will never work.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
September 1, 2003 at 12:17 am
maybe isnull(yourcolumn,replacementvalue) can help out or
coalesce(yourcolumn,replacementvalue)
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
September 2, 2003 at 9:56 am
Here's an example of Access IIF translated to T-SQL CASE:
IIF(MyColumn = 'test val', 'return this', 'otherwise')
CASE
WHEN MyColumn = 'test val' THEN 'return this'
WHEN MyColumn = 'another val' THEN 'return foo'
ELSE 'otherwise'
END
Note that I added another WHEN option to the CASE statement to show that multiple options are possible, unlike with the IIF statement. Keep in mind that like IIF, CASE only returns a value -- it is NOT a flow control statement like the "Case" statement in VB.
Edited by - edbutler3 on 09/02/2003 09:56:52 AM
September 2, 2003 at 2:47 pm
For control of flow:
IF 1 = 2 --criteria here
Begin
SElect '1 is equal to 2'
END
Else
Begin
select '1 is not equal to 2'
END
Signature is NULL
March 2, 2005 at 11:03 am
I had a more complicated IIF statement in an access database that I needed to convert to a stored procedure. The IIF statement was
IIf([SrvDisc]=0 Or IsNull([SrvDisc]),IIf([Disc_Service]=0,0,[Price]*[Disc_Service]),[Price]*[SrvDisc]) As Disc
Not only is it an IIF statement within another IIF statement but the [Price] field is actually a calculated column in the Access Query. The [Price] calculation is [SrvQuantity]*[SrvPrice]
I used a case statement like this to solve it
Case
When IsNull([SrvDisc],0) = 0 then Case When [Disc_Service] = 0 then 0 else ([SrvQuantity] * [SrvPrice])*[SrvDisc] End
Else ([SrvQuantity] * [SrvPrice])*[SrvDisc]
End
AS Disc
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply