'if' statements - SQL Server Forums

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

SQL Server 2000 Forums

SQL Server Development (2000)

'if' statements

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-05-31 : 09:07:54

Curt writes "I have an existing access query that works fine, but it will not work as an SQL view. Why not? It is having problems with the 'if' statements, and the quotes also, I think. How does it work in SQL?

I am running SQL Server 2000 - SP2 on a Windows 2000 server.

SELECT fips.fips, fips.county, ca05title.order, ca05title.title, ca05title.line,
IIf([80]=1,"(D)",IIf([80]=2,"(L)",IIf([80]=3,"(E)",IIf([80]=9,"NA",IIf([1980y]=0,"0",Format([1980y],"#,###")))))) AS 1980,
IIf([90]=1,"(D)",IIf([90]=2,"(L)",IIf([90]=3,"(E)",IIf([90]=9,"NA",IIf([1990y]=0,"0",Format([1990y],"#,###")))))) AS 1990, IIf([95]=1,"(D)",IIf([95]=2,"(L)",IIf([95]=3,"(E)",IIf([95]=9,"NA",IIf([1995y]=0,"0",Format([1995y],"#,###")))))) AS 1995,
IIf([99]=1,"(D)",IIf([99]=2,"(L)",IIf([99]=3,"(E)",IIf([99]=9,"NA",IIf([1999y]=0,"0",Format([1999y],"#,###")))))) AS 1999
FROM (ca05title INNER JOIN CA05BEA ON ca05title.title = CA05BEA.title) INNER JOIN fips ON CA05BEA.fips = fips.fips
ORDER BY fips.fips, ca05title.order;

This is copied directly from the query; sorry if it is confusing."

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-05-31 : 09:21:21

A quick forum search on 'iif' bring back many many threads . . .

here is one

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=8578

<O>

dsdeming

479 Posts

Posted - 2002-05-31 : 10:19:21

You should also know that Access SQL will often generate SQL that is non-standard and will not run in SQL Server. 'IIF' is only one example. As I recall, the pivot table code produced by Access won't work either.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources