iLearn

Hi Guys Just encountered a scenario where I needed to handle numeric and non numeric types using Case statement. Here is the synopsis of challenge and solution:- Challenge :- You have a varchar column in your input table having values such as 1.1111, 34, John. CREATE TABLE #TEST ([UpdatedValue] VARCHAR(100)) INSERT INTO  #TEST VALUES('1.1111'),('34'),('John') select * from #TEST Requirement :- You need to round off the decimal values to 2 places  Solution :- I got to know while implementing that for a case statement to work the output data i.e. the resultant data from each case should be of same type. It won't be able to treat numeric and non numerics together. That is why I personally found it a bit tricky but on brain storming solution looked to be very simple. The basic idea is to use different select queries and then union together. Here it is :- select CASE WHEN CHARINDEX('.',[UpdatedValue],0) >=1 THEN CAST( C

Hi Folks, Comments play an important part in writing any code. Especially when somebody else reads your code. So the other day I was writing a stored procedure and some comments on top to list the tasks being performed inside the stored procedure. We are using sql server 2005 and the code snippet was something like this :- /*abc /* /* ----- ----The logic of the stored procedure is ... */ */ */ alter proc abcd @i int as select 1 I was able to create the sp without any problems, however on clicking modify (when you try to see or edit the sp inside ssms) it gave me an error:- syntax error in textheader of stored procedure On further searching online I found that it has to do with embedded comments like the one as following :- /* /* ----*/ */ -------------------------------------- If you notice above lines you ll see comments are being embedded inside parent /* -----*/ comments.  So I got rid of the internal comments like this /*abc ----- ----The logi

Hi Folks, Hope you all are doing good. Would like to start with praying for all those who have been affected by a deadly earthquake in Nepal. Just when it seems that life is good something like this happens, to remind us of that we are not permanent here. Death is a bitter truth. Moving on this week I again ran into one of the bugs in reporting services 2005 i.e. we cannot have aggregates of aggregates in a situation where we have a parent group and a child group. Requirement -    See the resultset below pid pdesc cid cdesc 1 harish 1 ninu 1 harish 2 lovely 2 asha        3  ricky 2 asha   4 hina We have 2 groups one is parent having pid has the identifier and second group is child having cid as the identifier We want to calculate the sum of cids accross parent and child group (off-course at child level it would be the individual cid itself) Implementation -  so I have written the expression sum(Fields!cid.Value,"details") adjacent to c

Hi Folks Greetings to everyone!!! So last week one of the report was having a performance issue. It had 10 datasets ,5 data regions and a report parameter. Regions displayed cascading information based on user's selection of parameter. For example,. lets assume the parameter name to be ReportLevel, it would have 3 possible values - Country, State, City. This being a multi valued parameter will decide which data region to be displayed. In this example we will have 3 datasets binded to 3 tablix or tables or data regions. So if user selects Country only than country data region will be displayed and like wise Country and city level details will only be shown if the user selects the value as country, city. Now I assumed (most of you guys would already know the correct behaviour :-) ), only those datasets or queries would be executed which are being displayed. On further having a discussion with one of my friend and an expertise in SSRS, I came to know that all datasets will

Hi Folks Highlighting on the issue that I faced last week while working on reporting services 2005. Requirement :- A data region (table) is to be displayed based on the parameter value. Table has a toggle facility on detail rows of a group like so on export to excel it should look like this  Solution :- I found out that is a known issue in ssrs 2005. On adding an expression at the visibility property of the data region it looses the toggle functionality in the excel. However it works fine with 2012 reporting services. Your comments and feedback are welcome here.

Hi Folks, When you work in a offshore - onsite setup in IT industry and if you are fortunate :-) to be a part of the offshore team then there are many compromises that you have to make. Out of those one such is to develop your code based on some illogical limitations enforced upon you by the onsite development/dba team. That is where you improvise and learn. I am going to throw some light on one such situation I faced in my last project. But this time thankfully the onsite dba gave me some tips to go about my task. Requirement :- You are given a database that has some tables in it. However we cannot access these tables directly so we are given access to some user defined functions only. For example one such table is emp_man which looks like this: and there is udf corresponding to this i.e. ///////////////////////////////////////////////////////////////////////////////////////////// create function ufn_getempl (@hrdate datetime) returns table   as   return   select *