Skip to main content

#SQL CTE(Common Table expressions)

SQL Server 2005 and on wards, a very powerful feather has been added for the programmers' benefit called CTE.

 CTE is again a temporary result set derived from the underling definition. 

 Common Table Expressions offer the same functionality as a view, but are ideal for one-off usages where you don't necessarily need a view defined for the system.

 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.

 Common Table Expression Syntax
A Common Table Expression contains three core parts:

  • The CTE name (this is what follows the WITH keyword)
  • The column list (optional)
  • The query (appears within parentheses after the AS keyword)


The query using the CTE must be the first query appearing after the CTE.

 Syntax :

 WITH expression_name [ ( column_name [,...n] ) ]
AS
( CTE_query_definition ) 

 Example

(* table scheme is at end of this post) ;WITH myCTE(Name,Age,Designation,RCOUNT)
AS
(
select
Name, 
Age,
Designation,
ROW_NUMBER() over(partition by Name, Age, Designation order by Name) as rcount
From
DupEmployee
)

SELECT * FROM myCTE

 Important point that needs a mention is the SELECT followed by the CTE definition, any operation SELECT, INSERT, UPDATE, DELETE or Merge can be performed immediately after the CTE and the CTE lasts only for a single such operation.

 ;WITH myCTE(Name,Age,Designation,RCOUNT)
AS
(
select
Name, 
Age,
Designation,
ROW_NUMBER() over(partition by Name, Age, Designation order by Name) as rcount
From
DupEmployee
)
update myCTE set Designation='Duplicate' where RCOUNT>1

SELECT * FROM DupEmployee order by Name

 ;WITH myCTE(Name,Age,Designation,RCOUNT)
AS
(
select
Name, 
Age,
Designation,
ROW_NUMBER() over(partition by Name, Age, Designation order by Name) as rcount
From
DupEmployee
)
delete FROM myCTE where RCOUNT>1

 Table creation Code
CREATE TABLE [DupEmployee](
[Name] [varchar](50) NULL,
[Age] [int] NULL,
[Designation] [varchar](50) NULL,
 )

  insert into [DupEmployee] values('Raj',32,'TL')
 insert into [DupEmployee] values('Deena',28,'SS')
 insert into [DupEmployee] values('Raj',32,'TL')
 insert into [DupEmployee] values('Gow',27,'TA')
 insert into [DupEmployee] values('Raj',32,'TL')
 insert into [DupEmployee] values('Siva',27,'TTL')

 insert into [DupEmployee] values('Gow',27,'TA')
Labels:

Comments

Post a Comment

Popular posts from this blog

#EF : DbEntityValidationException - How can I easily tell what caused the error?

While calling  SaveChanges  on my  DbContext , I get the following exception: System.Data.Entity.Validation.DbEntityValidationException: Validation failed for one or more entities. See 'EntityValidationErrors' property for more details. This is all fine and dandy, but I don't want to attach a debugger every time this exception occurs. More over, in production environments I cannot easily attach a debugger so I have to go to great lengths to reproduce these errors. How can I see the details hidden within the  DbEntityValidationException ? Answer :  The easiest solution is to override SaveChanges on your entities class. You can catch the DbEntityValidationException, unwrap the actual errors and create a new DbEntityValidationException with the improved message. Create a partial class next to your SomethingSomething.Context.cs file. Use the code at the bottom of this post. That's it. Your implementation will automatically use the overriden Save...
Post a Comment
Read more

Use Log4Net in C# windows form Application

we are going to learn on how to use the Log4Net library for creating logs. Create a new windows form application in VS. Install Log4Net library Add to AssemblyInfo.cs  Configure in  App.config Use in code  Install Log4Net library Then install the Log4Net library from the Nuget Package library.           log4net by The Apache software foundation 2.0.8 (i installed the latest). Add to AssemblyInfo.cs  After installing this package, open up AssemblyInfo .cs file under the Properties folder and add the log4net assembly information into it (under the other assembly information.).    [assembly: log4net.Config.XmlConfigurator(Watch= true )]  Configure in  App.config Now, open the App.config file and enter required details for LogNet to work. <configSections>       <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net, Version=1.2....
1 comment
Read more

#MVC Authorize Attribute

Authorization is the process of determining the rights of an authenticated user for accessing the application's resources. The Asp.Net MVC Framework has a AuthorizeAttribute filter for filtering the authorized user to access a resource. Authorize Attribute Properties Properties Description Roles Gets or sets the roles required to access the controller or action method. Users Gets or sets the user names required to access the controller or action method. Filtering Users by Users Property Suppose you want to allow the access of AdminProfile to only shailendra and mohan users then you can specify the authorize users list to Users property as shown below. [ Authorize ( Users = "Raj,Deena,Siva,Gow" )] public ActionResult AdminProfile () { return View (); } Filtering Users by Roles Property Suppose you want to allow the access of AdminProfile action to only Admin and SubAdmin roles then you can specify the authorize roles...
Post a Comment
Read more