Syntax error in query expression using sub query

-1
SELECT
(
SELECT Sum(tbl_allTransactions.transAmount) AS SumOftransAmount
FROM tbl_allTransactions
WHERE (((tbl_allTransactions.[transType])='Expense')) OR (((tbl_allTransactions.[transType])='Budget') AND ((tbl_allTransactions.[transMonth])=[@transMonth]))
GROUP BY tbl_allTransactions.transMonth
,

SELECT Sum(tbl_allTransactions.transAmount) AS SumOftransAmount
FROM tbl_allTransactions
WHERE (((tbl_allTransactions.[transType])='Expense')) OR (((tbl_allTransactions.[transType])='Budget') AND ((tbl_allTransactions.[transMonth])=[@transMonth]))
GROUP BY tbl_allTransactions.transMonth
)

FROM tbl_allTransactions WHERE (((tbl_allTransactions.[userID])=[@userID]))

I am getting following error:-

System.Data.OleDb.OleDbException (0x80040E14) Syntax error in query
expression '(SELECT Sum(tbl_allTransactions.transAmount) AS
SumOftransAmount FROM tbl_allTransactions WHERE
(((tbl_allTransactions.[transType])='Expense')) OR
(((tbl_allTransactions.[transType])='Budget') AND
((tbl_allTransactions.[transMonth])=[@transMonth])) GROUP '. at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResulthr) at
System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS
dbParams, Object& executeResult) at
System.Data.OleDb.OleDbCommand.ExecuteCommandText(Obje
sql
sql-server-2008
asked on Stack Overflow May 3, 2015 by Aishwar C Nigam • edited May 6, 2015 by Mureinik

1 Answer

0

The original code was missing a closing parenthesis on the first subquery. The code was also simplified by consolidating the transType conditional from 2 statements to 1. Extra brackets were removed to enhance readability. Also column aliases were added to output of each subquery. Since both queries render the same data, the second column was called "SumOftransAmount2"

SELECT
(SELECT Sum(tbl_allTransactions.transAmount)
FROM tbl_allTransactions
WHERE tbl_allTransactions.transType in ('Expense', 'Budget') AND tbl_allTransactions.transMonth = @transMonth
GROUP BY tbl_allTransactions.transMonth) as SumOftransAmount
,
(SELECT Sum(tbl_allTransactions.transAmount)
FROM tbl_allTransactions
WHERE tbl_allTransactions.transType in ('Expense', 'Budget') AND tbl_allTransactions.transMonth = @transMonth
GROUP BY tbl_allTransactions.transMonth) as SumOftransAmount2
FROM tbl_allTransactions
WHERE tbl_allTransactions.userID=@userID
answered on Stack Overflow May 3, 2015 by WorkSmarter • edited May 3, 2015 by WorkSmarter

User contributions licensed under CC BY-SA 3.0