Error - Incorrect syntax near the keyword 'Select'.

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

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 < @fr_date
)
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 < @fr_date
set Balance=select (Balance1+Balance2)

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh4y ago
Accepted 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 < @fr_date
)

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 < @fr_date

set Balance=select (Balance1+Balance2)
+1