Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • 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
Bojerne
Asked:
Bojerne
  • 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
 
BojerneAuthor 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
From David's solution, delete the comma at the end of line 5.
0
 
BojerneAuthor Commented:
of course - thank you so much
0
 
David KrollCommented:
Good catch Phillip!
0
 
BojerneAuthor 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
 
BojerneAuthor 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
 
BojerneAuthor Commented:
aha - thank you again :)
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

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