Wednesday, 11 March 2015

Using Common Table Expressions(CTE)

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, DELETE, or CREATE VIEW statement. A CTE is similar to a 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.

e.g.-

;with SelectCta(Bundle_No, User_Id, AllotDate, UserName, NoofBills,Net_Amt, Claim_Amt,Status,Alloted_Date,BundleNoText,AllotedUserId, TPA, billdated, TPAID) as
(
SELECT DISTINCT
                         IE_BundleNo.Bundle_No, IE_BundleNo.User_Id, CONVERT(varchar, IE_BundleNo.Alloted_Date, 103) AS AllotDate, UserLogin.UserName, IE_BundleNo.NoofBills,
                         IE_BundleNo.Net_Amt, IE_BundleNo.Claim_Amt, IE_BundleNo.Status, IE_BundleNo.Alloted_Date, IE_BundleNo.BundleNoText, IE_BillAllotStatus.AllotedUserId,
                        (select DISTINCT InsurerComName from IE_Master_InsuranceCompany where IE_Master_InsuranceCompany.InsurerComID=IE_BillAllotStatus.TPAID)  AS TPA,
'From ' + CONVERT(varchar, IE_BillAllotStatus.BatchDateFrom, 103) + ' To ' + CONVERT(varchar, IE_BillAllotStatus.BatchDateTo, 103) AS billdated,
IE_BillAllotStatus.TPAID
FROM            IE_BundleNo INNER JOIN
                         UserLogin ON IE_BundleNo.User_Id = UserLogin.UserID INNER JOIN
                         IE_BillAllotStatus ON IE_BundleNo.BundleNoText = IE_BillAllotStatus.BundleNoText
)
select Bundle_No, User_Id, AllotDate, UserName, NoofBills,Net_Amt, Claim_Amt,Status,Alloted_Date,BundleNoText,AllotedUserId, TPA, billdated, TPAID,
dbo.Get_Insurencecompany(TPAID,Bundle_No ) as InsCompanyName
 from SelectCta

No comments: