Solved

Access SQL to T-SQL

Posted on 2015-01-28
11
289 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
  • 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
Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

 
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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

713 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