Using Alias In A View

Jan 30, 2007

Hi

How (if i can) can i use a alias in my view
if i have 1 alias total and a alias Moms

can't i then have a nother alias there say total+moms?

Or shall i write all again in another alias

Alvin

View 5 Replies


ADVERTISEMENT

Get Column Alias From View AND Phisical Attribute

May 27, 2008

Hello Everyone,

I am creating a Metadata management application for a business intelligence platform on SQL Server 2005.

For this purpose, I've set out to consolidate all DB-object metadata (Databases, Tables, Columns, Views) into a single repository that business users can browse through. It has been fairly straightforward so far, but I've hit a wall in the views department.

In fairly simple terms, I want to know which physical columns are selected in a view, with the twist of also knowing the columns' alias. This seems to be impossible as far as I can tell, it is easy enough to get both of these independently, but I can't figure out how to connect an alias to an actual column.

(I prefer to use the metadata catalog views to the INFORMATION_SCHEMA as I need to know the object_id's)

Aliases are easy enough to obtain:

select object_id, column_id, name
from sys.columns
where object_id = object_id('SomeViewsName');


As are the 'physical' columns:
SELECT
t.object_id as TABLE_OID,
c.column_id as COLUMN_OID,
c.name
FROM
sys.views v
JOIN sys.sql_dependencies d
ON d.object_id = v.object_id
JOIN .sys.objects t
ON t.object_id = d.referenced_major_id
JOIN sys.columns c
ON c.object_id = d.referenced_major_id
AND c.column_id = d.referenced_minor_id
WHERE
d.class < 2 AND
v.name = 'SomeViewsName';


As I've said before, the problem is joining these two datasets. One of the problems associated with this is that the latter query also returns columns used in JOIN statements, even if they are not projected in the select part of the view definition.

Ultimately I'd like to get this result:

Alias used in View, physical table's object id, physical column's id

Am I missing something?

Thank you very much in advance for your help.

View 3 Replies View Related

Fetch Metadata From A Column With An Alias In A View

Aug 2, 2007

Hi!

I have created a view and one of the columns in the view has an alias assigned to it.

I'm able to read the metadata from INFORMATION_SCHEMA.VIEW_COLUMN_USAGE and also lookup
from which table each column in the view orginated from except for the column that has an alias assigned to it.

Is there any other way to lookup a column that has an alias assigned to it?


Thanks alot!

Adam

View 2 Replies View Related

How To View The Alias Datatypes And Their Properties In A Database

Dec 6, 2007

I have added alias datatypes using sp_addtype and would like to view the properties defined in it.
please let me knowhow to view the properties of them and also is there a way to list all alias datatypes existing in the current database along with their properties.

View 1 Replies View Related

Can Create Alias Server1DBInstance1 On Server3DBInstance3 And Assign Objects To That Alias

Nov 24, 2015

We will be moving 2 different databases (SS2005 & SS2008) to a new SS2014 SQL Server.  Currently our codes looks something like Server1DBInstance1... & Server2DBInstance2... Is it possible to move the objects from these 2 instances to Server3DBInstance3 and then use an alias to reference the objects?  Or does Server3 need to have DBInstance1 & DBIstance2?  Basically, is the alias just for the database or for the instance too?  Can I create an alias "Server1DBInstance1' on Server3DBInstance3 and assign objects to that alias?

View 16 Replies View Related

Dbo Alias

Mar 30, 2000

We have an alias for dbo in various code areas, development/test/production. It was easy to tell the DBO alias
on release 6.5 but now on 7.0, the only way I can figure out if the userid is aliased to dbo is to sign on as the userid
and query 'select user_name( )' and if it returns dbo, I have the answer. Is there an easier way to do this by looking in a
system catalog or is there something else I am missing? Thanks in advance. Karen Suenram

View 7 Replies View Related

DBO ALIAS?

Jul 1, 1999

In 6.5 you could alias someone as dbo, thus preventing an ownership chain break. In 7.0 there are roles, one of which is db_owner. I gave a user db_owner role, and they created a table. But no one else can see it? And he is listed as the owner in EM. How do I more effectivly mimic the dbo alias available in 6.5?

View 1 Replies View Related

Having With Alias?

Jun 25, 2007

Hi All,

I am trying to filter my result with HAVING clause. vchEditorName is an alias which I get in the result as a column (it gives me correct values). I want to select only those records which have the value in this field like 'akash'


sql Code:






Original
- sql Code




SELECT a.intArticleId, a.intEditorId, a.enumEditorType, a.vchArticleTitle, a.enumStatus, am.vchFirstName, u.vchScreenName,
Case a.enumEditorType
WHEN 'A' THEN am.vchFirstName
WHEN 'M' THEN u.vchScreenName
END as vchEditorName
FROM tblArticles a
LEFT OUTER JOIN tblLogin u ON a.intEditorId = u.intUserID
LEFT OUTER JOIN tblAdminMaster am ON a.intEditorId = am.intAdminID

WHERE a.enumStatus='A'
GROUP BY a.intArticleId, a.intEditorId, a.enumEditorType, a.vchArticleTitle, a.enumStatus, am.vchFirstName, u.vchScreenName
HAVING vchEditorName LIKE '%akash%'






SELECT a.intArticleId, a.intEditorId, a.enumEditorType, a.vchArticleTitle, a.enumStatus, am.vchFirstName, u.vchScreenName,     CASE a.enumEditorType         WHEN 'A' THEN am.vchFirstName         WHEN 'M' THEN u.vchScreenName     END  AS vchEditorNameFROM  tblArticles a LEFT OUTER JOIN tblLogin u ON a.intEditorId = u.intUserID LEFT OUTER JOIN tblAdminMaster am ON a.intEditorId = am.intAdminID          WHERE a.enumStatus='A' GROUP BY a.intArticleId, a.intEditorId, a.enumEditorType, a.vchArticleTitle, a.enumStatus,  am.vchFirstName, u.vchScreenNameHAVING vchEditorName LIKE '%akash%' 

View 5 Replies View Related

Alias

Apr 21, 2008

I want an alias instead of display the column name for the below query..

select STE = case when STE ='AT'then'01'
when STE ='AT1'then'02'
......
.......
.......
........
else STE
end
from tableinfo

Output for the query

STE
---
01
02

Desired output:

STEDN
-----
01
02

Thanks for the help in advance !!!

View 4 Replies View Related

Alias

Jun 2, 2008

Hi All,
I've just installed an active/active sql cluster. Both are named instance hence if i need to connect to it, I need to use NODE1SQLINST1 and NODE2SQLINST2. Is there a way to just use NODE1 or any other single name to connect to the sql instance? I was looking at Alias but I can't get it working. Anyone has any ideas or suggestion for me? Reason why i want to do this is in an odbc connection, instead of using NODE1SQLINST1, I can use NODE1.
Thanks

Regards
Ken

View 6 Replies View Related

Anybody Pls Help Me!!!!!!!!! About Alias T.y.

Aug 15, 2007

pls someone help me about adding alias column or column alias

here's my code

select

(select count(*)
from dto_client_dtl
where dto_client_dtl.testStage is null or (dto_client_dtl.testStage = 'INI' and dto_client_dtl.testResult = 'POS')) as 'NoOfPendingTransaction',
(select count(*)
from dto_client_dtl
where dto_client_dtl.testStage in ('CHM','CHA') and dto_client_dtl.testResult = 'POS') as 'NoOfConfirmedPositive',
(select count(*)
from dto_client_dtl
where dto_client_dtl.testStage in ('CHM','CHA') and dto_client_dtl.testResult = 'NEG') as 'NoOfConfirmedNegative',
(select count(*)
from dto_client_dtl
where dto_client_dtl.testStage is not null and dto_client_dtl.testResult = 'POS') as 'TotalNoOfScreeningPositive',
(select count(*)
from dto_client_dtl
where dto_client_dtl.testStage = 'INI' and dto_client_dtl.testResult = 'NEG') as 'TotalNoOfScreeningNegative',
(select count(*)
from dto_client_dtl
where dto_client_dtl.tranStat in ('CCn','TCN')) as 'TotalNoOfCancelledTxns',
('NoOfPendingTransaction'+'TotalNoOfScreeningPositive'+'TotalNoOfScreeningNegative') as 'SubTotal'

from dto_client_dtl

when i run this code the output is

Truncated incorrect DOUBLE value: 'NoOfPendingTransaction'
Truncated incorrect DOUBLE value: 'TotalNoOfScreeningPositive'
Truncated incorrect DOUBLE value: 'TotalNoOfScreeningNegative'

View 2 Replies View Related

Alias

Oct 5, 2006

hi. i face a problem . i cant reference an alias and have to copy and paste code again. how can i ? see "Computed Total", i had to copy its code again.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[Select_Bill_]

@payment_or_bill nvarchar(2),

@spcode nvarchar(25)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @fieldname varchar(10) DECLARE @stmt varchar(4000)

DECLARE Fields CURSOR FOR

SELECT Amounttype FROM BILL_Amounttypes

SET @stmt = 'select billid,Date_,Suppliercode,billtype,typecode,payment_or_bill,roe,currency'

OPEN Fields

FETCH Next FROM Fields INTO @fieldname

WHILE @@Fetch_Status = 0

BEGIN

SET @stmt = @stmt + ', (select billid_detailed from

bill_Detailed where billid = bill1.billid and

amounttype = ''' + @fieldname + ''''

SET @stmt = @stmt + ') As ' + @fieldname

FETCH Next

FROM Fields

INTO @fieldname END

CLOSE Fields

DEALLOCATE Fields

SET @stmt = @stmt + ',(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end)

from bill_detailed where billid = bill1.billid ) as "Computed Total",Total'

SET @stmt = @stmt + ',(Select case when

(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end ) from bill_detailed where billid = bill1.billid ) =

Total then ''Yes'' else ''No'' end ) as Processed'

SET @stmt = @stmt + ' From bill_ as bill1'

exec(@stmt)

select (@stmt)

END

View 5 Replies View Related

Alias OK, IP Not

Dec 11, 2006

Hi all,



I was able to get my mirroring setup to work only when I use Alias instead of IP address. Any idea why it is so?



Thanks,

Avi

View 3 Replies View Related

Alias

May 18, 2006

How to get more columns within same alias?

(
select DateOpen AS Date,TestObjectID from RprRepair where TestObjectID = @AssetID
union all
select DateSent ,TestObjectID from RprRepair where TestObjectID = @AssetID
union all
select DateRepairFinished,TestObjectID from RprRepair where TestObjectID = @AssetID
) AS Der



This works fine alone, but when i put it into union i get an error that no more than one value can be in subqueries.

View 3 Replies View Related

Alias Information In 70

Jun 27, 2000

Hi,
In my database one user is aliased like a guest. When i run sp_helpuser on the particular database it is giving following result.

username login name database
-------- ----------- --------
aaaa NULL XYZ


NOrmally we fill see like this for GUEST User. Now i want to drop this user 'aaaa' only. IF i use
sp_dropuser 'null'
i think guest user will also drop from XYZ database.
So can anyone pls suggest me how to drop this user. I have to add a same user with full permissions. i know in 65 we have sysalternates table gives all alias information. Is there any table gives the same information in sql7.
Pls suggest me regarding this.

thank u
raj

View 1 Replies View Related

ROLES Vs Alias

Feb 16, 1999

I am just getting started with SQL Sever 7.0, most things are must easier and simpler.
I do have one mind set problem
With SQL 6.5 I created a logon(JAWS) , aliased the developers to the logon(JAWS)
and all the tables and other objects were owned by ((JAWS). The developers would then grant
access to the tables. And the users would open JAWS.tablename.
In the SQL 7.0 test are I take an NT group that have the devlopers in them and allow SQL SErver
logon for that group. Then I allow the group access to the database and give the group
database roles of db_owner, db_ddladmin, db_datareader and db_datawriter.
The developers can create objects but the objects are not owned by the role but are
owned by the indivudual NT accounts that are in the NT group.
Any hint of what to change to have the ownership show up as the NT group??

View 1 Replies View Related

Using Alias In Where Condition

Mar 1, 2001

Can we do

Select BookNo as Catalog from Books
where Catalog = 12356

I need to find the way to use alias in "where" for very complex query
Is anyone has way around it ?
Thank you

View 1 Replies View Related

Several Alias For The Same Column

Mar 2, 2005

In SQLServer I can't change my SQL column name.

But I need to see another field name in query tool (Excel)

How can I do that (alias..., description ?

Thanks

View 1 Replies View Related

Column Alias

Aug 30, 2006

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.

View 14 Replies View Related

Database Alias

Sep 27, 2006

Is there any way in SQLServer (2000) to setup an alias to another SQLServer database?

What I want/need to do is this:

.i have a database for each project but many of the tables schemas/triggers/stored procedures/etc. are common so what I want to do is run one set scripts. I want to essentially set up a project structure so that all specific app databases also include some core stuff.

.for example, consider a Users table (simple example). For two applications we have exactly the same users structure, sprocs, triggers, etc. but we have physically different data. I just want to run the same ddl.

.but the users table has a log associated with it in an associated log database. The trigger (and some historical queries) refer to the explicit log database (App1Log.Users, for example).

.what i'm figuring is that if i could set up an alias to the log database and have the sql code refer to that I'd be able to reuse all the code very neatly:

set logDBAlias = 'App1Log'

insert into logDBAlias.Users etc....

.trouble is I cannot figure out how to alias the log database!

Any ideas?

Thank you.
John

View 1 Replies View Related

Server Alias

May 13, 2004

how to add a alias to a server?
I have gone through BOL where it says it can be done thru right click agent -->> connections and configure alias.
i want to know if i configure a alias to a server from a client is it valid for other connections also?
meaning there are two database servers A and B
i add alias to server A by using client network utilities on server B,
then is this alias available if i try to connect to the server A thro some other server c?
hope this is not that confusing as it sounds. ;)

View 2 Replies View Related

Alias Name For Table

Dec 5, 2006

Hi ,

I have query related to using of Alias names for the Tables in SQL query, will the performance of the query affected if we use Alias name for tables in joins. considering the fact that there was no need to use alias name in those joins.

Your response is highly appreciated.

Regrads

View 6 Replies View Related

Alias Yes Or No For Performance

Mar 27, 2007

Hi all,
I have a small argument with friend here regarding use of alias names for tables will affect the performance of the query in case of a large database. Please provide your thoughts on the same.

Necessity is the mother of all inventions!

View 20 Replies View Related

Alias Has Confused Me.

Jul 23, 2005

I'm trying to learn how to make and use aliases for two tables in inthis update statement:ALTER PROCEDURE dbo.UpdateStatusAS UPDATE dbo.npfieldsSET Status = N'DROPPED'FROM dbo.npfields NPF, dbo.importparsed IMPLEFT JOIN IMPON (NPF.pkey = IMP.pkey)WHERE (IMP.pkey IS NULL) AND((NPF.Status = N'ERR1') OR (NPF.Status = N'ERR2') OR (NPF.Status =N'ERR3'))I thought I could define the aliases in the FROM statement.I'm using Access as a front end to SQL server if that makes adifference in the queries.

View 5 Replies View Related

Help Selecting An Alias

Mar 24, 2006

Hello guys, I need help in something as I don't know if it is possiblewhat I want.I have a select like this...SELECT Cod1 as SQL, Cod2 as Oracle FROM tableand I need to sort by alias SQL or Oracle as the select is composeddinamically so it could be either Cod1 as SQL or Cod2 as SQL and theuser needs to filter the data using SQL or ORACLE.I need something like this:SELECT Cod1 as SQL, Cod2 as Oracle FROM table WHERE SQL = 'one' ANDOracle = 'two'Any ideas?Thank you

View 4 Replies View Related

Database Alias Name

Jul 20, 2005

How would I create a database alias name for the following databasetestingdatabase.comThis database exisist on a remote server. I have tried using the databasename asserver.[testingdatabase.com].dbo.tablewere server is a linked server using sp_addlinkedserver[testingdatabase.com] is the database namedbo is the instantancetable is the name of the table.using select * from server.[testingdatabase.com].dbo.table producing thefollowing error.unspecified errorI cannot change or rename the database for many reason's (Original programerhardcoded this in a compiled app) don't have access to source code anyway.I have not found any docs on database alias, found docs for server / tablesetc.ThanksConrad

View 4 Replies View Related

Database Alias

Jul 20, 2005

Hi,Is there any way to set up an alias for a database within an SQL script?What I'm trying to acheive is... instead of having -<DatabaseName>.dbo.table, <DatabaseName2>.dbo.table, etc. - throughout thescript, I just set up an alias at the top of the script and reference them.TIABen

View 3 Replies View Related

Using Alias In An Expression

Jul 20, 2005

HiI tried to use the alias a1 for the column f1 like this, but it fails:create table a (f1 int not null primary key)insert into a (f1) values (2)select f1 a1 from a where a1 = 2Server: Msg 207, Level 16, State 3, Line 1Invalid column name 'a1'.In my real situation f1 is a "pretty complex" calculation, and a1 is analias of that.I wanted to use the alias in the query instead of repeating the calculationseveral times.If this use of alias is not legal, then is there a way to get around this?TIAGunnar VøyenliEDB-konsulent asNORWAY

View 2 Replies View Related

CASE Alias In WHERE

Jul 20, 2005

Hello,I found members of this group very helpful for my last queries.Have one problem with CASE. I can use the column name alias in Order By Clausebut unable to use it in WHERE CLAUSE.PLS TELL ME IF IT IS POSSIBLE TO USE IT IN WHERE CLAUSE AND SOME ALTERNATIVE.QUERY:SELECTM.SECS =CASEWHEN NO_OF_SEC IS NULL THEN -1WHEN NO_OF_SEC =0 THEN 1ELSE NO_OF_SECENDFROM DOWNLOAD_MASTER MWHERE M.SECS < 100ORDER BY M.SECSHoping for a immediate reply.thanks in advance

View 3 Replies View Related

Getting The Alias Table

May 13, 2008



Hi,
I have a problem which i need to get the NewDate which the NewDate is Generated thru Computation of DateDiff
Here's my Statement

SELECT DISTINCT
RBUCode, DCO, Prov, District, DATEDIFF(mi, ReportedDate + ' ' + ReportedTime, CompletedDate + ' ' + CompletedTime) / 60 AS NewDate
FROM PostedFaultTable
WHERE (DCO = 'La Union')

I need to get the Less than 5 Hrs of the NewDate


I tried This but it's not working
RBUCode, DCO, Prov, District, DATEDIFF(mi, ReportedDate + ' ' + ReportedTime, CompletedDate + ' ' + CompletedTime) / 60 AS NewDate
FROM PostedFaultTable
WHERE (DCO = 'La Union') and NewDate <= 5


Regards

View 4 Replies View Related

Table Alias

May 30, 2008



Is table alias using in a query only for Readablity?
Is there any difference in performence between a query with table alias and a query without table alias?

View 1 Replies View Related

TNS Alias Name Changed

Jan 22, 2007

Maybe somebody has asked this question, but I could not find it....

I used SSIS to transfer data from Oracle database to SQL Server 2005 (with Microsoft OLE DB Provider for Oracle). It worked fine with the TNS alias name said oracle1. Then I changed the TNS alias name to oracle2. I could connect to Oracle database with oracle2 using Oracle's tool. But SSIS could not find (or solve) oracle2, if I changed the name back to oracle1 in SSIS, then SSIS worked.

Any idea?

Thanks!

View 7 Replies View Related

How To Use Alias In SQL Server?

Mar 17, 2008



Is there any way to refer to an Alias in a select statement? For example, if I create an expression that sums to fields together and give it the alias TOTAL, and then in another field I want to create and other expresssion that multiplies a field by TOTAL, what is the syntax?

In MS Access the Alias name is surrounded by brackets, but I can't seem to find a way to do this with SQL Server.

Thanks

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved