Microsoft Access to SQL Server

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Microsoft Access to SQL Server

Author

Topic

jpockets
Starting Member

45 Posts

Posted - 2007-04-30 : 15:31:32

I'm switching over to SQL Server from Access I have an iif statement in access and i'm not to sure on how to do the same thing in SQL Server, any guidence is greatly appreciated. I was thinking about using a case statement but not sure on the syntax.

Access statment:

Sum(IIf([Hit_Ratio]![STATUS_CD]='B',1,0))+Sum(IIf([Hit_Ratio]![STATUS_CD]='Q',1,0))+Sum(IIf([Hit_Ratio]![STATUS_CD]='L',1,0))+Sum(IIf([Hit_Ratio]![STATUS_CD]='K',1,0)) AS Quoted

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-30 : 15:40:13

This should help you out:

http://weblogs.sqlteam.com/jeffs/archive/2007/03/30/Quick-Access-JET-SQL-to-T-SQL-Cheatsheet.aspx

You've got to convert the IIF() functions to CASE expressions.

- Jeff
http://weblogs.sqlteam.com/JeffS

jpockets
Starting Member

45 Posts

Posted - 2007-04-30 : 16:11:33

Thanks for the reply...

I changed the statement:

Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "b" THEN Return 1 ELSE Return 0) END

but i'm getting an error that says: Incorrect syntax near the keyword 'Return'

And

A Return statement with a return value cannot be used in this context

I'm assuming my syntax is wrong.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-04-30 : 16:36:21

Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "b" THEN ~~Return~~ 1 ELSE ~~Return~~ 0 END) ~~END~~

Why did you put those "return" 's in there?

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-04-30 : 16:41:21

I think we need to see the complete query.
There are most certainly other things to change.

Peter Larsson
Helsingborg, Sweden

jpockets
Starting Member

45 Posts

Posted - 2007-05-01 : 09:22:49

Thanks for the help

This is the full Query

SELECT dbo.THIT_RATIO_DETL.F_DIVISION_NO,
	Count(dbo.THIT_RATIO_DETL.SUBMISSION_NO)as Sub,
	Sum(dbo.THIT_RATIO_DETL.WORKING_FL) as Working,
    Sum(dbo.THIT_RATIO_DETL.DECLINED_AUTO_FL) as  Declined,
    Sum(dbo.THIT_RATIO_DETL.LOST_FL) AS Lost,
    Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or "Q" THEN 1 ELSE 0) END as QUOTE
    FROM  dbo.THIT_RATIO_DETL 
GROUP BY dbo.THIT_RATIO_DETL.F_DIVISION_NO

The error message i'm getting is: incorrect syntax near keyword "THEN"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-05-01 : 09:31:44

[code]
Sum(CASE WHEN dbo.THIT_RATIO_DETL.STATUS_CD = "B" or dbo.THIT_RATIO_DETL.STATUS_CD = "Q" THEN 1 ELSE 0 END) as QUOTE
[/code]

jpockets
Starting Member

45 Posts

Posted - 2007-05-01 : 09:50:30

Thank-you!!!!!! It's going to take a while to get used to the syntax but i'll get it, thank-you again!

jpockets
Starting Member

45 Posts

Posted - 2007-05-01 : 12:08:19

I hit another brick wall.... I'm trying to truncate the following to two decimals places,I did some research and saw the cast function, but i can't seem to get it to work...

Cast(Sum(dbo.THIT_RATIO_DETL.DECLINED_AUTO_FL) / Count(dbo.THIT_RATIO_DETL.SUBMISSION_NO)* 100,(8,2)) as Dec_Per

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-05-01 : 16:53:09

Cast(100 * Sum(dbo.THIT_RATIO_DETL.DECLINED_AUTO_FL) / Count(dbo.THIT_RATIO_DETL.SUBMISSION_NO) AS NUMERIC(8,2)) as Dec_Per

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources