How to convert access query to MS SQL

Question

I am trying to run access query in MS SQL but i don't know how to do it i already research but unable to find any solution.

Here is the Access Query(It's Working fine in Access):

Updated Code:

SELECT DISTINCT b.Category, b.SubCategory1, b.SubCategory2, b.SubCategory3, 
b.SubCategory4, 'Total '+ b.SubCategory3+' FY 2018' AS Title, 
Sum(b.TotalAmount) AS Budget,
0 AS [201801],
0 AS [201802],
0 AS [201803],
0 AS [201804],
0 AS [201805],
0 AS [201806],
0 AS [201807],
0 AS [201808],
0 AS [201809],
0 AS [201810],
0 AS [201811],
0 AS [201812],
0 AS TotalSpent,
0 AS TotalCommited,
0 AS UncommitedBudget

FROM tblBudget AS b
GROUP BY b.Category, b.SubCategory1, b.SubCategory2, b.SubCategory3, 
b.SubCategory4
UNION ALL SELECT DISTINCT b.Category, b.SubCategory1, b.SubCategory2, 
b.SubCategory3, b.SubCategory4, IIF((po.PONumber), 'Forecast - ' + po.Title, 
po.PONumber + ' - ' + po.Title + ' ('+ po.ProjectStatus +')' ), 0 AS Budget, 

(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=1 AND tb.PONumber=po.PONumber) AS [201801], 
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=2 AND tb.PONumber=po.PONumber) AS [201802], 
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=3 AND tb.PONumber=po.PONumber) AS [201803],
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=4 AND tb.PONumber=po.PONumber) AS [201804],
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=5 AND tb.PONumber=po.PONumber) AS [201805], 
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=6 AND tb.PONumber=po.PONumber) AS [201806],
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=7 AND tb.PONumber=po.PONumber) AS [201807],
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=8 AND tb.PONumber=po.PONumber) AS [201808],
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=9 AND tb.PONumber=po.PONumber) AS [201809],
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=10 AND tb.PONumber=po.PONumber) AS [201810],
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=11 AND tb.PONumber=po.PONumber) AS [201811],
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=12 AND tb.PONumber=po.PONumber) AS [201812], 
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
year(tb.AcctPeriod)=2018 AND tb.PONumber=po.PONumber) AS TotalSpent,
SUM(TotalOrdered) - IIF(((SELECT Sum(tb.InvoiceAmount) FROM tblInvoices AS 
tb WHERE year(tb.AcctPeriod)=2018 AND tb.PONumber=po.PONumber)),0,(SELECT 
Sum(tb.InvoiceAmount) FROM tblInvoices AS tb WHERE year(tb.AcctPeriod)=2018 
AND tb.PONumber=po.PONumber))-
IIF(po.ProjectStatus='Closed',
SUM(TotalOrdered) - IIF(((SELECT Sum(tb.InvoiceAmount) FROM tblInvoices AS 
tb WHERE year(tb.AcctPeriod)=2018 AND tb.PONumber=po.PONumber)),0,(SELECT 
Sum(tb.InvoiceAmount) FROM tblInvoices AS tb WHERE year(tb.AcctPeriod)=2018 
AND tb.PONumber=po.PONumber))
,0)  AS TotalCommited,
0+
IIF(po.ProjectStatus='Closed',
SUM(TotalOrdered) - IIF(((SELECT Sum(tb.InvoiceAmount) FROM tblInvoices AS 
tb WHERE year(tb.AcctPeriod)=2018 AND tb.PONumber=po.PONumber)),0,(SELECT 
Sum(tb.InvoiceAmount) FROM tblInvoices AS tb WHERE year(tb.AcctPeriod)=2018 
AND tb.PONumber=po.PONumber))
,0) AS UncommitedBudget

FROM (tblBudget AS b LEFT JOIN tblPO AS po ON b.id = po.BudgetCategory) 
GROUP BY b.Category, b.SubCategory1, b.SubCategory2, b.SubCategory3, 
b.SubCategory4, po.Title, po.PONumber, po.ProjectStatus
UNION ALL SELECT DISTINCT b.Category, b.SubCategory1, b.SubCategory2, 
b.SubCategory3, b.SubCategory4, 'Total '+b.SubCategory3+' FY 2018' AS Title,
0 AS Budget,
0 AS [201801],
0 AS [201802],
0 AS [201803],
0 AS [201804],
0 AS [201805],
0 AS [201806],
0 AS [201807],
0 AS [201808],
0 AS [201809],
0 AS [201810],
0 AS [201811],
0 AS [201812],
0 AS TotalSp,
0 AS TotalCommited,
Sum(b.TotalAmount) - IIF((SELECT Sum(p.TotalOrdered) FROM tblPO AS p WHERE 
p.BudgetCategory=b.id),0,(SELECT Sum(p.TotalOrdered) FROM tblPO AS p WHERE 
p.BudgetCategory=b.id)) AS UncommitedBudget
FROM tblBudget AS b
GROUP BY b.Category, b.SubCategory1, b.SubCategory2, b.SubCategory3, 
b.SubCategory4,b.id;

Errors In MS SQL Server:

Updated Errors:

Msg 4145, Level 15, State 1, Line 21
An expression of non-boolean type specified in a context where a condition 
is expected, near '('.
Msg 156, Level 15, State 1, Line 22
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 23
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 24
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 25
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 26
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 27
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 28
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 29
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 30
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 31
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 32
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 33
Incorrect syntax near the keyword 'AS'.
Msg 156, Level 15, State 1, Line 34
Incorrect syntax near the keyword 'AS'.
Msg 102, Level 15, State 1, Line 35
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 35
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 37
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 37
Incorrect syntax near ')'.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Line 41
Incorrect syntax near ')'.
Msg 4145, Level 15, State 1, Line 62
An expression of non-boolean type specified in a context where a condition 
is expected, near '('.

I run the same query in SQL Server but i am unable to figure out the problem, kindly help me in that.

Any help would be great for me.

Error messages are pretty clear. Besides, the query is awful. Plenty of "bad practices": DISTINCT after GROUP BY, multiple subqueries instead of PIVOT, absurd IIFs with subqueries which reproduce trivial ISNULL functionality. — IVNSTN
– IVNSTN, Commented Feb 28, 2018 at 8:08

IVNSTN · Accepted Answer · 2018-02-28 10:12:38Z

1

1.

Incorrect syntax near '201801'.

Column name can't start with number: https://learn.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers put it in square brackets like [2018]

2.

The isnull function requires 2 argument(s).

ISNULL inside IIF is broken. You did not finish migration to either of them. Should be this (extra efforts for server, unwelcome):

IIF(
  (SELECT Sum(p.TotalOrdered) FROM tblPO AS p WHERE p.BudgetCategory=b.id),
  0,
  (SELECT Sum(p.TotalOrdered) FROM tblPO AS p WHERE p.BudgetCategory=b.id)
) AS UncommitedBudget

or this:

ISNULL((SELECT Sum(p.TotalOrdered) FROM tblPO AS p WHERE p.BudgetCategory=b.id), 0)

3. DISTINCT after GROUP BY is an absurd. It does not have impact on output but still needs some effort from server for sorting. Or you have logically wrong GROUP BY clause.

4. You cannot have semicolon ; in the middle of a statement. See before each UNION ALL.

5. Take a look how pivoting is usually performed:

SELECT p.[201801], p.[201802], p.[201803]
FROM 
( 
    SELECT CONVERT(VARCHAR(6), i.period, 112) period_name, i.amount
    FROM tblInvoices i
    WHERE i.period >= '20180101' and i.period < '20190101'
) i
pivot (
  sum(i.amount) 
  for i.period_name in (
    [201801], [201802], [201803]
  )
)p

http://sqlfiddle.com/#!18/ea5c5/6

avoid this:

...
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=1 AND tb.PONumber=po.PONumber) AS 201801, 
(select sum(tb.InvoiceAmount) from tblInvoices AS tb WHERE 
Month(tb.AcctPeriod)=2 AND tb.PONumber=po.PONumber) AS 201802,
...

your approach makes server working a lot more.

edited Feb 28, 2018 at 10:12

answered Feb 28, 2018 at 8:20

IVNSTN

9,3896 gold badges24 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Usama Over a year ago

Hi with your help i removed many errors, now there are no red lines error in sql but some errors are coming i didn't get the point of pivot and i updated my code and errors please have a look at it and let me know where i am doing mistake.

IVNSTN Over a year ago

Line 21 An expression of non-boolean type specified in a context where a condition is expected

Collectives™ on Stack Overflow

How to convert access query to MS SQL

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related