Column prefix doesnt match with a table name error

I keep getting an error of Column prefix " t" doesnt match with a table
name or alias name with the query with this sql query (part of a c#
script):
"SELECT
d.SalesRep_id,s.fname,s.lname,s.Total_BonusPerc,s.ID,s.Hire_date,s.Term_date
,
d.ddate,d.salesOff_loc_ID,d.SplitGross,
d.SplitRep_ID,d.Sale_Type_ID,d.DBA_ID,c.ID,t.Amount_Revenue,t.MGTag_ID
FROM tblBankTran as t,tblDeal as d INNER JOIN tblSalesrep as s ON s.ID
= d.SalesRep_id INNER JOIN tblCustTag as c on c.ID = t.MGTag_ID WHERE
d.salesOff_loc_ID = " + 3 + "";
I'm trying to include the tblBankTran table and it's Amount_Revenue,
which the tblBanktran table field MGTag_ID is related to tblCustTag ID
field.
Thanks in advance
.NetSportsHi
This is invalid syntax
FROM tblBankTran as t,tblDeal as d INNER JOIN
Instead , FROM tblBankTran t INNER JOIN tblDeal s ON....
For details please refer to the BOL
".Net Sports" <ballz2wall@.cox.net> wrote in message
news:1118099682.466063.5530@.g43g2000cwa.googlegroups.com...
> I keep getting an error of Column prefix " t" doesnt match with a table
> name or alias name with the query with this sql query (part of a c#
> script):
> "SELECT
>
d.SalesRep_id,s.fname,s.lname,s.Total_BonusPerc,s.ID,s.Hire_date,s.Term_date
,
> d.ddate,d.salesOff_loc_ID,d.SplitGross,
> d.SplitRep_ID,d.Sale_Type_ID,d.DBA_ID,c.ID,t.Amount_Revenue,t.MGTag_ID
> FROM tblBankTran as t,tblDeal as d INNER JOIN tblSalesrep as s ON s.ID
> = d.SalesRep_id INNER JOIN tblCustTag as c on c.ID = t.MGTag_ID WHERE
> d.salesOff_loc_ID = " + 3 + "";
> I'm trying to include the tblBankTran table and it's Amount_Revenue,
> which the tblBanktran table field MGTag_ID is related to tblCustTag ID
> field.
> Thanks in advance
> .NetSports
>

column name alias concatenation

I have a web application where I would like to return a dynamic column name using aliasing. below is an example:

select hours as 'Fri<BR>' + cast(Day(getDate()) as varchar(2)) from todayshours

I get an error trying to do concatenation as part of the alais. Any ideas?

Luke
lgraunke AT 4invie.comWhats <BR>

Is this being done in SQL Server?|||Ideally I would like the column name/header to show something like 'Fri<BR>20'. The '<BR>' is just some web formating that is automatically incorporated.|||This should float your boat...

USE Northwind
GO

DECLARE @.cmd varchar(8000)

SELECT @.cmd = 'SELECT Quantity AS ['
+ CASE DATEPART(WeekDay,GetDate())
WHEN 1 THEN 'SUNDAY'
WHEN 2 THEN 'MONDAY'
WHEN 3 THEN 'TUESDAY'
WHEN 4 THEN 'WEDNESDAY'
WHEN 5 THEN 'THURSDAY'
WHEN 6 THEN 'FRIDAY'
WHEN 7 THEN 'SATURDAY'
END
+ '<BR>'
+ cast(Day(getDate()) as varchar(2))
+ '] FROM [Order Details]'

SELECT @.cmd

EXEC(@.cmd)|||Thanks, that was exactly what I was looking for.

column alias in where clause

Hi, I'm trying to migrate a database from Sybase to SQL Server. As far as I
understand (I'm not a db exert), it is not possible in SQL Server to
reference an alias for a column name that is defined in the select-clause in
the where clause. With Sybase this worked, so this kind of thing is widely
spread through the code. Just before I'm going to change all these alias
names to the actual column names: does someone know a way to avoid this,
which means using aliases in where clauses? While I'm at it: could it be the
case that 'IF' statements are not allowed in the select-clause?
Thanx in advance for all replies,
Martin
"Martin" schrieb:
> Hi, I'm trying to migrate a database from Sybase to SQL Server. As far as I
> understand (I'm not a db exert), it is not possible in SQL Server to
> reference an alias for a column name that is defined in the select-clause in
> the where clause. With Sybase this worked, so this kind of thing is widely
> spread through the code. Just before I'm going to change all these alias
> names to the actual column names: does someone know a way to avoid this,
> which means using aliases in where clauses? While I'm at it: could it be the
> case that 'IF' statements are not allowed in the select-clause?
> Thanx in advance for all replies,
> Martin
1. Aliases from the select list work in the where clause!
2. No 'if' in select statements, but 'case' does the trick ...
|||maybe I'm just doing it the wrong way, then (maybe the 'AS' is not really
what's meant by aliasing). But when I try:
SELECT My_ID, My_Some AS SomeAlias
FROM My_Table
WHERE (SomeAlias = 'a')
I get an "invalid colum name" error. I'll give the CASE a try, thanks!
"Christian Donner" wrote:

> "Martin" schrieb:
> 1. Aliases from the select list work in the where clause!
> 2. No 'if' in select statements, but 'case' does the trick ...
|||"Martin" schrieb:

> maybe I'm just doing it the wrong way, then (maybe the 'AS' is not really
> what's meant by aliasing). But when I try:
> SELECT My_ID, My_Some AS SomeAlias
> FROM My_Table
> WHERE (SomeAlias = 'a')
> I get an "invalid colum name" error. I'll give the CASE a try, thanks!
I'm terribly sorry! I mistook 'where' and 'order by' (I hope I will not be
banned from this forum for stupidity). Where clauses do NOT support aliases.
Sorry again for this blunder ...
|||Ok, so I have to rename those. Never mind the mix-up, after all, I got a
'positive' "It can't be done that way!"
"Christian Donner" wrote:

> "Martin" schrieb:
>
> I'm terribly sorry! I mistook 'where' and 'order by' (I hope I will not be
> banned from this forum for stupidity). Where clauses do NOT support aliases.
> Sorry again for this blunder ...
|||On Tue, 14 Jun 2005 02:29:01 -0700, Martin wrote:

>Hi, I'm trying to migrate a database from Sybase to SQL Server. As far as I
>understand (I'm not a db exert), it is not possible in SQL Server to
>reference an alias for a column name that is defined in the select-clause in
>the where clause. With Sybase this worked, so this kind of thing is widely
>spread through the code. Just before I'm going to change all these alias
>names to the actual column names: does someone know a way to avoid this,
>which means using aliases in where clauses? While I'm at it: could it be the
>case that 'IF' statements are not allowed in the select-clause?
>Thanx in advance for all replies,
>Martin
Hi Martin,
You can't use a column alias in the WHERE clause (or any other part of
the query, except ORDER BY). However, there is a workaround that uses
derived tables. This can be especially useful if a column in the SELECT
clause is defined using a lengthy expression and you don't feel like
repeating the expression. Simple example:
SELECT der.Expr1, der.Expr2,
der.Expr1 + der.Expr2,
der.Expr1 * der.Expr2
FROM (SELECT (insert lengtht expression here) AS Expr1,
(put another long expression here) AS Expr2
FROM MyTable
WHERE Col1 + Col2 < Col3) AS der
WHERE der.Expr1 BETWEEN der.Expr2 AND der.Expr2 * 2
AND der.Expr2 <> 0
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

column alias in where clause

Hi all,
I have the following query that uses a subquery for one of the columns.
However if I try to use this subquery column in the where clause with the
alias I've given it, it tells me it is an invalid column. Here's the query:
select a.*,(select companyID from contacts where contactID = a.contactID) as
compID
from appointment a
where compID in(select companyID from tsClientUsers where UserID=5) or
compID is null
It keeps giving me the error that compID is an invalid column name. why? And
how can I get around this? I cannot do an inner join because not all
appointments have a related contact.
Any help would be much appreciated!
--
SaraSara, try this:
select * from
(
select a.*,(select top 1 /*just in case when subquery returns more than one
value*/companyID from contacts where contactID = a.contactID) as
compID
from appointment a )p
where compID in(select companyID from tsClientUsers where UserID=5) or
compID is null
Regards,
Tomislav Kralj
"Sara" <s@.mail.com> wrote in message
news:E5241E9D-47B0-40F5-AD0D-CA8B06347755@.microsoft.com...
> Hi all,
> I have the following query that uses a subquery for one of the columns.
> However if I try to use this subquery column in the where clause with the
> alias I've given it, it tells me it is an invalid column. Here's the
> query:
> select a.*,(select companyID from contacts where contactID = a.contactID)
> as
> compID
> from appointment a
> where compID in(select companyID from tsClientUsers where UserID=5) or
> compID is null
> It keeps giving me the error that compID is an invalid column name. why?
> And
> how can I get around this? I cannot do an inner join because not all
> appointments have a related contact.
> Any help would be much appreciated!
> --
> Sara|||The WHERE clause has no idea about the alias, because it is processed long
before the expressions in your SELECT list. So, in most cases, you will
either have to repeat the expression in the WHERE clause, or use a subquery
like Tomislav suggests.
However in this case I believe it is possible to avoid all those subqueries
and use a LEFT OUTER JOIN:
DECLARE @.userID INT
SET @.userID = 5
SELECT a.*, c.companyID
FROM appointment a
LEFT OUTER JOIN Contacts c
ON a.contactID = c.contactID
LEFT OUTER JOIN tsClientUsers t
ON a.compID = t.compID
WHERE t.userID = @.userID
-- you may also try AND instead of WHERE here
If you provide DDL, sample data and desired results (see
http://www.aspfaq.com/5006), I might even be able to verify that my above
solution works in your case.
On 3/15/05 3:15 AM, in article
E5241E9D-47B0-40F5-AD0D-CA8B06347755@.microsoft.com, "Sara" <s@.mail.com>
wrote:

> Hi all,
> I have the following query that uses a subquery for one of the columns.
> However if I try to use this subquery column in the where clause with the
> alias I've given it, it tells me it is an invalid column. Here's the query
:
> select a.*,(select companyID from contacts where contactID = a.contactID)
as
> compID
> from appointment a
> where compID in(select companyID from tsClientUsers where UserID=5) or
> compID is null
> It keeps giving me the error that compID is an invalid column name. why? A
nd
> how can I get around this? I cannot do an inner join because not all
> appointments have a related contact.
> Any help would be much appreciated!|||On Tue, 15 Mar 2005 00:15:03 -0800, Sara wrote:
(snip)
>It keeps giving me the error that compID is an invalid column name. why? And[/color
]
Hi Sara,
That's a result of the defined order of evaluation for SELECT
statements. In theory, the order is:
1. Build temp result set from FROM clause (including all joined tables).
2. Remove rows not satisfying the WHERE clause
3. Create groups, according to GROUP BY clause
4. Remove all rows from groups not satisfing the HAVING clause
5. Build final result set, as specified in SELECT clause, based on data
in temp result set.
(6. Reorder the result set, based on ORDER BY - technically, this is a
cursor operation and not part of the SELECT statement).
(In practice, most products will change the order of evaluation for
optimization, as long as the results don't change)
As you can see, the building of the SELECT clause is the final step;
that's the reason that aliasses can't be used in the rest of the query
(though they can be used in the ORDER BY).
>how can I get around this? I cannot do an inner join because not all
>appointments have a related contact.
You could use a derived table (as suggested by Tomislav), but in this
case I believe there's a better solution:
SELECT a.Col1, a.Col2, ..., a.ColN, -- Never use SELECT * !!
c.CompanyID AS compID
FROM Appointments AS a
LEFT OUTER JOIN Contacts AS c
ON c.ContactID = a.ContactID
WHERE c.CompanyID IN (SELECT CompanyID
FROM tsClientUsers
WHERE UserID = 5)
OR c.CompanyID IS NULL
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Column Alias in views

Hi All,
I am currently transferring my Access application to SQL Server. Access allows you to declare and use aliases in the query at the same time.

e.g.
Select field1 as Alias1, field2 as Alias2, Alias1 & " " & Alias2 as Alias3 from table1;

In Access the above query will execute perfectly, no problem. However in SQL Server, if you try to run the same query it will give an error "Invalid column name Alias1" meaning that SQL Server is searching for Alias1 as a field in the table, not as an alias from the query.

My question is does SQL Server have a facility to declare and use alias directly as in Access and if no, is there a workaround?

Thanks for your time.

Regards:
Prathmeshhi

try this

Select field1 as Alias1, field2 as Alias2, field1 + ' ' + field2 as Alias3 from table1;

hope this will solve ur problem|||Hi,
Ok, I think I need to explain a bit more detail. I have got a database table that stores data about different equipments. Each equipment is identified by 3 distinct fields Area, Type, No. So a particular equipment tag would be of type:
Area+Type+No.

Now at the same time the table also holds the description of the equipment which comes from 2 fields desc1 and desc2. So the whole equimment desc would be desc1+desc2

Now on the reports the equiptag and equipment desc need to be concatenated to form one equipment number i.e. Area+type+No+Desc1+desc2

So what I wanted to do was
Select Area+type+No as Equiptag, Desc1+Desc2 As EquipDesc, EquipTag+EquipDesc As EquipNo from equipment;

but obviously SQL Server will give an error of invalid column for "EquipTag" and "EquipDesc"

So, Is there any way to do this?

Regards:
Prathmesh|||Hi,

So what I wanted to do was

Select
Area+type+No as Equiptag,
Desc1+Desc2 As EquipDesc,
EquipTag+EquipDesc As EquipNo
from
equipment;

but obviously SQL Server will give an error of invalid column for "EquipTag" and "EquipDesc"

So, Is there any way to do this?

To the best of my knowledge, you can't use an alias as part of a formula within the same SQL. You would either have to do this:

Select
Area+type+No as Equiptag,
Desc1+Desc2 As EquipDesc,
Area+type+No+Desc1+Desc2 As EquipNo
from
equipment;

or you could try creating a subquery like this:

SELECT
t.EquipTag,
t.EquipDesc,
t.EquipTag+t.EquipDesc As EquipNo
FROM
(SELECT
Area+type+No as Equiptag,
Desc1+Desc2 As EquipDesc
FROM
equipment) t

Regards,

hmscott|||Thanks hmscott,
The subquery idea is a good one. I'll give it a try. I was just curious if this could be done similar to Access or not. I must say, being an Access programmer, there are certain things in SQL Server which really annoy you. Most of my queries use this type of aliasing, so I now have to go and rewrite them to replace Aliasing.

Another thing is the "concat null yields null" thing. When you concat 2 strings and one is null, the returned string is Null. Huh!!! Why? I think this is totally stupid. In Access, this is not at all a problem. It just discards the nulls, and returns the concatenated string without nulls. Well I guess this is typical Microsoft behaviour. I tried executing the stored procedure to set the concat null yeidls null to false, but it does not work. I cannot figure out why. A similar question was posted in this forum asking why it does not work, but nobody was able to answer. If anybody has got any suggestions, please do let me know.

Thanks.

Regards:
Prathmesh|||All databases are different. All databases have things that are worse than other databases or extra things that are better than other databases. There is no reason. What is included in the SQL Standard should be the same accross databases but for anything else ...|||hi Prathmesh,

try this

SELECT ISNULL(columnwithnull,'') + nonnullcolumns from yourtable|||Hi baburaj,
Yep, that is what I am using now. However, I have decided on something else. I am planning to use SQL Server backend to Access frontend, because all my forms , reports, etc. are in Access.I am going to do all the complex join queries on SQL Server side as views and link the tables via odbc to Access using the Access "link tables" facility and the required formatting I will still do on Access side. This way I can have best of both worlds. I can make use of SQL server's performance and Access' formatting features.

Thanks to all for your help and suggestion guys.|||Another thing is the "concat null yields null" thing. When you concat 2 strings and one is null, the returned string is Null. Huh!!! Why? I think this is totally stupid. In Access, this is not at all a problem. It just discards the nulls, and returns the concatenated string without nulls. Well I guess this is typical Microsoft behaviour. I tried executing the stored procedure to set the concat null yeidls null to false, but it does not work. I cannot figure out why. A similar question was posted in this forum asking why it does not work, but nobody was able to answer. If anybody has got any suggestions, please do let me know.
Not entirely true - Access also provides the "+" concatenation operator where Null + "Something" = Null.
Rather than thinking of it as a bind you need to think through the implications. The + operator is great, for example, when putting together a csv address string for presentation - you don't need to use a load of conditional statements to exclude the comma if, for example, the address has no House Name.

column alias in select?

I would like to use the value calculated in one column in another colum by using it's alias. However, when I try that, I get an 'Invalid Column Name' error.

for example,

SELECT
sum(Price) as 'Sum_Price',
('Sum_Price' / 3) as 'One_Third_Sum_Price'The SELECT clause can only refer to columns from tables in the FROM clause, so that won't work. Of course, you can do this:

SELECT
sum(Price) as 'Sum_Price',
sum(Price) / 3 as 'One_Third_Sum_Price'

Or you can do this:

SELECT
Sum_Price
Sum_Price / 3 as One_Third_Sum_Price
FROM
( SELECT sum(Price) as 'Sum_Price'
FROM ...
)

Column alias in select statement - efficient?

Is a column alias in a "select" statement more efficient? (statement
parsing etc...)
Thanks, gert>> Is a column alias in a "select" statement more efficient?
Probably not, since there is hardly any evidence to support it.
Anith

Column Alias Behavior

I don't know why I never noticed this before, but in the code below, why can
I ORDER BY an aliased column, but I have to use the *actual* column name or
expression in the GROUP BY?
I would really rather say "GROUP BY Column1, Column2"
CREATE TABLE msl_T1 (
col1 INT,
col2 INT,
col3 INT
)
GO
SELECT col1 AS Column1, col2 * 2 AS Column2, MIN(col3) AS Column3
FROM msl_T1
GROUP BY col1, col2 * 2 -- RIGHT HERE!!
ORDER BY Column3
DROP TABLE msl_T1
GO
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave MustaneYou can use column aliases in the ORDER BY, but nowhere else. You can use a
derived table to circumvent this:
SELECT Column1, Column2, min (col3) Column3
FROM
(
SELECT col1 AS Column1, col2 * 2 AS Column2, col3
FROM msl_T1
) X
GROUP BY Column1, Column2
ORDER BY Column3
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada tom@.cips.ca
www.pinpub.com
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eMlNTrE9FHA.4084@.TK2MSFTNGP10.phx.gbl...
>I don't know why I never noticed this before, but in the code below, why
>can I ORDER BY an aliased column, but I have to use the *actual* column
>name or expression in the GROUP BY?
> I would really rather say "GROUP BY Column1, Column2"
> CREATE TABLE msl_T1 (
> col1 INT,
> col2 INT,
> col3 INT
> )
> GO
> SELECT col1 AS Column1, col2 * 2 AS Column2, MIN(col3) AS Column3
> FROM msl_T1
> GROUP BY col1, col2 * 2 -- RIGHT HERE!!
> ORDER BY Column3
> DROP TABLE msl_T1
> GO
>
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>|||> You can use column aliases in the ORDER BY, but nowhere else.
HMPH!! So much for "consistency". As Bill the Cat so eloquently put it,
"PTHPTHPTPTH" :-)
Peace & happy computing,
Mike Labosh, MCSD
"When you kill a man, you're a murderer.
Kill many, and you're a conqueror.
Kill them all and you're a god." -- Dave Mustane|||It's not a question of consistency.
5aa6fc669a8" target="_blank">http://groups.google.ca/group/comp.../>
5aa6fc669a8
and read Joe Celko's explanation on Select.
"Mike Labosh" <mlabosh@.hotmail.com> wrote in message
news:eWwqf5E9FHA.2676@.TK2MSFTNGP15.phx.gbl...
> HMPH!! So much for "consistency". As Bill the Cat so eloquently put it,
> "PTHPTHPTPTH" :-)
> --
> Peace & happy computing,
> Mike Labosh, MCSD
> "When you kill a man, you're a murderer.
> Kill many, and you're a conqueror.
> Kill them all and you're a god." -- Dave Mustane
>

column alias as variable

Is there a way to select a column as an alias using a variable for the alias? something like this:

SELECT Column1 as @.myVariable FROM Table1

Not directly in a SELECT statement. You can use dynamic SQL like:

set @.sql = N'SELECT Column1 as ' + quotename(@.myVariable) + N' FROM Table1'

exec sp_executesql @.sql

But it is not going to be pretty if you want to do this for multiple columns and dynamic SQL has security implications/management issues. Why do you want to do this? How will the client handle this if the column names can be modified arbitrarily? One way to do this is to fix the column names as c1, c2, c3 etc and have a separate result set or metadata that contains the user friendly names for c1, c2, c3 respectively.

|||

The user enters a period length such as 30 days and the report displays the period date ranges as the coulmn name of a pivot.

like this:

Location 1-30 31-60 61-90

Texas 10 3 2

Florida 5 8 7

Column Alias

Hello everyone.
I was wondering if there is a way that you can set the alias name of a column to a value that resides in another table instead of the alias being a static value that you type in.
If anyone has any ideas on ho i can accomplish this i would greatly appreciate it.I don't know if you would be able to do it but I have 2 ideas that might work.

1. do some kind of sub query which would return the alias name

2. write a stored procedure in which you could write a query to get the alias name, assign that to a variable, and then use the variable as the alias in the next query.

I have no idea if either one of these will work, but they are just some ideas.
I hope this helped
Good luck|||You can do this with dynamic SQL.

As a matter of fact, you can do also sorts of stupid things with dynamic SQL. And many people take up the challenge.|||Thanks for the suggestions. I am doing a little research to see how i can accomplish this using dynamix sql statements.|||And the challenge has been accepted.|||Is there a problem with aliasing the column in a view?|||And the challenge has been accepted.

Oh great. Are you gonna pay for my new keyboard (after I just spit my coke all over it)?

Regards,

hmscott|||First of all, if you just sniffed the coke, how on earth did you end up spitting it onto your keyboard? Its not really any of my business I guess, but inquiring minds want to know.

More importantly, I saw this one coming a long way off, and I'm usually the optimist in this crowd... Why should Blindman buy you new coke when you ought to have expected this quite a while back?

-PatP|||Coke? Keyboard? hmscott?

OK, I'm lost now.

The poster is (against advice) taking the "dynamic sql challenge". You appear to be confusing this with the "Pepsi Challenge".|||Just to clarify, I do not support dynamic sql statements, but it seemed like the only way to achieve the goal. If anybody has a better idea I am all ears.|||To paraphrase Slim Pickens from BLAZING SADDLES ...Why in the wide, wide world of sports would you want to do that?|||In this case, it would be more appropriate to paraphrase Slim Pickens whooping and hollering as he rides the nuclear warhead down to destruction in "Dr. Strangelove".|||well i know you can also update the column name in the syscolumn table.

I would have to use:

sp_configure 'allow updates', 1
GO
RECONFIGURE WITH OVERRIDE
GO

This will allow me to use ad hoc commands to rename table. But i am not sure if allowing updates on the system tables would be an approach to take.|||Modifying system tables beats dynamic SQL every time when you are looking to create self-inflicted injuries!

-PatP|||But of course, modifying system tables WITH dynamic sql would be like the biathalon of bad design!|||trifecta if you do it in a stored proc with a cursor|||trifecta if you do it in a stored proc with a cursorOh ouch!

-PatP|||there is a much easier way of doing this if the alias names are again column names of some other table and the datatype matches. is that the case?|||Assume it is. You've piqued my curiosity. What are you proposing?|||Ummm...Is there a problem with aliasing the column in a view?|||Assume it is. You've piqued my curiosity. What are you proposing?


select top 0 * from TableHavingAliasColName
union
select * from TableWithDiffColName

OR

select top 0 * into Dummy from TableHavingAliasColName
insert into Dummy select * from TableWithDiffColName|||Ah. So the table schemas have to be identical.
But the poster wants to set the column name to the VALUE in another table. Not the column name from another table.
Very creative, though.

Column alias

Can we have more than one column alias for a sinle column in the select
list.
Something like this
SELECT field1 A,B FROM Table
What for do you need it ? (BTW, columns can only have one Alias)
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
"Gopinath S" <gopinath_s@.nospamtrigent.com> schrieb im Newsbeitrag
news:OAzset5WFHA.580@.TK2MSFTNGP15.phx.gbl...
> Can we have more than one column alias for a sinle column in the select
> list.
> Something like this
> SELECT field1 A,B FROM Table
|||Jens Smeyer wrote:
> What for do you need it ? (BTW, columns can only have one Alias)
>
Same stored procedure is being used by two different reports which
shoud have different aliases.
Thankx
Done a work around
|||You can use like this....
select ContactName as Name, ContactName as SecondName from Customers
thanks
"Gopinath S" <gopinath_s@.nospamtrigent.com> wrote in message
news:#nqI175WFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Jens Smeyer wrote:
> Same stored procedure is being used by two different reports which
> shoud have different aliases.
> Thankx
> Done a work around
|||Hi,
No need to have different alias. You can very well use the same alias for
both the reports.
Thanks
Hari
SQL Server MVP
"Gopinath S" <gopinath_s@.nospamtrigent.com> wrote in message
news:%23nqI175WFHA.3184@.TK2MSFTNGP15.phx.gbl...
> Jens Smeyer wrote:
> Same stored procedure is being used by two different reports which shoud
> have different aliases.
> Thankx
> Done a work around

Colum Name - Alias

We are thinking of using general purpose column names in our application schema. We want to give the option to the end user to customize the filed names to fit their business . We want to build the functionality on the generic names so that it will work for multiple customes.

Example: We may want to have 10 Strings, 10 numbers and 5 booleans pre defined and reports running off of the table. The customer can name first number as pressure, second one for length and map their data to the table. Other customer can use the first number for temperature and the second one for width.

Is there a way to do it in SQL server w/o having a lookup table for column name aliasing?

Thanks

Option 1: Any Reporting application generally has provision to display a customized column names for the table reports.

Option 2: When you query the table - you can provide column alias for the columns queried for example:

SELECT Column1 AS Pressure, Column2 AS Length FROM TableName

Option 3: You can create multiple views over the base table and the created views can have appropriate column names.

Thanks,

Sankaranarayanan MG