.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

Column Alias Usage Everywhere in a Query Following Definition

Posted By:      Posted Date: October 04, 2010    Points: 0   Category :Sql Server

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:


View Complete Post

More Related Resource Links

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?

Efficiency: new column in source query or derived column task?

Hi All, I've just started working on an SSIS package that pulls data from an OLE DB Source by a query. A new column needs to be added based on the value of a queried column. I was wondering if it's better to do that in the query or with a derived column? A simple example: I have a table that contains CustomerName and CustomerCode (this one can be V /valid/ or I /invalid/). I need to store the CustomerCodeDesc in a separate column in the destination table. Is it better to alter the query like this: SELECT CustomerName, CustomerCode, CASE WHEN CustomerCode = 'V' THEN 'Valid' ELSE 'Invalid' END AS CustomerCodeDesc FROM CustomerTable Or is it better to use a DerivedColumn task in the DataFlow? Or maybe it doesn't really matter...

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

Pipe SQL Query function into Variable with no column headings

Hello, I don't know if I'm in the right forum but could not find one for Powershell. I need to return 1 piece of data from a sql database to a variable. I don't want the column headings. Here's what I have so far:         Function GetMenuGroup ([int]$StoreID) { $SqlConnection = New-Object System.Data.SqlClient.SqlConnection $SqlConnection.ConnectionString = "Server=Servername;Database=DatabaseName;Integrated Security=True" $SqlCmd = New-Object System.Data.SqlClient.SqlCommand $SqlCmd.CommandText = "exec storedProcedure $StoreID" $SqlCmd.Connection = $SqlConnection $SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter $SqlAdapter.SelectCommand = $SqlCmd $DataSet = New-Object System.Data.DataSet $SqlAdapter.Fill($DataSet)|out-null $SqlConnection.Close() $DataSet.Tables[0] } $MG=Getdata 2 |out-string Here's the results I get now - How do I return just "DatathatIreallyWant" in a string variable? I don't want the column or underline. ColumnName ------------- DataThatIreallyWant   Thank you, MG2

How to filter managed meta data column using page query string?

I have two managed term sets as follows: Articles:Downloads Projects:Downloads I am trying to use a content query web part to filter a list based on the above manged meta data column. I want to filter out only "Articles:Downloads" Scenario 1: When I edit the web part and directly set the filter option with the managed meta data column to "Articles:Downloads" it works fine. When I export the web part, in the .webpart file, the "FilterValue1" property has a value "Downloads|9096e43b-555d-4879-9acc-b4ada9ea9910". Scenario 2: I want the web part to get the filter information from query string [PageQueryString:pageType]. So I tried using these options: a) pageType=Downloads      This works but returns downloads under 'projects' as well. (i want downloads only under 'articles')  b) pageType=Articles:Downloads     Does not work and shows no records c) pageType = Downloads|9096e43b-555d-4879-9acc-b4ada9ea9910     Does not work. (This value is the same as one found in 'FilterValue1' of the static web part). c) pageType = Articles:Downloads|9096e43b-555d-4879-9acc-b4ada9ea9910     Does not work. d) pageType = 9096e43b-555d-4879-9acc-b4ada9ea9910     Does not work.   What am I missing here? Can some one please help?

can an alias column be used in a WHERE clause

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

Wierd case of: Msg 213, Column name or number of supplied values does not match table definition

Hi, I'm working on several triggers (that happen after insert or update) in order to log the changes in a different table. They all follow a similar syntax and are working fine, except for this one... I've reduced the next code to the minimum that gives an error, so we can safely assume the other parts of the trigger are working fine. INSERT INTO [Adt].[WardUnitStayLog] SELECT t.* FROM [Adt].[WardUnitStay] t INNER JOIN inserted i ON i.[Id] = t.[Id]; I've used this same syntax (but on different tables) in other triggers, and these are working perfectly fine. The above query provides the next error: Column name or number of supplied values does not match table definition. I've checked both tables for differences in the columns, but to no avail... (I've checked them manually and by outerjoining the information_schema.columns) (I've also checked the order in wich these columns are defined, they match over the two tables) These are the creation scripts for the tables: CREATE TABLE [Adt].[WardUnitStay] ( [Id] [dbo].[Id] IDENTITY(1,1) NOT NULL, [UnifiedUnitStayId] [dbo].[Id] NOT NULL, [WardCd] [dbo].[Cd] NOT NULL, [_FirstAtTm] [dbo].[Dtm] NOT NULL, [_IsReservation] BIT NOT NULL, [_LastAtTm] [dbo].[Dtm] NOT NULL, [_LastBedCd] [dbo].[Cd] NULL, [_LastPhysicianUid]

How to copy column headers along with the query result in SS00

HiIs it possible to copy the column headers along with the query result from SQL Server 2000 Query Analyser.Thanks in advance.fjz

MDX Query tuning with subcubes usage

Hello, I've an SSAS DB with 8+ dimensions among which 4 are regular dimensions and 4 are parent-child dimensions. The problem is I've an MDX query whose execution is taking 45 seconds of time. Following is my MDX query : Select Non Empty(Hierarchize({{DrillDownLevel({{[PCDim1].&[716]}})}})) on Rows         ,Non Empty(Hierarchize({{Descendants({[TimeDim1].&[5045],[Time].&[TimeDim1]},[Time].[Month],SELF)}})) on Columns From MyCube where ({[PCDim2].&[101],[PCDim2].&[102]} *         ([PCDim3].&[1],[PCDim4].&[1],[RegDim1].&[104]),[RegDim2].&[1],[RegDim3].&[14],[Measures].[MTD]) I tried to speedup the query execution by using the subcubes. Following is the altered query:   Select Non Empty(Hierarchize({{DrillDownLevel({{[PCDim1].&[716]}})}})) on Rows         ,Non Empty(Hierarchize({{Descendants({[TimeDim1].&[5045],[Time].&[TimeDim1]},[Time].[Month],SELF)}})) on Columns From ( Select {[PCDim2].&[101],[PCDim2].&[102]} on Columns From MyCube ) where (([PCDim3].&[1],[PCDim4].&[1],[RegDim1].&[104]),[RegDim2].&[1],[RegDim3].&[14],[Measures].[MTD]) surprisingly this took more time to execute when compared with the earlier query execution time. I've read that using subcubes in MDX query would speedup the query

Schema comparer mistake in table-valued function (Nullability in column definition)

Hello. In my database i have table valued function. In its definition nullability of column is not set directly. This column has a user-defined data type. So the question is: is this column nullable? As i can see here: http://msdn.microsoft.com/en-us/library/ms174979.aspx   When column nullability is not explicitly specified, column nullability follows the rules shown in the following table. Column data type Rule Alias data type The Database Engine uses the nullability that is specified when the data type was created. To determine the default nullability of the data type, use sp_help. It should be not null, as user defined type is declared as not null. SMO says, that column is not null, but schema compare says it is nullable. Who is wrong and why?

How to enable delete column at Gridview when having specify query.

Hello:  I have trouble to add delete column to gridview when using specific query. I have PK set up at the table, I need add specific query to display data at gridview:   SELECT [PID], [GID], [TID], [SID], [NEWSID] FROM [TableOne] WHERE ([NAME] = ?)   After builder this query, I click advanced button. However, generate insert, update, and delete Statements is not enabled.   How to add delete column to this gridview?   Your help is highly appreciated.

SharePoint Dataview Insert New Mode - Defaulting a Required Column to a Query String Parameter Valu

I've got a SharePoint DataView with an insert button. there is a required lookup column I want to default to the value of Querystring Value during new mode.  Can I do this in the generated code below? I tried replacing @ApplicantId with @QSApplicantID.. and  tried string(@QSApplicantID) too.. no luck gave me an error: The data source control failed to execute the insert command. Could it be that QSApplicantID (which I've used in my DataView Datasource filter with no problem) is not available during the insert? I think I can do what I want in Javascript but was hoping I could instead right in the XSL markup. *my query string parameter**     ParameterBinding Name="QSApplicantID" Location="QueryString(ID)" DefaultValue="2222222"/ **the new form** SharePoint:FormField runat="server" id="ff4{$Pos}" ControlMode="New" FieldName="ApplicantId" __designer:bind="{ddwrt:DataBind('i',concat('ff4',$Pos),'Value','ValueChanged','ID',ddwrt:EscapeDelims(string(@ID)),'@ApplicantId')}" / **the link** a href="javascript: {ddwrt:GenFireServerEvent('__cancel;dvt_1_form_insertmode={1}')}">Insert</a Thanks for any help or information!

Adding a new column in update query


Hi all,


Is it possible to add a new column in an update query? I am joing two tables  with [Encountered Month] from Table 1 and [Calendar Date] from table 2 and trying to add one column[Fiscal Month] into the first table where  dbo.table1.[Encountered Month]=dbo.table2.[Calendar Date]

Appreciate your help.

retrieving column names using sql query


hi All
 I have A database in access2007

I want retrieve column names with sql query in my project
I write below code:
"select column_name from information_schema.columns where table_name ='table 1' ORDER BY ORDINAL_POSITION"

but display error :
Could not find file 'D:\Projects\Tapco_ECM\Tapco_ECM\Tapco_ECM\bin\Debug\information_schema.mdb'.

plz help me

Pivot table query dynamic column.


Hello All,


I want to export my data to excel sheet. The logic to export data in excel is clear.

Now my query is I have 3 tables and I want to generate the data jst like below.



Company Name

Company Email


Create Date


Fax No














And so on.....


The data will come from 3 tables I.e


  1. Master Table for Company

  2. Get information about collation of a column of a resultant select query using oledb



    I have executed a select query from a table using OLEDB (IID_ICommandText) . Now I want the information about collation(Case Sensitivity,Accent Sensitivity, Kana Sensetivity, Width Sensetivity, LCID) of all the eligible columns of a select query. Can anyone please help me with a way to retrieve all of them.



Two column Publishing page layout query


Hello I'm quite new to use sharepoint 2007 designer. I've set up a two column liquid publishing page layout template using two tables side by side. A total of 100% basically the left table is set to 70% and the right to 30%.

It works fine when used, however when no webparts or content are entered in the right column (table) the layout looks a bit strange since it the right column space is empty.

Is it possible for the left table to automatically expand to 100% if nothing is entered in the right table?

Thank you for any help that can be provided.

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