Syntax Error but dunno where - 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)

Syntax Error but dunno where

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-23 : 23:23:02

hi

I have this syntax:

declare @s varchar(8000)
select @s = isnull(@s + ',' + char(13), '') + ' case when JRNAL_Type = ''' + JRNAL_Type +
''' and Period = ' + cast(Period as varchar(20)) + ' and LedgerType = ''Forecast''' + ' then Amount else 0 end as [' +
JRNAL_Type + '-' + cast(JRNAL_Type as varchar(20)) + ']'
from tblProcess_A_Ledger group by JRNAL_Type, Period

exec('select *,' + @s + ' into tblProcess_CDGI_Ledger from tblProcess_A_Ledger order by JRNAL_Type,Period ')

It kept pointing to me - Incorrect syntax near the keyword 'into'

And i can't find anywhere is wrong. Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-23 : 23:34:05

do a print @s before the exec and check the syntax. Or post it back here.

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-24 : 00:21:47

OK, I believe the @s has hit a limit of varchar(8000) cause i have a 600,000 thousands of rows to process.

By the way, i have a table:

Drop Table #Mytable

create table #Mytable (TCode varchar(10), TransDa int, Amount int, LedType Varchar(15))
insert #Mytable
select '01', 2006001, 1000, 'Acutal' union all
select 'AB001', 2006001, 1100, 'Forecast' union all
select 'AB002', 2006001, 1020, 'Forecast' union all
select '02', 2006001, 1150, 'Acutal' union all
select 'AB001', 2006002, 1200, 'Forecast' union all
select 'AB001', 2006002, 1210, 'Forecast' union all
select 'AB002', 2006002, 1110, 'Forecast' union all
select 'con', 2006002, 2000, 'Acutal'

--calculation

declare @s varchar(8000)
select @s = isnull(@s + ',' + char(13), '') + ' case when TCode = ''' + TCode +
''' and TransDa = ' + cast(TransDa as varchar(10)) + ' and LedType = ''Forecast''' + ' and TCode Like ''A%''' + ' then Amount else 0 end as [' +
TCode + '-' + cast(TransDa as varchar(10)) + ']'
from #Mytable group by TCode, TransDa

print @s
exec('select *,' + @s + ' from #Mytable order by TCode, TransDa')

And when I execute it, it give me this

TCode TransDa Amount LedType 01-2006001 02-2006001 AB001-2006001 AB001-2006002 AB002-2006001 AB002-2006002 con-2006002
---------- ----------- ----------- --------------- ----------- ----------- ------------- ------------- ------------- ------------- -----------
01 2006001 1000 Acutal 0 0 0 0 0 0 0
02 2006001 1150 Acutal 0 0 0 0 0 0 0
AB001 2006001 1100 Forecast 0 0 1100 0 0 0 0
AB001 2006002 1200 Forecast 0 0 0 1200 0 0 0
AB001 2006002 1210 Forecast 0 0 0 1210 0 0 0
AB002 2006001 1020 Forecast 0 0 0 0 1020 0 0
AB002 2006002 1110 Forecast 0 0 0 0 0 1110 0
con 2006002 2000 Acutal 0 0 0 0 0 0 0

How do I generate columns that starts with AB. As you notice, i began to have columns like 01-2006001, 02-2006001 and so on which i do not want. Thanks a million thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-24 : 00:37:54

"@s has hit a limit of varchar(8000) "
also refer to here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=52274

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-24 : 00:53:13

hi

I don't understand how come this statement in this post:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69267

does not hit the limit.It is the same table which have 600,000 rows.

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-24 : 01:42:55

the column name for that post is shorter where as this is longer by 6 + 2 chars.

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

quote:
select @s = isnull(@s + ',' + char(13), '') + ' case when TCode = ''' + TCode +
''' and TransDa = ' + cast(TransDa as varchar(10)) + ' and LedType = ''Forecast''' + ' and TCode Like ''A%''' + ' then Amount else 0 end as [' +
TCode + '-' + cast(TransDa as varchar(10)) + ']'
from #Mytable group by TCode, TransDa

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-24 : 02:05:24

hi

create table #Mytable (TCode varchar(10), TransDa int, Amount int, LedType Varchar(15))
insert #Mytable
select '01', 2006001, 1000, 'Acutal' union all
select 'AB001', 2006001, 1100, 'Forecast' union all
select 'AB002', 2006001, 1020, 'Forecast' union all
select '02', 2006001, 1150, 'Acutal' union all
select 'AB001', 2006002, 1200, 'Forecast' union all
select 'AB001', 2006002, 1210, 'Forecast' union all
select 'AB002', 2006002, 1110, 'Forecast' union all
select 'con', 2006002, 2000, 'Acutal'

--calculation

Declare @sQry Varchar(8000)
Declare @date int
Select @Date = 0,@sQry = 'Select * , '

While @Date < (Select Distinct Max(TransDa) From #Mytable where LedType = 'Forecast')
Begin
Select @Date = Min(TransDa) From #Mytable Where TransDa > @Date and LedType = 'Forecast'
Select @sQry = @sQry + ' Case When TransDa = ' + Convert(varchar,@Date) + ' and LedType = ''Forecast''' + ' and TCode =''' + TCode + ''' Then Amount Else 0 End As [' + TCode + Convert(varchar,@Date) + '],'
End

-- Delete the Last , due to the loop..

Select @sQry = left(@sQry,len(@sQry)-1)
-- Create the From Cluase.

--Select @sQry = @sQry + 'Into tblfinal'
-- Create the From Cluase.

Select @sQry = @sQry + 'Into tblProcess_CDGI_Ledger' + ' From #Mytable'
-- Execute the Query
Print len(@sQry)

Exec(@sQry)

-----------------Result-------------------------

TCode TransDa Amount LedType 01-2006001 02-2006001 AB001-2006001 AB001-2006002 AB002-2006001 AB002-2006002 con-2006002
---------- ----------- ----------- --------------- ----------- ----------- ------------- ------------- ------------- ------------- -----------
01 2006001 1000 Acutal 0 0 0 0 0 0 0
02 2006001 1150 Acutal 0 0 0 0 0 0 0
AB001 2006001 1100 Forecast 0 0 1100 0 0 0 0
AB001 2006002 1200 Forecast 0 0 0 1200 0 0 0
AB001 2006002 1210 Forecast 0 0 0 1210 0 0 0
AB002 2006001 1020 Forecast 0 0 0 0 1020 0 0
AB002 2006002 1110 Forecast 0 0 0 0 0 1110 0
con 2006002 2000 Acutal 0 0 0 0 0 0 0

How do I generate columns that starts with AB. As you notice, i began to have columns like 01-2006001, 02-2006001 and so on which i do not want the loop to generate. Thanks a million thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-24 : 02:09:26

You mean you want only TCode that starts with AB ?

where TCode like 'AB%'

Kristen
Test

22859 Posts

Posted - 2006-07-24 : 02:39:27

If you are hitting the 8,000 character limit see:

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210&SearchTerms=Execute+dynamic+SQL+that+is+longer+than+varchar

Kristen

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-24 : 02:46:36

Yes, i want columns to be generated that starts with 'AB%' only.Thanks

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-24 : 03:50:44

hi

I have tried executing this code:

declare @s varchar(8000)
select @s = isnull(@s + ',' + char(13), '') + ' case when TCode = ''' + TCode +
''' and TransDa = ' + cast(TransDa as varchar(10)) + ' and LedType = ''Forecast''' + ' and TCode Like ''AB'' + ''%''' + ' then Amount else 0 end as [' +
TCode + '-' + cast(TransDa as varchar(15)) + ']'
from #Mytable group by TCode, TransDa

print @s
exec('select *,' + @s + ' from #Mytable order by TCode, TransDa')

But it still give me the result as:

Code TransDa Amount LedType 01-2006001 02-2006001 AB001-2006001 AB001-2006002 AB002-2006001 AB003-2006003 con-2006002
---------- ----------- ----------- --------------- ----------- ----------- ------------- ------------- ------------- ------------- -----------
01 2006001 1000 Acutal 0 0 0 0 0 0 0
02 2006001 1150 Acutal 0 0 0 0 0 0 0
AB001 2006001 1100 Forecast 0 0 1100 0 0 0 0
AB001 2006002 1200 Forecast 0 0 0 1200 0 0 0
AB001 2006002 1210 Forecast 0 0 0 1210 0 0 0
AB002 2006001 1020 Forecast 0 0 0 0 1020 0 0
AB003 2006003 1110 Forecast 0 0 0 0 0 1110 0
con 2006002 2000 Acutal 0 0 0 0 0 0 0

I have aleady tried my best and i still can't get the result as:

TCode TransDa Amount LedType AB001-2006001 AB001-2006002 AB002-2006001 AB003-2006003
---------- ----------- ----------- --------------- ----------- ----------- ------------- -------------
01 2006001 1000 Acutal 0 0 0 0
02 2006001 1150 Acutal 0 0 0 0
AB001 2006001 1100 Forecast 1100 0 0 0
AB001 2006002 1200 Forecast 0 1200 0 0
AB001 2006002 1210 Forecast 0 1210 0 0
AB002 2006001 1020 Forecast 0 0 1020 0
AB003 2006003 1110 Forecast 0 0 0 1110
con 2006002 2000 Acutal 0 0 0 0

Any help thanks a million thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-24 : 04:03:54

the "where TCode like 'AB%'" should be added to the 1st query.


declare @s varchar(8000)
select @s = isnull(@s + ',' + char(13), '') + ' case when TCode = ''' + TCode +
''' and TransDa = ' + cast(TransDa as varchar(10)) + ' and LedType = ''Forecast''' + ' and TCode Like ''AB'' + ''%''' + ' then Amount else 0 end as [' +
TCode + '-' + cast(TransDa as varchar(15)) + ']'
from  #Mytable 
where TCode like 'AB%'
group by TCode, TransDa

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-07-24 : 04:06:16

Somthing like this


create table #Mytable (TCode varchar(10), TransDa int, Amount int, LedType Varchar(15))
insert #Mytable
select '01', 2006001, 1000, 'Acutal' union all
select 'AB001', 2006001, 1100, 'Forecast' union all
select 'AB002', 2006001, 1020, 'Forecast' union all
select '02', 2006001, 1150, 'Acutal' union all
select 'AB001', 2006002, 1200, 'Forecast' union all
select 'AB001', 2006002, 1210, 'Forecast' union all
select 'AB002', 2006002, 1110, 'Forecast' union all
select 'con', 2006002, 2000, 'Acutal'

--calculation

declare @s varchar(8000)
select @s = isnull(@s + ',' + char(13), '') + ' case when TCode = ''' + TCode +
''' and TransDa = ' + cast(TransDa as varchar(10)) + ' and LedType = ''Forecast''   then Amount else 0 end as [' +
TCode + '-' + cast(TransDa as varchar(15)) + ']'
from #Mytable Where TCode Like 'AB%'
group by TCode, TransDa
print @s
exec('select *,' + @s + ' from #Mytable order by TCode, TransDa')

Drop Table #MyTable

Chirag

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2006-07-24 : 04:52:51

hi

I am very thankful to you guys, am really thankful. One last request i hope so. Is there anyway to convert it from:

declare @s varchar(8000)
select @s = isnull(@s + ',' + char(13), '') + ' case when TCode = ''' + TCode +
''' and TransDa = ' + cast(TransDa as varchar(10)) + ' and LedType = ''Forecast''' + ' and TCode Like ''AB'' + ''%''' + ' then Amount else 0 end as [' +
TCode + '-' + cast(TransDa as varchar(15)) + ']'
from #Mytable
where TCode like 'AB%'
group by TCode, TransDa

to a while statement like:

Declare @sQry Varchar(8000)
Declare @date int
Declare @Tran Varchar(8000)
Select @Date = 0,@sQry = 'Select * , '
While @Date < (Select Distinct Max(TransDa) From #Mytable)
Begin
Select @Date = Min(TransDa) From #Mytable Where TransDa > @Date and LedType = 'Forecast'
Select @sQry = @sQry + ' Case When TransDa = ' + Convert(varchar,@Date) + ' and LedType = ''Forecast''' + ' and ' + @Tran = +' TCode like ''AB%''' + ' Then Amount Else 0 End As [' + @Tran + Convert(varchar,@Date) + '],' from #Mytable where TCode like 'AB%'
--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 = ''Forecast''' + ' Then Amount Else 0 End As [' + 'FC_LM' +Convert(varchar,@Date) + '],'

End
-- Delete the Last , due to the loop..

Select @sQry = left(@sQry,len(@sQry)-1)
-- Create the From Cluase.

--Select @sQry = @sQry + 'Into tblfinal'
-- Create the From Cluase.

Select @sQry = @sQry + ' From #Mytable'
-- Execute the Query
Print len(@sQry)

Because the rest of the processing of other table are using while statement, so i am hoping to make it more consistent look.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources