GROUP BY expressions must refer to column names th

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

GROUP BY expressions must refer to column names th

Author

Topic

chandan_joshi80
Starting Member

30 Posts

Posted - 2008-06-07 : 09:02:28

I am creating a procedure and passing a parameter.On the basis of that parameter i want to group by in the procedure

group by @FinancialYear
I tried dynamic also but no result
Code :

Declare @FinancialYear_1 [varchar](50)
DECLARE @sql nvarchar(4000)
set @FinancialYear_1='2006-2007'
set @sql=
'select @FinancialYear_1,sum(a.Receipt) as Receipt,sum(a.Payment) as Payment from
(select FinancialYear,VouicherAmt as Receipt,0 as Payment from PCMAVoucher
where substring(VoucherMode,2,1)='+'"R"'
+'union all

select FinancialYear,0,VouicherAmt from PCMAVoucher
where substring(VoucherMode,2,1)=' +'"P"' +

') a
group by ' + @FinancialYear
but it is showing error

chandan Joshi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-07 : 10:25:20

Do you ever set @FinancialYear to a valid value?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-06-07 : 10:34:15

[code]DECLARE @FinancialYear NVARCHAR(50)
@SQL NVARCHAR(4000)

SELECT @FinancialYear = '2006-2007',
@SQL = '
SELECT ' + QUOTENAME(@FinancialYear, '''') + ' AS FinancialYear,
SUM(Receipt) AS Receipt,
SUM(Payment) AS Payment
FROM (
SELECT FinancialYear,
VouicherAmt AS Receipt,
0 AS Payment
FROM PCMAVoucher
WHERE SUBSTRING(VoucherMode, 2, 1) = ''R''

UNION ALL

SELECT FinancialYear,
0,
VouicherAmt
FROM PCMAVoucher
WHERE SUBSTRING(VoucherMode, 2, 1) = ''P''
) AS a
GROUP BY FinancialYear
'

EXEC (@SQL)[/code]

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources