Cannot Use Alias For A Field Name In Views!!!!!!!!
Jul 27, 2004
HI Friends,
Can anybody help in this prob with SQL server Enterprise Manager
Prob:
I cannot use alias name spelled same as the table field-name, in a view. For eg:
SELECT Name AS name
FROM dbo.test
if I take this view in the design view in Enterprise Manager, it will changed to:
SELECT Name
FROM dbo.test
Can anybody help me How can I avoid this???? I need the same alias name with different case....
Nish
View 5 Replies
ADVERTISEMENT
Oct 31, 2005
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:
Prathmesh
View 8 Replies
View Related
Nov 20, 2006
Hello All,
I have the following code:
USE MLS
select sc.name,f.field#,fdesc,flong
from sysobjects so join syscolumns sc
on so.id = sc.id
join fld f
on f.field# = replace(sc.name,'_','')
where so.name = 'dbo.tbl_MLS_Leads_Trans'
I am trying to get the description which is flong and I get the following error message:
Msg 208, Level 16, State 1, Line 2
Invalid object name 'fld'.
What am I doing wrong?
TIA
Kurt
View 2 Replies
View Related
Jun 1, 2006
My question is simple, I'd like to do something I do in Jet ANSI-89 SQL. Mind you I'm just adding numbers here - they are not actual columns in 'SomeTable'
SELECT 1 AS A, 2 AS B, A+B AS C
FROM SomeTable
The Jet engine evaluates and does arithmetic on the Aliased column names - handy when they contain their own functions. The resultset would show:
A B C
1 2 3
However from what I can tell SQL Server 2005 is not picking this up. Is their an equivalent?
View 1 Replies
View Related
Jul 18, 2015
use Northwind
Go
select
dbo.Orders.OrderID
,Cast(dbo.Orders.OrderDate As DATE)Order_Date
, dbo.Customers.CustomerID
, dbo.Customers.CompanyName
, dbo.Products.ProductName
[code].....
I cannot use the alias field names as part of additional calculations for new columns.
total_Amount and Grand_Total cannot be done with my skill level.
View 9 Replies
View Related
May 20, 2004
When I created a CASE statement (This is at work, Pat:)) it is about 30-40 lines long. I gave it a name and set the name = to the case statement:
ie,
SELECT fieldname1 =
CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END
, fieldname2
, fieldname3
FROM tablename1
GROUP BY CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END, , fieldname2, fieldname3
etc.
The long CASE statement in my GROUP BY is awkward to me. Is this the only way to do it? I tried using the fieldname1 but it comes back as an invalid field name and asks for the "expression".
Regards,
Dave
View 5 Replies
View Related
Aug 17, 2007
Hi, Im trying to run this query
UPDATE DataModif
SET t.Ind_des = Replace (t.Tit_Des,"'",'"')
FROM Tit_Modificables t
WHERE
t.Ind_num in
(SELECT CAST (t2.Ind_num AS VARCHAR(10))
FROM Tit_Modificables t2
WHERE t2.Emp_id ='1100004' AND t2.Ejercicio_fiscal = 2003 AND t2.Nom_tabla = CAST (10 AS NUMERIC))
but ir sends this message error
The multi-part identifier "t.Ind_num" could not be bound.
I don't know if there is a problem because I'm using an Update.. FROM
Any help?
View 6 Replies
View Related
Apr 28, 2006
Hi,
When I try execute one query in SQL 2k5, with alias in order by clausule, I retrieve the follow message:
Server: Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "alias.fields" could not be bound.
Where alias is a any alias and, fields is a field of the table with alias.
Already exists one fix to patch this?
Thanks
View 39 Replies
View Related
Jan 17, 2007
Im trying to execute following update SQL:
UPDATE Property SET ImageList = U.ImageList
FROM Property M
INNER JOIN RETS.dbo._Updates U ON M.ListingID = U.ListingID AND M.FeedID
= U.FeedID
WHERE M.FeedID = ?
But following error:
[Execute SQL Task] Error: Executing the query " UPDATE Property SET
ImageList = U.ImageList FROM Property
M INNER JOIN RETS.dbo._Updates U ON M.ListingID = U.ListingID
AND M.FeedID = U.FeedID WHERE M.FeedID = ?" failed with
the following error: "The multi-part identifier "M.FeedID" could
not be bound.". Possible failure reasons: Problems with the query,
"ResultSet" property not set correctly, parameters not set correctly,
or connection not established correctly.
ByPassPrepare is set to TRUE and ParameterName = 0 to variable User::Feed_ID
HOWEVER - following query executes fine:
UPDATE Property SET
ImageList = U.ImageList
FROM Property M
INNER JOIN RETS.dbo._Updates U ON M.ListingID = U.ListingID AND M.FeedID
= U.FeedID
WHERE M.FeedID = 11
Beats me - any help with explaining this to me
please?
View 7 Replies
View Related
Nov 20, 2006
Hi Everyone
This is the query and I am getting follwoing error message
"The multi-part identifier "InvDate.Account Reference" could not be bound."
SELECT MAX([DATE NOTE ADDED]) AS LASTDATE,
CC.[COMPANY],
CC.[ACCOUNT REFERENCE],
INVDATE.[LASTORDERDATE]
FROM CUSTOMERCONTACTNOTES AS CCN,
(SELECT *
FROM CUSTOMER) AS CC,
(SELECT MAX([INVOICE DATE]) AS LASTORDERDATE,
[ACCOUNT REFERENCE]
FROM INVOICEDATA
GROUP BY [ACCOUNT REFERENCE]) AS INVDATE
WHERE CCN.[COMPANY] = CC.[COMPANY]
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS IN (SELECT DISTINCT ([ACCOUNT REFERENCE])
FROM INVOICEDATA)
AND CC.[ACCOUNT REFERENCE] COLLATE SQL_LATIN1_GENERAL_CP1_CI_AS = INVDATE.[ACCOUNT REFERENCE]
GROUP BY CC.[COMPANY],CC.[ACCOUNT REFERENCE]
ORDER BY CC.COMPANY ASC
By the way its SQL Server 2005 Environment.
Mitesh
View 4 Replies
View Related
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
Jul 20, 2005
I created a view that joins two tables (tblOrder and tblProduct). The fieldProductID is common to both. When I save the View I get the following errormessage: "Column names in each view must be unique." Is there a way aroundthat? Should I be naming my Key Fields and Foreign Key fields differently?I'm new to MS-SQL. Thanks for your help.Louis
View 2 Replies
View Related
Sep 12, 2007
Our phone system keeps a list of events, such as login and logout, which I'm doing a self join on to get the start and end times so I can calculate the time between the start and end of each event during a time period.
Then I stuffed the self joins into a couple views for the events I'm interested in...
The problem is that this makes the "end time" a calculated field, so when I use it as part of the filter it takes forever. I need to account for events ending within the time period, and events starting within the time period... so I need to filter on both the start and end time in the initial select. Filtering the start time first in a subselect, then the end time in the main select is quick but can lose things that started before the start time.
So how can I speed up the filtering on that calculated column?
Or am I going to have to get really creative with unions?
View 2 Replies
View Related
Dec 19, 2007
While creating a view in SQl Server 2005 Management Studio Or Interation Services on a SQL Server 2000 database using "*" something very strange happens. Has anyone every seen this happen before?
The new view is created something like the following:
CREATE VIEW [dbo].[new_view]
AS
SELECT *
FROM Table1
GO
However, when right-clicking on the new view, and choosing "Design", the SELECT scrambles the field names with aliases of the other field names. When the view is run, the result set is incorrect. It may look something like the following:
SELECT
Field1,
Field2 AS Field3,
Field3 AS Field4,
Field4 AS Field5,
.
.
.
From Table1
GO
View 2 Replies
View Related
Apr 3, 2006
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies
View Related
Aug 4, 2015
I would like to create a procedure which create views by taking parameters the table name and a field value (@Dist).
However I still receive the must declare the scalar variable "@Dist" error message although I use .sp_executesql for executing the particularized query.
Below code.
ALTER Procedure [dbo].[sp_ViewCreate]
/* Input Parameters */
@TableName Varchar(20),
@Dist Varchar(20)
AS
Declare @SQLQuery AS NVarchar(4000)
Declare @ParamDefinition AS NVarchar(2000)
[code]....
View 9 Replies
View Related
Sep 6, 2007
Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?
View 1 Replies
View Related
Jun 28, 2007
Hello.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
View 3 Replies
View Related
Feb 22, 2007
Hello,
to make a report easier I'm developing it using a view of joined views of joined views.
Is there any significant performance penalty as opposed to just having one big select?
Cheers.
View 1 Replies
View Related
Mar 14, 2006
Hello There,I'm trying to create a view that has calculations dependent oncalculations, where the problem resides is that each time I make acalculation I must create an intermediate view so I can reference aprevious calculation.for example lets say I have my_table that has columns a & b. now I wanta view that has a & b, c = a + b, and d = c + 1.this is grossly simplified, the calculations I actually use are fairlycomplex and copying / pasting them is out of the question.so what I have is my_view_a which makes column c, and my my_view_finalwhich makes column d (however, in my real application I have 5 of theseviews, a/b/c/d/e/)is there anyway I can consolidate all these views into one? I wasthinking of using a stored procedure with temp tables or somethingalong those lines.I just which I can use the aliases that I create for c in d in onestep.any insight would be greatly appreciated.
View 5 Replies
View Related
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
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
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
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
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
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
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
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
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
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
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
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
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
