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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

can an alias column be used in a WHERE clause

Posted By:      Posted Date: September 08, 2010    Points: 0   Category :Sql Server
Hi, I am using Alias to a return values that i get back form my function (money datatype). Once done with that, i need ot filter my final result set based on the Alias column. My question is can i use an Aliased column in a where Clause?   SELECT BSC.TypeId,BSC.ParentId,BSC.Id,BSC.Name,(SELECT dbo.UdfSumActualsByPeroid('2010-05-02','2010-05-09',BSC.Id,NULL,NULL,NULL)) AS Actual1,NULL FROM Subscriber.Budget.SubscriberCategory BSC WHERE BSC.SubscriberId = '0CCECB4E-C151-42BF-8F47-FD36ED3D2955' AND Actual1 > 0 ORDER BY BSC.TypeId,BSC.InterviewSequence

View Complete Post

More Related Resource Links

Column Alias in ORDER BY Clause -- not supported in SQL Server 2005/2008 -- Causes enter migration



We are migrating from SQL Server 2000 to SQL Server 2008 R2 and our web application having thousands of SQL Statement with "Column Alias in ORDER BY Clause ".It is very difficulty to change all statements.

One of article says that SQL Server 2005/SQL Server 2008 not support "Column Alias in ORDER BY Clause and we have to replace with column name" because of this issue If we change Alias name to column name, we have do enter application/integrity testing. it is going to be big deal.

our client Decided to stop migration, Please some help is there any other alternative.



how to pass column value in WHERE clause

Friends, my scanario is fully generic and i m implementing this in database level by your quick response and help... i m trying to demostrate my problem with some sample table and SP (please see comment in SP for more clarification and understanding) the question is which i also write in SP's bottom area, Question, HOW CAN I PASS VALUE FOR COLUMNS "WF_Company_ID" AND "WF_Employee_ID" FROM TABLE ##SAT IN ABOVE STATEMENT AND GET VALUE FOR COLUMN "UID" FROM TABLE "MAINTABLE Tables are, CREATE TABLE MAINTABLE(UID INT, WF_Company_ID INT, WF_Employee_ID INT, WF_Position VARCHAR(50), WF_TimeSheet_ID INT) INSERT INTO MAINTABLE VALUES(1, 100, 625, 'Clerk', 1234) INSERT INTO MAINTABLE VALUES(2, 101, 626, 'Clerk', 1235) --SELECT * FROM MAINTABLE CREATE TABLE SEARCHCOLUMNS(C_KEY INT, C_NAME VARCHAR(100)) INSERT INTO SEARCHCOLUMNS VALUES(1, 'WF_Company_ID') INSERT INTO SEARCHCOLUMNS VALUES(1, 'WF_Employee_ID') INSERT INTO SEARCHCOLUMNS VALUES(2, 'TOTAL_PRICE') INSERT INTO SEARCHCOLUMNS VALUES(2, 'TOTAL_AMOUNT') --SELECT * FROM SEARCHCOLUMNS SP is below, CREATE PROCEDURE [dbo].[Test2] @StrXML XML AS BEGIN TRY DECLARE @ColList NVARCHAR(MAX) DECLARE @SQL NVARCHAR(MAX) CREATE TABLE #tXML (InputXML XML) INSERT INTO #tXML (InputXML) SELECT @StrXML SET @ColList = STUFF ( (

can alias in select be used for selecting other column in that table?

Hi All,I want to use an alias name in a select clause to select other column in that table? select   top 1 (   case when CreatedByName <> '' then 'yy'         else 'xx' end) as filName, (filName + 'xx')from Order       But it throws error like " Invalid column name 'fileName'."Could you please help me out?

Write select query with a paremetrized where clause, and populate database with the retrieved column

I want to be able to retrieve column values using the dropdownlist values as parameters then populate the tbPO table with the retreived values. Where and how do I do it. ImportsSystem.Data.OleDb ImportsSystem ImportsSystem.IO Imports ?directcostDataSetTableAdapters PartialClass po_header  Inherits System.Web.UI.Page  Dim cn As OleDbConnection  Dim cmd As OleDbCommand  Dim dr As OleDbDataReader  Dim icount As Integer  Dim str As String  Dim vendor_id As Object   ?  Public Sub btn_click(ByVal sender As Object, ByVal e As System.EventArgs) Handles button1.ClickTry   context.Items.Add(     cmd.CommandType = System.Data.   cmd.CommandText = Dim context As HttpContext = HttpContext.Current"Company_Name", DropDownList3.Text)Dim cn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=C:\Users\Owner\Documents\Visual Studio 2010\WebSites\WebSiteJun25\App_Data\directcost.mdb;")Dim cmd As New OleDbCommand()CommandType.Text"insert into tbPO(ponumber, suppliername, ShipTo, basictype, DateNeeded, Purpose,ShippingInstr, ProjId) values(?,?,?,?,?,?,?,?)" cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.AddWithValue( cmd.Parameters.Add

Column Alias Usage Everywhere in a Query Following Definition


In the following query, you cannot use the column aliases in WHERE and GROUP BY clauses, then suddenly like thunder from the blue, you can use it in the ORDER BY clause. It is counter-intuitive. It is counter-productive as well.

SELECT	[Year]=YEAR(OrderDate),
FROM AdventureWorks2008.Sales.SalesOrderHeader
WHERE YEAR(OrderDate) > 2002
GROUP BY YEAR(OrderDate), MONTH(OrderDate)
ORDER BY [Year],[Month]
/* Year	Month	TotalSales
2003	1	1968647.184
2003	2	3226056.1486
2003	3	2297692.9898

I made a suggestion to enable column alias usage everywhere in a query. You can vote for this feature at Connect:


Expressions (with Where clause?) to Build Calculated Column


Hi All-  I thought perhaps what I wanted to do was simple, but I can't figure it out, so I'm hoping someone will give me some tips.  I have a dataset coming in with multiple years of data for employment figures.  I have the year and the geography set as parameters.  the user chooses the year and the state (or multiple years and states) and can see some statistical data.  One of the columns displayed is Employment (or # of Jobs).  What I am attempting to do is add a calculated column that will show me the 1 year pct change in Jobs.  If the user selects 2009, then the report would dynamically create the change.  I thought perhaps I could create an expression for that column that looks something like below

=(sum(Fields!empl.Value) where Fields!year.Value = @year - sum(Fields!empl.Value) where Fields!year.Value = @year - 1) / sum(Fields!empl.Value) where Fields!year.Value = @year - 1

I am relatively new to SSRS - so I apologize in advance if I haven't given you the appropriate information that will allow you to help with this inquiry, but any advice that you can give me would be GREATLY appreciated.  This type of calculation is something that we want to do often.  I know that I can add a 1 year % change to my dataset and pull it through - however, I want the change to be dynami

Tooltip for GridView Column Headers (ASP.Net)

I just wanted to add auto tooltips for my GridView headers. There are couple of ways but I don't want to use javascript and wanted to use simple 'title' attribute. It took couple of hours to get the working code supporting sortable columns. I thought it will be very useful for others. Here's the code and logic behind it.

Multiple Column Dropdownlist for the ASP.NET DataGrid

Based on my previous control "Multiple Column DropDownList for ASP.NET", I received many emails asking for the same control to be used in the DataGrid for web applications. Here we go.. This control can be used as the regular MS DropDownList in the DataGrid and also as a regular dropdownlist. It has all the properties, like DataTextField, DataValueField, DataSource, SelectedIndex etc. The download file contains the samples both in VB.NET and C#. In this sample, I have used the Northwind database of SQL Server.

Power of using Clause in SharePoint

The Using Clause will help to avoid memory leaks in SharePoint by automatically Disposing the MOSS objects.

For example when we create SPSite and SPWeb objects, if we don't dispose it explicitly it might create memory leaks,

Alternating row background for first column of a report


is there a way to make the first column of a report use the an alternating background color? 

I have the background color set for the entire row however it does not work for the first column if the color is set to an expression.  If I just set it to a color i.e. red it works just fine. If I insert a dummy column to the left it works just fine (but the dummy column does not work, presumably because it is first??).

In the file below only the textbox for part number does not show an alternating background.  All others work correctly.

                    <Textbox Name="PartNumber">

Use a column as parameter for a sds in another column; gridview


Hola, I have a gridview which is bound to a sds which returns 7 columns and I added two columns from the GUI.  What I want is to fill up the 9th column with DDLs and these  DDLS will get their data from a different sds which is using a stored procedure with a single parameter.  I created the 9th row, used template field, made the sds for 9th row, the stored_procedure, threw a DDL in Item template and all  I need now is to pass a parameter in the first column to the sds in the 9th column.  I can code this but i'm getting tired of aspx.cs :) and would like to do it from the GUI.

Pass a grid column value to a Modal


Hi, I have a requirement to hide a field called details on a gridview and show it in a jQuery modal dialog when users click on the Customer ID hyperlink. This grid is created programmatically. I will be hiding the details column via the CSS display:none property. Does some one know how can I pass the grid's selected hidden column value to the modal? Here is some of the code I started:

            GridView mygrid = new GridView();
            //.....................................More columns code for the grid

            //Here is where I need to pass the Details column value associated with the customer ID field selected to the Modal and open the Modal dialog.            
            HyperLinkField linkField = new HyperLinkField();
            string[] dataNavigateUrlFields = { "CustomerID" };
            linkField.DataTextField = "CustomerID";

Bug: Does not change column type from nvarchar to ntext


Scenario: If you create a table with a column of type nvarchar, populate it with some content and try to modify it using the WebMatrix editor, from nvarchar to ntext, it does not display any error message or warning that this is not possible. It silently saves and reverts the change, back to nvarchar. Wasted some hours on this issue as I thought my column was ntext, but in fact was still nvarchar.

Expected behavior: It should change to ntext, or if SQL CE doesn't support that, it should notify the user that no change was applied.

Display column from child table. Possible ?



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.

Rendering data with HTML tags in the DD Gridview for a selected column


I am having trouble finding out where and how to HTML Encode a cell's data on the Dynamic Data (v4.0) gridview of List.aspx.  As a simple case, suppose I have formatted cell data that is A<br/>B in the DB.    Obviously, I want A stacked on B in the cell.

It seems gridView1.HtmlEncode = true has gone away.

So maybe I'll try to catch it on the RowDataBound event:

protected void GridView1_RowDataBound(Object sender, GridViewRowEventArgs e)

            if (e.Row.RowType == DataControlRowType.DataRow)
                // Html Encode the cells


but this event never fires?

Has anyone figured out how to properly render HTML tags data in the List.aspx's GridView1? 

If I figure this out, then I can add a MetaAttribute called something like [EncodeAsHtml(true)] and be on my way.  Thanks!

Creating a user friendly alias for the website path


 Hello mates,

I am hosting my ASP.NET application on a Windows 2003 Server.

To browse to my application one has to type the path :http://serverName/applicationName

I want users to only type  applicationName on the web adress area.I believe there is something to do with alias and CNAME on DNS records that can adress this.

Any help on how to go about doing this will be highly appreciated.

Filter by Computed Column



After looking for a driver to open legacy dbf drivers to connect, the next level trouble a have is this one:

first, i can retrieve the data but when trying to filter by a parameter (somesc columns) i get an error.

The error says about the database can't determine some blah blah!

The error is a columm tha is formated (  1234  ) about  8 space, fixed. The values in the query appears like that. I resolve this by Trimming Ltrim(ColumnName) and i get this (1234).

Now in SqlExpress, how can i filter by the new Trimmed Expression? (This is a primary key that the DBF use)


SELECT column1, column2, column3


SELECT Ltrim(column1) as NewExp, column2, column3
Where NewExpre = @NewExpres                  -Error-


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