PeopleSoft Adoption Made Smooth & Simple!
On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool. Claim Your Free WalkMe Account Now
Course Of The Month
5 days, 17 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
Access SQL to T-SQL
Posted on 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;
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;
[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
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;
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;
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.
IIf([ArticleName] Is Null,'N/A',[ArticleName])
to
ISNULL([ArticleName],'N/A'
See https://msdn.microsoft.com/en-us/library/ms184325.aspx for details.
Active 1 day ago
That was quick :) - when trying your solution David I get:
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'FROM'.
Msg 156, Level 15, State 1, Line 6
Incorrect syntax near the keyword 'FROM'.
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.
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.
Active 1 day ago
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
Thank you
Featured Post
With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
In this article we will learn how to fix “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables.
Make a table:
Update a specific column given a specific row using the UPDATE statement:
Remove a set of values using the DELETE s…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month5 days, 17 hours left to enroll
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.