Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 306
  • Last Modified:

Access SQL to T-SQL

I'm faily new to T-SQL (on a SQL Server 2014), and are trying to convert this  simple Access SQL to T-SQL - as far as I can read IIF/IF is also valid from SQL Server 2012, but I can't see the forrest for trees :)

Please advice
SELECT tbl_CHAIN.ChainID, tbl_CHAIN_SORT.ArticleID, IIf([ArticleName] Is Null,'N/A',[ArticleName]) AS ArticleN
FROM tbl_ARTICLE RIGHT JOIN (tbl_CHAIN LEFT JOIN tbl_CHAIN_SORT ON tbl_CHAIN.ChainID = tbl_CHAIN_SORT.ChainID) ON tbl_ARTICLE.ArticleID = tbl_CHAIN_SORT.ArticleID;
0
Mik Mak
Asked:
Mik Mak
  • 5
  • 4
  • 2
2 Solutions
 
David KrollCommented:
SELECT tbl_CHAIN.ChainID, tbl_CHAIN_SORT.ArticleID,
case
  when [ArticleName] is null then 'N/A'
  else [ArticleName]
end as ArticleN,
FROM tbl_ARTICLE RIGHT JOIN (tbl_CHAIN LEFT JOIN tbl_CHAIN_SORT ON tbl_CHAIN.ChainID = tbl_CHAIN_SORT.ChainID) ON tbl_ARTICLE.ArticleID = tbl_CHAIN_SORT.ArticleID;
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Change

IIf([ArticleName] Is Null,'N/A',[ArticleName])

to

ISNULL([ArticleName],'N/A')

See https://msdn.microsoft.com/en-us/library/ms184325.aspx for details.
0
 
Mik MakConsultantAuthor Commented:
That was quick :) - when trying your solution David I get:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'FROM'.
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
From David's solution, delete the comma at the end of line 5.
0
 
Mik MakConsultantAuthor Commented:
of course - thank you so much
0
 
David KrollCommented:
Good catch Phillip!
0
 
Mik MakConsultantAuthor Commented:
Btw - isn't IF/IIF a T-SQL function after version 2012 ?
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
No problem David.

IIF has been a function since 2012 - see the drop-down box in https://msdn.microsoft.com/en-us/library/hh213574.aspx

That's how I check if a specific function is used in version X.
0
 
Mik MakConsultantAuthor Commented:
Super - great tip. But howcome the Access syntax "IIf([ArticleName] Is Null,'N/A',[ArticleName]) AS ArticleN" throws an error - the T-SQL IIF syntax looks similar ?
Thank you
0
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Because IS NULL is not a function - you can use it in the WHERE, but not in an IIF.
0
 
Mik MakConsultantAuthor Commented:
aha - thank you again :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now