Migrating query from MS 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

SQL Server 2005 Forums

Transact-SQL (2005)

Migrating query from MS ACCESS to SQL Server

Author

Topic

jamonakes
Starting Member

18 Posts

Posted - 2007-08-13 : 18:07:59

I have the following query that has been working fine on MS ACCESS.


SELECT tblCategories.catId, tblCategories.catName, tblCategories.catTextId, tblCategories.catDescription, tblCategories.catActive, tblCategories.catParent, IIf(IsNull(artCount),0,artCount) AS ArticleCount, IIf(IsNull(tblCategories_1.catName),'None',tblCategories_1.catName) AS catParentName, tblCategories.catskiId, (SELECT skiName FROM tblSkins WHERE skiId = tblCategories.catskiId) AS skinName
FROM (tblCategories LEFT JOIN tblCategories AS tblCategories_1 ON tblCategories.catParent=tblCategories_1.catId) LEFT JOIN [SELECT arccatId, COUNT(*) + 0 As artCount FROM tblArticleCategories GROUP By arccatId]. AS tblAC ON tblCategories.catId=tblAC.arccatId
ORDER BY tblCategories.catName;

Now I need to migrate the same query to SQL Server and I keep getting the following error message, after I have adjusted the IIf to CASE..WHEN..END: Incorrect syntax near the keyword 'AS'.

I am sure there is something I am not doing right. Could someone please adjust the SQL statement for me so that it can run on SQL Server and achieve the same output?

Thank you.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-08-13 : 22:49:51

POST the syntax that's generating the error.

jamonakes
Starting Member

18 Posts

Posted - 2007-08-14 : 18:06:14

The following is the syntax:


SELECT tblCategories.catId, tblCategories.catName, tblCategories.catTextId, tblCategories.catDescription, tblCategories.catActive, tblCategories.catParent, CASE artCount WHEN IsNull(artCount,0) THEN artCount END AS ArticleCount, CASE tblCategories_1.catName WHEN IsNull(tblCategories_1.catName,'None') THEN tblCategories_1.catName END AS catParentName, tblCategories.catskiId, (SELECT skiName FROM tblSkins WHERE skiId = tblCategories.catskiId) AS skinName
FROM (tblCategories LEFT JOIN tblCategories AS tblCategories_1 ON tblCategories.catParent=tblCategories_1.catId) LEFT JOIN [SELECT arccatId, COUNT(*) + 0 As artCount FROM tblArticleCategories GROUP By arccatId]. AS tblAC ON tblCategories.catId=tblAC.arccatId
ORDER BY tblCategories.catName;

When I remove the dot immediately after arccatId], it positively parses the SQL statement as valid, but on executing it it gives the following error message:
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'SELECT arccatId, COUNT(*) + 0 As artCount FROM tblArticleCategories GROUP By arccatId'.

When I put the dot (.) back, I get the following error when i parse the query:
Server: Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'AS'.

Now the query in my first post, the one with the IIf function, works fine in ACCESS but not so in SQL Server. I would therefore request assistance in converting the query in my first post, with IIf, to be SQL Server capable.

Thank you

mattyblah
Starting Member

49 Posts

Posted - 2007-08-14 : 22:55:52

IIf(a, b, c) should convert to:


case when (a) then b else c end

Just read your initial posting. IsNull in sql server follows the syntax:


IsNull(val_to_evaluate, val_to_replace_if_null)

That's more than likely the problem.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources