Invalid Colums name for cross tab

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 for cross tab

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-08-26 : 11:52:46

hi

I have this crosstab query:

Declare @sQry Varchar(MAX)
Declare @Date int
Declare @LedType varchar(15)
Select @Date = 0,@sQry = 'Select * , '
-- Loop for creating the Dynamic SQL
While @Date < (Select Distinct Max(PDate) From dbo.tblDemo_A where LedType = 'F' and JType like 'FC%' group by JType, PDate )
Begin
Select @Date = Min(PDate) From tblDemo_A Where PDate > @Date and LedType = 'F' and JType like 'FC%' group by JType, PDate
Select @sQry = Isnull(@sQry + ',' + char(13), '') + ' Case When JType Like ''' + JType +''' and PDate = ' + Convert(varchar,@Date) + ' and LedType = ''F''' + ' Then Amount Else 0 End As [' + JType + '-' + Convert(varchar,@Date) + '],'
End

Select @sQry = left(@sQry,len(@sQry)-1)

Select @sQry = @sQry + 'Into tblDemo' + ' From tblDemo_A'

Print len(@sQry)
Exec(@sQry)

and is giving me:

Msg 207, Level 16, State 1, Line 8
Invalid column name 'JTYPE'.
Msg 207, Level 16, State 1, Line 8
Invalid column name 'JTYPE'.

Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-26 : 12:03:09

Post your table structure
Make sure JTYPE is a column

Madhivanan

Failing to plan is Planning to fail

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-08-26 : 12:14:38

hi

This is the table structure:

CREATE TABLE [dbo].[ tblDemo_A](
[AC] [char](15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[PDate] [int] NOT NULL,
[AMOUNT] [decimal](18, 3) NOT NULL,
[D_C] [char](1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[JTYPE] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[JSRCE] [char](5) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[TREFERENCE] [char](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[LedType] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY]

GO
SET ANSI_PADDING OFF

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-26 : 22:04:32

[code]Select @sQry = Isnull(@sQry + ',' + char(13), '') +
' Case When JType Like ''' +
JType + ''' and PDate = ' +
Convert(varchar,@Date) + ' and LedType = ''F''' +
' Then Amount Else 0 End As [' +
JType + '-' + Convert(varchar,@Date) + '],'[/code]

You specify a column name of a table in the line above without FROM <table>

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-08-27 : 02:22:43

Declare @sQry Varchar(MAX)
Declare @Date int
Declare @LedType varchar(15)
Select @Date = 0,@sQry = 'Select * , '
-- Loop for creating the Dynamic SQL
While @Date < (Select Distinct Max(PDate) From dbo.tblDemo_A where LedType = 'F' and JType like 'FC%' group by JType, PDate )
Begin
Select @Date = Min(PDate) From tblDemo_A Where PDate > @Date and LedType = 'F' and JType like 'FC%' group by JType, PDate
Select @sQry = Isnull(@sQry + ',' + char(13), '') + ' Case When JType Like ''' + JType +''' and PDate = ' + Convert(varchar,@Date) + ' and LedType = ''F''' + ' Then Amount Else 0 End As [' + JType + '-' + Convert(varchar,@Date) + '],'from tblDemo_A where JTYPE like 'FC%' group by JTYPE, PDate
End

Select @sQry = left(@sQry,len(@sQry)-1)

Select @sQry = @sQry + 'Into tblDemo' + ' From tblDemo_A'

Print len(@sQry)
Exec(@sQry)

Go

Ok, I hv place in the From Table Statement. But it still gives me some error.

Actually, i have this piece of code which is already working: but it take a long tome to execute.

DECLARE @SQLDynamic3 varchar(MAX)

SELECT @SQLDynamic1 = isnull(@SQLDynamic1 + ',' + char(13), '') + ' case when JType Like ''' + JType +
''' and PDate = ' + cast(PDate as varchar(10)) + ' and LedType = ''F''' + ' then Amount else 0 end as [' +
JType + '-' + cast(PDate as varchar(15)) + ']'from tblDemo_A where JType like 'FC%' group by JType, PDate

Print Len(@SQLDynamic1)
exec('select *,' + @SQLDynamic1 + 'into tblDemo from tblDemo_A order by JType, PDate')

Am wondering which statement would give a better performanc? Should I stick to the one which i have changed or using a whlie loop. Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-28 : 11:18:11

Set the execution plan and see

Madhivanan

Failing to plan is Planning to fail

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-08-28 : 23:20:35

hi

thanks guys

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources