June 22, 2014

SQL Server - One to one record mapping between two table without any common column

Today I had to write a script that maps one to one records without any common columns between two tables. I think it would be helpful for you as well.

declare @Customer table (CustomerId int, CustomerName varchar(50))
declare @Branch table (BranchId int, BranchName varchar(50))

insert into @Customer values (1, 'Kale')
insert into @Customer values (2, 'Gore')
insert into @Customer values (3, 'Sailo')
insert into @Customer values (4, 'Mailo')

insert into @Branch values (1, 'Dharan Branch')
insert into @Branch values (2, 'Biratnagar Branch')

;with Customer (RowNumber, CustomerId, CustomerName) as
    select row_number() over(order by CustomerID) as RowNumber, * from @Customer
,Branch (RowNumber, BranchId, BranchName) as
    select row_number() over(order by BranchId) as RowNumber, * from @Branch
select cust.CustomerId, cust.CustomerName, bra.BranchId, bra.BranchName
from Customer as cust
left join Branch as bra
on cust.RowNumber = bra.RowNumber

June 17, 2014

Comman Table Expression and its usage

According to Microsoft:

A common table expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE or CREATE VIEW statement. A CTE is similar to derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

A CTE can be used to:
  • Create a recursive query.
  • Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
  • Enable grouping by a column that is derived from a scalar subselect or a function that is either not deterministic or has external access.
  • Reference the resulting table multiple times in the same statement.
Using a CTE offers the advantages of improved readability and ease in maintenance of complex queries. The query can be divided into separate, simple logical building blocks. These simple blocks can then be used to build more complex, interim CTEs until the final result set is generated.

CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

Now, I would like to show some examples so far I have experienced.

For Example:

;WITH tbl(Id, AvgValue)
       SELECT Id, Avg(Value) as AvgValue
       FROM Quantity
       GROUP by Id

SELECT Name, AvgValue
customer as cst on cst.id = tbl.id
Order by Name

In the above example, you can see we have used subquery in order to use in the main query. It's kind of virtual view.


Lets suppose there are 3 tables which holds information about Customer Name and their respective County. First table named Customer contains their basic info, second table County and third table named CustomerCounty has relationship between Customer and County table.

;WITH tblCTE (RowNumber, CustomerId, CountyName, County) AS
  SELECT 1, CC.CustomerId, MIN(C.CountyName), CAST(MIN(C.CountyName) AS VARCHAR(Max))
  FROM CustomerCounty as CC
  INNER JOIN County as C ON CC.CountyId = C.CountyId
  GROUP BY CustomerId


  SELECT CTE.RowNumber + 1, CC.CustomerId, C.CountyName, CTE.County + ', ' + C.CountyName
  FROM CustomerCounty as CC
  INNER JOIN County as C ON CC.CountyId = C.CountyId
  INNER JOIN tblCTE as CTE ON CTE.CustomerId = CC.CustomerId
  WHERE C.County > CTE.County

SELECT CTE.CustomerId, S.Name, Areas
INNER JOIN Customer as C ON C.CustomerId = CTE.CustomerId
INNER JOIN (SELECT CustomerId, MAX(RowNumber)
AS Max FROM CTE GROUP BY CustomerId) as tbl
ON CTE.RowNumber = tbl.Max AND CTE.SalesmanId = tbl.CustomerId
ORDER BY CustomerId

In the above example we have see Common Table Expression has two queries, first one acts as a base query and another acts as a recursive query. The second query is using CTE so this helps in recursive processing. The above example helps to concatenate string of different row into single one.


CustomerId Name Areas
1                 Harke       Luton, Bedford, Milton keynes
2                 Matt         Hitchin, Stevenage

June 14, 2014

SQL Server - Len() vs DataLength()

DataLength returns the length of any expression in bytes which also includes trailing spaces if exits. DataLength is specially useful with varchar, varbinary, text, image, nvarchar and ntext data types because these data types can store variable-lenght data.

Len returns the number of character, rather than the number of bytes, of the given expression excluding trailing spaces.

So, we can say Len function will first right trim the expression and counts the expression where as DataLength function gives the storage space required for the characters.

For Example:

select Len('Avesh') -- Returns: 5
select Len('Avesh ') -- Returns: 5
select Len(' Avesh') -- Returns: 6
select Len(' Avesh ') -- Returns: 6

select DataLength('Avesh') -- Returns: 5
select DataLength('Avesh ') -- Returns: 6
select DataLength(' Avesh') -- Returns: 6
select DataLength(' Avesh ') -- Returns: 7

June 11, 2014

SSRS Join Function - Display/Set multi -value parameter

Join function helps to display array value.

On the parameters tab of the query definition, we need to set the parameter value

=JOIN(Parameters!.Value, ",")

To display multi-value parameter in textbox

=JOIN(Parameters!.label, ","

June 10, 2014

SSRS - Passing Multiple Parameters with Stored Procedure

1) Add data source as needed.
2) Create dataset as needed. Below, you can see there are 4 parameters. We will focus on contractname dataset for multi values parameter.
3) You can see, TSQL stored procedure is used to get the list of contract name for contractname dataset. This stored procedure returns the list of contract name.

4) Then you need to go to parameter. Right click on contractname parameter -> go to properties.
5) Define the values of dataset from Available Values.

6) In the general tab of the properties, select Allow multiple values

7) Run the report. You will be able to see as below.

8) When the parameter is passed on the stored procedure, it should be like
Select * from ContractTable where ',' + @contractname + ',' Like '%,' + Contract + ',%'
and date between @startfiscalyear and @endfiscalyear and quarter = @quarter

June 8, 2014

SSIS: Cannot Create Connector, The destination component does not have any available inputs for use in creating a path

When creating SSIS package, I was trying to link the data flow source to destination I got the error below.

After looking properly, I realized that instead of using data flow OLE DB destination, I was using data flow OLE DB source in the data flow destination. It was a quick fix, I just had to replace OLE DB Source with OLE DB destination.