Crosstab stuck on syntax - 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

Transact-SQL (2000)

Crosstab stuck on syntax

Author

Topic

vmon
Yak Posting Veteran

63 Posts

Posted - 2003-05-23 : 17:53:10

I am not sure what to do. I have read every post on cross tabs and tried all kinds of stuff to get this to work. I am using the much referenced crosstab proc with the addition of WHERE. my statement is below. I get a syntax error at the + sign.

EXECUTE crosstab 'SELECT Caseworker FROM tblWeeklyAging GROUP BY Caseworker', ' SUM(Amount)', ' CONVERT(varchar(8),Date_Bucket,1)', ' tblWeeklyAging', ' WHERE DATE_CALC = ''' + cast(@txtCurrent As varchar) + ''''

If I take the ', ' WHERE DATE_CALC = ''' + cast(@txtCurrent As varchar) + '''' off the end it works wonderfully. Can anyone advise me further on my headache?

thanks,
vmon

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-05-23 : 18:27:42

Is it possible for @txtCurrent to be NULL. If so it would create the following where clause

WHERE DATE_CALC = 'NULL'

this could be a problem.

The string looks well formed to me.

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-23 : 20:32:04

I took a look at the CROSSTAB documentation

[url]http://sqlteam.com/item.asp?ItemID=2955[/url]

I don't see a 5th parameter after the tablename.

Here's an example of the usage

quote:
EXECUTE crosstab 'SELECT LastName FROM Employees INNER JOIN Orders
ON (Employees.EmployeeID=Orders.EmployeeID)
GROUP BY LastName', 'count(lastname)', 'Year(OrderDate)', 'Orders'

Sam

Edited by - SamC on 05/23/2003 20:39:19

vmon
Yak Posting Veteran

63 Posts

Posted - 2003-05-24 : 00:26:36

quote:

I took a look at the CROSSTAB documentation

[url]http://sqlteam.com/item.asp?ItemID=2955[/url]

I don't see a 5th parameter after the tablename.

Here's an example of the usage

quote:
EXECUTE crosstab 'SELECT LastName FROM Employees INNER JOIN Orders
ON (Employees.EmployeeID=Orders.EmployeeID)
GROUP BY LastName', 'count(lastname)', 'Year(OrderDate)', 'Orders'

Sam

Edited by - SamC on 05/23/2003 20:39:19

Sam - Check out http://www.sqlteam.com/Forums/topic.asp?TOPIC_ID=6216 It shows the @WHERE.

vmon

vmon
Yak Posting Veteran

63 Posts

Posted - 2003-05-24 : 00:32:03

quote:

Is it possible for @txtCurrent to be NULL. If so it would create the following where clause

WHERE DATE_CALC = 'NULL'

this could be a problem.

The string looks well formed to me.

I have tried it with: DECLARE @txtCurrent DateTime
set @txtCurrent = '05/23/2003'

as well as letting the proc prompt me for the value when I run it and replacing the cast with '05/23/2003'.

Thanks,
vmon

SamC
White Water Yakist

3467 Posts

Posted - 2003-05-24 : 02:48:49

quote:

I have tried it with: DECLARE @txtCurrent DateTime
set @txtCurrent = '05/23/2003'

as well as letting the proc prompt me for the value when I run it and replacing the cast with '05/23/2003'.

Thanks,
vmon

@txtCurrent is VARCHAR in Rob's procedure, why would you use datetime? Since you also tried character, I don't think that's a problem.

Why don't you print the string before it's executed so you can see how it's formed? Just add

Print 'INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' + IsNull(@where,'1=1') + ' AND ' + @pivot + ' Is Not Null'

Sam

vmon
Yak Posting Veteran

63 Posts

Posted - 2003-05-27 : 08:32:45

quote:

quote:

I have tried it with: DECLARE @txtCurrent DateTime
set @txtCurrent = '05/23/2003'

as well as letting the proc prompt me for the value when I run it and replacing the cast with '05/23/2003'.

Thanks,
vmon

@txtCurrent is VARCHAR in Rob's procedure, why would you use datetime? Since you also tried character, I don't think that's a problem.

Why don't you print the string before it's executed so you can see how it's formed? Just add

Print 'INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table + ' WHERE ' + IsNull(@where,'1=1') + ' AND ' + @pivot + ' Is Not Null'

Sam

Sam,

I am a little new at this if you can't tell yet.

I can't get the sproc to save with ' WHERE DATE_CALC =''' + cast(@txtCurrent As varchar) +'''' at the end of the execute crosstab statement. I get an error incorrect syntax near '+'.

thanks,
vmon

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-27 : 15:21:18

quote:

as well as letting the proc prompt me for the value

Huh? How do you have a Proc prompt you for values?

Brett

8-)

vmon
Yak Posting Veteran

63 Posts

Posted - 2003-05-27 : 15:28:23

quote:

quote:

as well as letting the proc prompt me for the value

Huh? How do you have a Proc prompt you for values?

Brett

8-)

I am using MS Access data project. When I double click on the sproc it runs and parameters are prompted for. I have not found a way to do this with EM or QA but have not looked too hard either.

vmon

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-27 : 15:31:04

It's Access prompting you. Don't know how/if you can get prompted by a sproc...

Brett

8-)

ToddV
Posting Yak Master

218 Posts

Posted - 2003-05-27 : 15:31:23

Try setting that last parameter to a variable before calling the procedure.

DECLARE @WHERE Varchar(1000),
@txtCurrent Datetime

SET @WHERE = ' WHERE DATE_CALC = ''' + cast(@txtCurrent As varchar) + ''''
EXECUTE crosstab
'SELECT Caseworker FROM tblWeeklyAging GROUP BY Caseworker', ' SUM(Amount)', ' CONVERT(varchar(8),Date_Bucket,1)',
' tblWeeklyAging',
@WHERE

Edited by - toddv on 05/27/2003 15:32:24

vmon
Yak Posting Veteran

63 Posts

Posted - 2003-05-27 : 18:01:19

ToddV,

Works great. Thanks a bunch.

vmon

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources