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)))

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.

ROLLUP vs CUBE

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: http://msdn.microsoft.com/en-us/library/ms189305%28SQL.90%29.aspx
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.

Visual Studio vs ReportingService

Today I almost pulled my hair off again (well, what rests of it). I was having a local report that used to work until I added some fields in the stored procedure that was filling it. After wandering and thinking and checking the SP, I found this:

http://social.msdn.microsoft.com/Forums/en-US/vsreportcontrols/thread/64532304-a4e8-466e-b0ce-65ee723a84b7

Visual Studio Editor

While working on a web site recently, I used Visual Studio 2008 for a small wizard in ASP.NET.

It was cool at the beginning, but it become really painful when, after a while, I was trying to update the tables on the page. Seems like Visual Studio is creating a lot of stupid style-sheets when you drag the table's rows or columns. Hilarious things, like very height rows of the tables, happen. When you try to resize the row back to it's normal size, no way. The only way I found was to manually edit the ASPX and delete the generated styles. And I'm not the only one having problems with it, a friend whom I am working with reported the same problem.

While I like Visual Studio for it's ease of use and clean interface, I found the way it deals with the wizard to be very...let's say at least uninspired. So, what can be done ?
1) Integrate really often, so if the things get messed up you can come back to a good version.
2) Manually edit the ASPX for small changes. It's much faster than trying to convince VS to work the way you'd expect.

Don't get over OVER

So I knew about over but didn't have the opportunity to use it every day. But today, I needed a query that would group the data by two criteria; for each group I have a number and then for each group I need the row with that number to be the MAX in the group by first criteria.

Like this: I have table T with Columns A, B and theNumber and I do a

SELECT A,b, sum(theNumber) 
FROM T 
GROUP BY A, B

And from this I need the rows with the max sum(theNumber) per A

That resulted into

SELECT A, min(B), theNb FROM ( 
 SELECT A, B, theNb,  MAX(theNb) OVER (PARTITION BY A) maxim
 FROM ( 
  SELECT A,B, sum(thenumber) as theNb FROM T
        WHERE someCondition
        GROUP BY A, B) aa 
 )  bb 
WHERE theNb=  maxim

instead of big subQueries and clumsy code

Be Discrete

Well, sometimes being discreet is being efficient :)
I recently had to optimize some reports for speed and I found one query that took around 8 seconds.
The query, a pretty simple select was doing

SELECT blah, blah
FROM theTable
WHERE Year <= @Year
AND Year > @Year - 10

What I did was using the ugly, but efficient

SELECT blah, blah
FROM theTable
WHERE Year IN (@Year-9, @Year-8, @Year-7, @Year-6, @Year-5, @Year-4, @Year-3, @Year-2, @Year-1, @Year)


The execution time dropped from 8 seconds to about 900 ms.

UNPIVOT SQL SERVER OPTION

Sometimes reading about new features of the products you are using help a lot.

Using UNPIVOT I managed to reduce the length of a query from 8 seconds to about 650 - 700 ms.
The old query was doing a UNION between data based on data from some columns. The initial Query was doing this (data changed for privacy reasons):

SELECT country
, year
, SUM(cases) AS nbCases
, isNotValidSomething
, isValidSomething
, 'some stuff' AS ResultType
, 1 AS SortOrder
, CASE Column01 WHEN 'S' THEN Sum (cases) ELSE 0 END AS Sensitive, 
CASE Column01 WHEN 'R' THEN Sum (cases) ELSE 0 END AS Resistant, 
CASE Column01 WHEN 'Unk' THEN SUM (cases) ELSE 0 END AS Unknown
FROM vAll 
WHERE 
Country  = @Country 
AND Year =@Year 
AND Column01  IS NOT NULL
GROUP BY country, year, isNotValidSomething, isValidSomething
, Column01
, rType
UNION
SELECT country
, year
, SUM(cases) AS nbCases
, isNotValidSomething
, isValidSomething
, 'some other stuff' AS ResultType
, 2 AS SortOrder
,CASE Column02 WHEN 'S' THEN Sum (cases) ELSE 0 END AS Sensitive, 
CASE Column02 WHEN 'R' THEN Sum (cases) ELSE 0 END AS Resistant, 
CASE Column02 WHEN 'Unk' THEN SUM (cases) ELSE 0 END AS Unknown
FROM vAll 
WHERE Country  = @Country 
AND Year =@Year 
AND Column01  IS NOT NULL
GROUP BY country, year, isNotValidSomething, isValidSomething
, Column02, rType

UNION
....

(there are 8 queries like this in UNION)

The new query is doing :

SELECT country, year
, SUM(cases) AS nbCases
, isNotValidSomething, isValidSomething, ID, Cols FROM vAll
UNPIVOT (
ID FOR Cols in ( Column01
,Column02
,Column03
,Column04
,Column05
,Column06
,Column07
,Column08
)
) up
WHERE country = @Country
AND year = @Year
GROUP BY country, year, isNotValidSomething, isValidSomething, up.ID, Cols
ORDER BY country, year desc, Cols


Thanks to this post.

All best,
Radu

Publishing WPF Site

OK, we (almost) finished the WPF application and wanted to install it.
Nothing simpler than this: right-click -> publish -> choose location -> run XBAP. I wish !!!
it kept telling me desperately that "System.IO.IOException: Cannot locate resource 'app.xaml'."

Some google research lead me to this post. I tried dropping out my custom control and replace it's call with the code itself and it suddenly worked.
Thank you , guys, I was really out of ideas about it !
This is only my part of problems, my budy is struggling with thinks like : missing ADODB on the remote machine, dll not in the GAC, etc

Strange strange world...:)

Embedded Report crashing

I begun feeling the smell of WPF few weeks ago. I'm developing an application where we need reports, of course. Misteriously I noticed my report crashing on the second run. It works OK when I run it for the first time, but if I navigate back to the application and try to run the report again, I get a "nice" FatalExecutionEngineError with a code = 0xc0000005
Search the 'net deperately but no success until I tested with the same report but no data within. I noticed it worked OK if no data is loaded.
Well, I decided to test more: I created a blank report and called it from my code; all worked fine i was able to go to the report and back as many times as I wanted.
Cool, a small step forward. I added some simple labels and all continues to work.
It stopped working when I passed the parameters. Strange enough but as i don't have too much times I droped the parameters and let it run. I send the data in the DataSet.


However today my report stopped working.
The cause seems to be a field with the value set to the expression =Right(TimeValue(Fields!Date.Value), 10)

what is wrong with this ? the date field is a Date...maybe I'll never know. I just passed it from outside as a string and display directly.


But the problems with the rdlc reports don't end now. I wanted a simple pageNumber , when I added this in the future the report start crashing again on the second run.