.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan
Fauzul Azmi
Asad Ali
Post New Web Links

Common Table expression

Posted By: Alwyn Duraisingh.M     Posted Date: September 20, 2010    Points: 2   Category :Sql Server
 
Common Table Expression(CTE) is aexpression which will be
called recursively until the condition is satisfied. It's
introduced in Sql Server 2005. This can be used in both
Select and DML statements. The result set generated by the
CTE is same as a hybrid Derived Table meets declared Temporary
Table.


View Complete Post


More Related Resource Links

Reviewer suggested common table expression instead of adding 12 fields

  
So during a code review a DB Mgr suggested that I use a common table function instead of adding the fields below. No idea how to possibly do this. Anyone have any ideas? Here is a code snippet that he wants to lose: SELECT   LP .leaseID, sum   (paymentAmount1+paymentAmount2+paymentAmount3+paymentAmount4+paymentAmount5+paymentAmount6+paymentAmount7+paymentAmount8+paymentAmount9+paymentAmount10+paymentAmount11+paymentAmount12) as restPayment FROM   leasePaymentScheduleDetails AS L inner join leasePaymentSchedules AS LP ON L.leasePaymentScheduleID = LP.leasePaymentScheduleID WHERE   paymentYear>=@curyear+5 GROUP   BY LP .leaseID   Any input is appreciated.

How to use the table which is created with CTE(Common Table Expression) more than one select,insert

  
Hi,

I faced one problem while working with Common Table expressions in Sqlserver 2008.

the problem is How to use the table(means temporary table)  which is created  by with  CTE's after that one select statement.

Actaully the table(which is created using with CTE) is automatically deleted, one's after execution of any select,insert,update or create view statement .Right!

But I needed that table second time also. How it is possible.

please help if any one knows..........

Thanks in Advance........

Pavan.

Common Expression table vs Derived table

  

Hello ,

I have read about Common expression table . and I have read the following :

   " Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query"  .

  I want to know what does it mean ? and I need a sample to understand it .

   Thanks

     Hema


ASk

Data Points: Common Table Expressions

  

In T-SQL, views and derived tables serve similar purposes and have their advantages. But when working with SQL Server 2005,there's a third option--using Common Table Expressions (CTEs). John Papa explains.

John Papa

MSDN Magazine October 2007


Regular Expression to Match
in HTML

  

Here is the kind of text I want to match via a regular Expression

The id="dgSchedule" is always present in the TAG but its location may differ

The table is span over multiple line/contains white spaces/tabs...

I have the regular expression to match the start and end tag respectively over a single line

\<table .*\>

\</table\>

The problem is to match the whole table span over multiple lines

<table cellspacing="0" rules="all" border="1" id="dgSchedule" style="border-style:None;height:100%;width:100%;border-collapse:collapse;">
	<tr class="blackbar" align="center" style="background-color:#7FB4DE;font-family:Verdana;font-weight:bold;height:20px;">
		<td>From Place</td><td>To Place</td><td>Time</td><td>Bus Type</td>

	</tr><tr style="background-color:#DEECF5;">
		<td>1</td><td>2</td><td>00:15</td><td>Express</td>
	</tr><tr style="background-color:#EFF5FA;">
		<td>2</td><td>3</td><td>00:15</td><td>Express</td>
	</tr><tr style="background-color:#DEECF5;">

		<td>3</td

Expression box only shows the first row of data in a repeating table...

  
Expression box only shows the first row of data in a repeating table, how can I display all rows of a repeating table in an expression box, preferably without code?

Export Visio Database Table Names to Excel

  
If you use the Enterprise Architect edition of Microsoft Visio for data modeling regularly, then there is a good chance that at some point you've wanted to export just the table names into Excel. You might want to do this to map logical ERD entities to physical data model tables, track project status by entity, or track overlap between database versions.

Insert value using Table Value Functions

  
a real gem in Sql Server 2008. mostly people still using Stored procedure may be they shifted to SQL Server but they are not using TVF right now.

Using jQuery to Filter Table Rows

  
The project is using the .net GridView control, so I had limited control over the output HTML code. Still, I think this code can work for most tables. One thing to notice: you should use the class "filterable" on your table or on one of its parents for the code to work.
First, we need a text box:

RadioButton Groups, Table Rows And NamingContainers in asp.net

  
The RadioButtonList. Very handy for inviting users to select just one item from a list although because of the screen real estate it takes up, used less and less in favour of the DropDownList. In plain HTML, the browser knows to enforce the unique selected value amongst a list of radio buttons because they all have the same value for their name attributes.

RadioButton Groups, Table Rows And NamingContainers

  
(In which AJAX-enhanced CheckBoxes become more useful than RadioButtons but inheritance saves the day, and a simple RadioButton-derived control establishes the purpose of a control's naming container)

The RadioButtonList. Very handy for inviting users to select just one item from a list although because of the screen real estate it takes up, used less and less in favour of the DropDownList. In plain HTML, the browser knows to enforce the unique selected value amongst a list of radio buttons because they all have the same value for their name attributes.

Building a 3-Tier App with Silverlight 3, .NET RIA Services, and Azure Table Storage

  
Silverlight 3, .NET RIA Services, and Windows Azure Services Platform makes a 3-tier Cloud application easier to build: Silverlight 3 as presentation tier, .NET RIA services as the business logic and data access tier, and Windows Azure Table as the data storage tier. The sample application in this article demonstrates the architecture with a simple Survey application with all these technologies working together from Windows Azure.

Temporary tables in SQL Server vs. table variables

  
When writing T-SQL code, you often need a table in which to store data temporarily when it comes time to execute that code. You have four table options: normal tables, local temporary tables, global temporary tables and table variables. I'll discuss the differences between using temporary tables in SQL Server versus table variables.

Import XML File to SQL Table

  
Here is a brief example of importing an XML file into SQL Server table. This is accomplished by using the BULK option of OPENROWSET to load the file, and then utilizing the XQuery capabilities of SQL Server to parse the XML to normalized table format. This example requires SQL server 2005 or SQL Server 2008.

First, the following XML is saved to XML file C:\Products.xml.

Adding the table row below a particular row.

  

What is the best method in jQuery to add an additional row to a table as the row below a particular row?


Checkboxes from data table

  

I have a data table that contains some values for Certifications.  This table can be updated in a different part of my application.  On the web page I am currently working on, I would like to have check boxes appear for every value in the data table.  I have been struggling with this trying to use a For Each loop and I cannot get it to work for anything.  Any suggestions? 

I am using the following Razor syntax to obtain my data (I know this works because I can display it in a WebGrid):

@{

var db = Database.Open("MyConn");

Display column from child table. Possible ?

  

Hello,

I'am using dnamic entity with EF4. On a list page of a table, I would like to display a column containing information from a child table of the current element. Sample :

Order List :

Order Data | Required Date | Shipped Date | etc... | ... | Customer Name (foreign key with tostring() method override) | Customer Postal Code (Column that I want to add) |

I don't known how to do that. Is it possible ? Maybe I have to to create my own Metadata proxy that will add dynamicly a column on the MetaColumn list of the table.

Does someone have an easier or better idea ?


Thank you for any help.


Categories: 
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend