Re: SQL Server 2000 + OLEDB + simulating pivot table = CASE...WHEN gone

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

(krishnou@xxxxxxxxxxx) writes:
I have a problem executing a stored procedure through OLE DB and ASP.
The SP in question simulates a Pivot and is explained here
http://www.sqlteam.com/item.asp?ItemID=2955

When I run the following query :
EXECUTE crosstab 'select title from titles inner join sales on
(sales.title_id=titles.title_id) group by title',
'sum(qty)','stor_id','stores'

From the Query analyser: everthing runs well and statement generated
by the SP is ok :
select title , '6380' = sum( CASE stor_id WHEN '6380' THEN qty END),
'7066' = sum( CASE stor_id WHEN '7066' THEN qty END), '7067' =
sum( CASE stor_id WHEN '7067' THEN qty END), '7131' = sum( CASE
stor_id WHEN '7131' THEN qty END), '7896' = sum( CASE stor_id WHEN
'7896' THEN qty END), '8042' = sum( CASE stor_id WHEN '8042' THEN qty
END) from titles inner join sales on (sales.title_id=titles.title_id)
group by title


BUT, when I run the same query from an ASP page using an oledb
connection, the statement generated by the query is wrong, the
"CASE .... WHEN" statements are gone, only the "END" remains:
select title , '6380' = sum( qty END), '7066' = sum( qty END), '7067'
= sum( qty END), '7131' = sum( qty END), '7896' = sum( qty END),
'8042' = sum( qty END) from titles inner join sales on
(sales.title_id=titles.title_id)
group by title

Does someone have an explanation for this strange behavior ?
I tried to execute the SP using command object and also plain text...
no difference, I didn't try through ODBC though...

Bad news. That procedure you found relies on undefined behaviour.
To wit,

SELECT @x = 0
SELECT @x = @x + col FROM tbl

May result in @x getting the sum of col, but @x could also be assigned
something else.

It could be addressed by replacing

SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +
stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '
+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM
##pivot

with a cursor over the ##pivot table.

Or check out RAC at http://www.rac4sql.net/, a third-party product
which is good at this. (They say. Never had the need myself.)

--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.

Quantcast