如何将访问查询转换为MS SQL

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.

我试图在MS SQL中运行访问查询但我不知道如何做我已经研究但无法找到任何解决方案。

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

这是Access Query(它在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:

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.

我在SQL Server中运行相同的查询,但我无法弄清楚问题,请帮助我。

Any help would be great for me.

任何帮助对我来说都很棒。

1 个解决方案

#1

1.

Incorrect syntax near '201801'.

'201801'附近的语法不正确。

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

列名不能以数字开头:https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-identifiers将其放在方括号内,如[2018]

2.

The isnull function requires 2 argument(s).

isnull函数需要2个参数。

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

IIF中的ISNULL被打破了。您没有完成向其中任何一个的迁移。应该这样(服务器的额外努力,不受欢迎):

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.

3. GROUP BY之后的DISTINCT是荒谬的。它对输出没有影响,但仍需要服务器进行排序。或者你有一个逻辑上错误的GROUP BY子句。

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

你不能有分号;在声明中间。在每个UNION ALL之前查看。

5. Take a look how pivoting is usually performed:

5.看看通常如何进行旋转:

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.

您的方法使服务器工作得更多。