Error - Incorrect syntax near the keyword 'Select'.

Question

Hi

Incorrect syntax near the keyword 'Select'. & Must declare the scalar variable "@Opening".

Declare @fr_Date as Date
Set @fr_Date = '2011/11/11'
declare @to_Date as Date
Set @to_date = '2021/10/10'
Declare @Opening as double

Select @Opening =  (SUM(Credit)-SUM(Debit)) from JDT1 where  RefDate < '2010/11/11'
	Union all
	Select (SUM(JDT1.Credit)-SUM(JDT1.Debit)) from 
	OJDT inner join JDT1 on OJDT.transid = JDT1.transid
	 AND JDT1.RefDate >= '2010/11/11' AND JDT1.RefDate < @fr_date

Thanks

Sachin Singh · Answer

What do you want to return? As you are doing union all you can not return a scalar value (double) as it is returning multiple rows simply select them With CTE as ( select (SUM(Credit)-SUM(Debit)) as Balance from JDT1 where RefDate '2010/11/11' Union all Select (SUM(JDT1.Credit)-SUM(JDT1.Debit)) as Balance from OJDT inner join JDT1 on OJDT.transid = JDT1.transid AND JDT1.RefDate >= '2010/11/11' AND JDT1.RefDate ) select * from CTE if you really want to return a single value then you don't need union all, instead, you need some aggregate operator Declare Balance1 double ; Declare Balance2 double ; Declare Balance double ; set Balance1=select (SUM(Credit)-SUM(Debit)) from JDT1 where RefDate '2010/11/11' ; set Balance2= Select (SUM(JDT1.Credit)-SUM(JDT1.Debit)) as Balance from OJDT inner join JDT1 on OJDT.transid = JDT1.transid AND JDT1.RefDate >= '2010/11/11' AND JDT1.RefDate set Balance=select (Balance1+Balance2)

Error - Incorrect syntax near the keyword 'Select'.

Answers (1)