Wednesday, October 6, 2010

Find the Last Apparition of a Value in String

Well, indeed, I spent few minutes today trying to find the last apparition of a value in string. All this in a  simple T-SQL query in SQLServer 2005. The reason I wanted this is that I need the last part of a string separated by a known delimiter.
I was having a string like this:
declare @a as nvarchar(50)
set @a = 'ssss$kkkk$ooooo$abcde'

I searched the Internet a bit, but with no success. But after few minutes I remembered a function that I seldom use, the reverse. From here, all was as simple as:
select charindex ('$', reverse('ssss$kkkk$ooooo$abcde') )
and further play with this to get the value I need

Update: Of course, using it to trim out the last part of the string is going to be something like this:
select Substring(@a, 1, Len(@a) - charindex ('$', reverse(@a)))

Wednesday, September 22, 2010

SSRS. Must declare scalar variable @Parameter1

Well, one of the errors in Reporting Services 2005 says:

An error occurred during local processing
An error has occurred during report processing
Query execution failed for data set 'Name of the data set'
Must declare the scalar variable @Parameter1

This can be pretty annoying especially when you know you have the report parameter and it used to work. What happens is that, due to some black magic that I cannot explain, the data source is loosing the list of parameters. So the fix is to either add the parameters one by one or just delete and recreate the data source and everything will work.

Wednesday, September 8, 2010


Well, that's what happen when you don't carefully read the documentation. I have recently discovered the benefit of the WITH ROLLUP clause in SQLServer 2005, and I thought what it was doing was returning all possible combinations of grouping.
Wrong, that's the WITH CUBE.

Using the WITH ROLLUP I found out that the number of rows returned by my query was dependent on the order of the fields in the group by clause. Funny.

Well, to keep it short, I found this:
because "ROLLUP generates a result set that shows aggregates for a hierarchy of values in the selected columns" we get results depending on this hierarchy.

unlike it, what I needed was the WITH CUBE clause: CUBE generates a result set that shows aggregates for all combinations of values in the selected columns.