Invalid Colums name - SQL Server Forums

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

SQL Server Development (2000)

Invalid Colums name

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-21 : 02:12:31

hi

I have this code which say - Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Actual'.

While @Date < (Select Distinct Max(Period) From dbo.tblAll_Ledger where LedgerType = 'Actual')
Begin
Select @Date = Min(Period) From tblAll_ledger Where Period > @Date and LedgerType = 'Actual'
Select @sQry = @sQry + ' Case When Period = ' + Convert(varchar,@Date) + 'and LedgerType = Actual ' + ' Then Amount Else 0 End As [' + 'A_' +Convert(varchar,@Date) + '],'
--Select @Date = Min(Period) From Terrence_Fact_AAA_A_SALFLDG_Extract_Entity_Code Where Period > @Date
Select @sQry = @sQry + ' Case When Period = ' + Convert(varchar,@Date) + 'and LedgerType = Actual ' + ' Then Amount Else 0 End As [' + 'A_LM' +Convert(varchar,@Date) + '],'
End

The 'Actual' is a value but do not know how to correct it. Thanks

Q
Yak Posting Veteran

76 Posts

Posted - 2006-07-21 : 02:47:13

The second select part looks a bit strange to me...

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-21 : 04:49:40

sorry I mean how to pass the value 'Actual' to

Declare @sQry Varchar(8000)
Select @Date = 0,
@sQry = 'Select * , '
Set @LedgerType = 'Actual'

Select @sQry = @sQry + ' Case When TransDate = ' + Convert(varchar,@Date) + ' And LedgerType = ' + @LedgerType + ' Then Amount Else 0 End As [' + 'A_' +Convert(varchar,@Date) + '],'

It always give an invalid column. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-21 : 05:30:56

Actual is a string. So you will need to enclose it in single quote.

Select @sQry = @sQry + ' Case When TransDate = ' + Convert(varchar,@Date) + ' And LedgerType = ''' + @LedgerType + ''' Then Amount Else 0 End As [' + 'A_' +Convert(varchar,@Date) + '],'

Also perform a PRINT @sQry to verify before exec() it.

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-21 : 05:41:09

Thanks

gugarc
Starting Member

17 Posts

Posted - 2006-07-21 : 09:09:28

It works fine, but to improve performance, you could replace the adhoc query (usually it´s execution plan can´t be cached) by a query with parameters and execute it with the sp_executesql stored procedure.

see the sp_executesql stored proc in the books online - you may aso have an output var.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources