Solved

Access SQL to T-SQL

Posted on 2015-01-28
11
291 Views
Last Modified: 2015-01-28
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
Comment
Question by:Bojerne
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
11 Comments
 
LVL 11

Accepted Solution

by:
David Kroll earned 250 total points
ID: 40575516
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
 
LVL 24

Assisted Solution

by:Phillip Burton
Phillip Burton earned 250 total points
ID: 40575517
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
 
LVL 1

Author Comment

by:Bojerne
ID: 40575529
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40575531
From David's solution, delete the comma at the end of line 5.
0
 
LVL 1

Author Comment

by:Bojerne
ID: 40575535
of course - thank you so much
0
 
LVL 11

Expert Comment

by:David Kroll
ID: 40575538
Good catch Phillip!
0
 
LVL 1

Author Comment

by:Bojerne
ID: 40575550
Btw - isn't IF/IIF a T-SQL function after version 2012 ?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40575555
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
 
LVL 1

Author Comment

by:Bojerne
ID: 40575577
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
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40575581
Because IS NULL is not a function - you can use it in the WHERE, but not in an IIF.
0
 
LVL 1

Author Comment

by:Bojerne
ID: 40575589
aha - thank you again :)
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question