Error in the execution of a query in SQL server

[tgyc001]

when I launch the following request , i get this error message :

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

Could someone please help me with the right query?

SELECT DISTINCT TB.[U_Budget_Key] AS [Budget Key],
(
SELECT Sum(T01.[U_Budget_XOF])
FROM [dbo].[@BUDGET] T01 WHERE T01.[U_Budget_Key]=TB.[U_Budget_Key] GROUP BY T01.[U_Budget_Key]
) AS [Q8] ,
(
SELECT SUM(T1.[OpenSum])
FROM OPRQ T0 INNER JOIN PRQ1 T1 ON T0.[DocEntry] = T1.[DocEntry] INNER JOIN OOCR T2 ON T1.OcrCode4 = T2.OcrCode
WHERE T1.[U_Budget]=TB.[U_Budget_Key] AND T1.[LineTotal] <> 0 AND T1.[U_Budget] <> '' AND T0.[CANCELED] <> 'Y' AND T1.[LineStatus]= 'O'
Group by T1.[U_Budget]
) AS [Open PR] ,
(
SELECT SUM(T1.[OpenSum])
FROM OPOR T0 INNER JOIN POR1 T1 ON T0.[DocEntry] = T1.[DocEntry]
WHERE T1.[U_Budget]=TB.[U_Budget_Key] AND T0.[DocStatus] = 'O' AND T1.[LineTotal] <> 0 AND T1.[U_Budget] <> ' '
GROUP BY T1.[U_Budget]
) AS [Open PO] ,
(
SELECT
( ISNULL(( SELECT SUM ( TA.[Debit]) FROM JDT1 TA
WHERE TA.[Account] = T0.[AcctCode] AND TA.ocrcode4 = T2.[OcrCode]
GROUP BY TA.[Account], TA.ocrcode4 ),0) - ISNULL((SELECT SUM ( TB.[Credit])
FROM JDT1 TB WHERE TB.[Account] = T0.[AcctCode] AND TB.ocrcode4 = T2.[OcrCode] GROUP BY TB.[Account], TB.ocrcode4),0))
FROM OBGT T0 INNER JOIN BGT1 T1 ON T0.[AbsId] = T1.[BudgId]
INNER JOIN BGT2 T2 ON T0.[AbsId] = T2.[BudgId] WHERE T2.[OcrCode]= TB.[U_Budget_Key] AND T2.[Instance] = 2
GROUP BY T2.[OcrCode],T0.[AcctCode]
) AS [Actual]
FROM [@BUDGET] TB

[DellSC]

If you look at the last set of Select statements, you're grouping the credits and debits on both Account and Ocrcode4 (link to U_Budget_Key) which means that this Select will return multiple results.

Also, doing a separate Select statement for each field in an outer select is inefficient. I've taken a stab at re-writing your query using a cool feature of SQL Server - the "With" statement - which is something I've used frequently. Basically, queries in a With statement are run and the results stored in memory to be used either inside another (later) query in the With or in the main query. Also, something I learned from a query tuning expert - when possible, instead of putting filters in a Where clause, put them in the join to the table that's being filtered. This will speed up your query. This may not be exactly what you're looking for, but I hope it will get you started:

With BudgetQry as
(
  SELECT U_Budget_Key, Sum(U_Budget_XOF) as Q8
  FROM @BUDGET 
  GROUP BY U_Budget_Key
),
OpenPRQry as
(
  SELECT T1.U_Budget as U_Budget_Key, SUM(T1.OpenSum) as OpenPRSum
  FROM OPRQ T0 
  INNER JOIN PRQ1 T1 
    ON T0.DocEntry = T1.DocEntry
      AND T1.U_Budget <> '' 
	  AND T1.LineTotal <> 0 
	  AND T1.LineStatus= 'O'
  INNER JOIN OOCR T2 
    ON T1.OcrCode4 = T2.OcrCode
  WHERE T0.CANCELED <> 'Y' 
  GROUP BY T1.U_Budget
),
OpenPOQry as 
(
  SELECT T1.U_Budget as U_Budget_Key, SUM(T1.OpenSum) as OpenPOSum
  FROM OPOR T0 
  INNER JOIN POR1 T1 
    ON T0.DocEntry = T1.DocEntry
      AND T1.LineTotal <> 0 
	  AND T1.U_Budget <> ' '
  WHERE T0.DocStatus = 'O' AND T1.LineTotal <> 0 AND T1.U_Budget <> ' '
  GROUP BY T1.U_Budget
),
DebitCredit as 
(
  SELECT
    U_Budget_Key,
    Account,
    ocrcode4,
    sum(Debit) as Debit,
    sum(Credit) as Credit
  From JDT1
  GROUP BY U_Budget_Key, Account, ocrcode4
),
ActualQry as 
(
  SELECT
    dc.U_Budget_Key,
    sum(dc.Debit) as Debit,
	sum(dc.Credit) as Credit
  FROM OBGT T0 
    INNER JOIN BGT1 T1 
      ON T0.AbsId = T1.BudgId
    INNER JOIN BGT2 T2 
      ON T0.AbsId = T2.BudgId
	    AND T2.Instance = 2
    INNER JOIN DebitCredit dc
      ON T2.OcrCode = dc.ocrcode4
	    AND T0.AcctCode = dc.Account
  GROUP BY dc.U_Budget_Key
)

SELECT
  budget.U_Budget_Key,
  budget.Q8,
  pr.OpenPRSum as [Open PR],
  po.OpenPOSum as [Open PO],
  IsNull(act.Debit, 0) - IsNull(act.Debit, 0) as Actual
FROM BudgetQry
  Inner Join OpenPRQry pr
    on budget.U_Budget_Key = pr.U_Budget_Key
  Inner Join OpenPOQry po
    on budget.U_Budget_Key = po.U_Budget_Key
  Inner Join ActualQry act
    on budget.U_Budget_Key = act.U_Budget_Key

-Dell

[agentry_src]

Look at the results you get from the second and third queries without the grouping. You have multiple subqueries with "!=" or as you scripted it "<>". Each of those has the potential of having more than one result.

Regards, Mike