Reuse Of Field Aliases
Jan 3, 2007
I have been working with SQL for quiet a while but think this perhaps is a very basic question that has always escaped me:
At my work I was exposed to both, MS SQL Server 2000 and Sybase Adaptive Server Anywhere/Sybase SQL Anywhere.
Under Sybase I was able to use aliases in other calculations and filters but i have never been able to do the same with SQL.
Example:
In Sybase I can write this:
Select Price * Units as Cost Cost * SalesTax as TotalTaxFrom Invoice Where TotalTax > 3.5
However if i want to do this in MS SQL 2000 i have to go trough
Select
Price * Units as Cost
Price * Units * SalesTax as TotalTax
From Invoice
Where (Price * Units * SalesTax) > 3.5
In the long run this is costing me a lot of code redundancy, not to mention a debugging nightmare. Is there a way to replicate this alias usage in MS SQL Server?
View 3 Replies
ADVERTISEMENT
May 7, 2008
Please help...
I need help analyzing this query. I am not sure why the person that wrote this query used the same table for 3 different aliases.
FROM property,
address,
ppi,
code_table state,
code_table country,
code_table prop_role
I tried to get rid of :
code_table country,
code_table prop_role
and put "state" in any line that referenced:
code_table country,
code_table prop_role
to see if the query worked the same, but I different results. Why?
Please help...
--7 seconds to omplete.
--set explain on;
--UNLOAD TO '/export/home/permit/owneraddress.txt'
SELECT ppi.id,
ppi.property_id,
ppi.party_id,
ppi.address_id,
ppi.eff_from_date,
ppi.eff_to_date,
address.line_1,
address.line_2,
address.line_3,
address.city,
state.code_table_cd,
address.zip_postal_code,
country.code_description,
address.line_care_of,
prop_role.code_table_cd
FROM property,
address,
ppi,
code_table state,
code_table country,
code_table prop_role
WHERE property.id = ppi.property_id
AND ppi.address_id = address.id
AND (property.eff_to_date IS NULL OR property.eff_to_date >= getdate())
AND (ppi.eff_to_date IS NULL OR ppi.eff_to_date >= getdate())
AND ppi.eff_from_date <= getdate()
AND state.id = address.province_state_cd
AND country.id = address.country_cd
AND prop_role.id = ppi.prop_role_cd
AND prop_role.code_table_cd = 'OWNER'
AND property.pact_code <> 'PERS'
View 5 Replies
View Related
Nov 9, 2004
Does anyone know of a neat way of aliasing tables in MS SQL Server 2000? I have a table [X] and I want to refer to it (in TSQL statements, stored procedures, etc.) by two names, say [X] and [Y]. There should be only one copy of the table's data.
I could handle this in my site logic (ie convert [Y] to [X]), but it would be really clean to handle this in the database.
Thanks for your advice.
Matt
View 7 Replies
View Related
Mar 26, 2015
trying to write a query from 2 tables and join them only they share a column name of the same name.I can define both aliases seperatrly but get an error when trying both,
select
case_HDR.case_nbr as "test"
Case_DTL.case_nbr as "test2"
From
Case_HDR, case_dtl
I need to join them also but trying to work in stages.
View 12 Replies
View Related
Apr 30, 2007
Dear Folks,
I'm getting the error with application like this..............
"the transaction log for database "mydatabase" is full.....to find out why space in the log can not be reused, see the log_reuse_wait_desc column in sys databases. "
I've checked with the column, there are totally 3 options.(I've checked with all other databases)
1)checkpoint,
2)log_backup,
3)nothing
what can i choose for this error to be suppressed?
actually there was log_backup option.
please guide me in this regard.........
thanks in advace.
Vinod
View 1 Replies
View Related
Jun 27, 2007
I have a SELECT query with an alias in it.
The intellisense shows all field except the alias one.
What goes wrong?
View 2 Replies
View Related
Mar 8, 2006
I'm in the process of building a site and converting views/tables/queries from an Access database to SQL. I've done this quite a few times, and never had any significant issues I couldn't figure out on my own.
In Enterprise Manager, I've created a view and in the query, I need to create an alias that is similar to below:
SELECT ((monthmult) + ((b2avg*15)-(av2*10)) + (lp1+lp2) + ((b1avg*30)-(av1*20))) as PIndexValue
which is how the formula reads in the Access view.
However, when I got to run the query, SQL strips out all of the parentheses and calculates the value in left to right order:
(monthmult + b2avg*15-av2*10 + lp1+lp2 + b1avg*30-av1*20) as PIndexValue
Which gives me an incorrect value.
Does anyone know why this is happening, or am I just unaware of the right way of doing it?
Thank you,
Derrick
View 3 Replies
View Related
Jan 15, 2008
Hi,
I’m working with a really old design migrated to SQL 2005, in which I basically have two tables…
Table 1 contains all the “proper” data, and has columns called: col_1, col_2, col_3
Table 1’s data is something like:
col_1, col_2, col_3
Jack,jack@yahoo.ca,Toronto
Jill,jill@hotmail.com,Montreal
Table 2 contains meta-data for Table 1, specifically, it has two columns: column, meta-data
Table 2’s data is something like:
column,metadata
col_1,name
col_2,email
col_3,city
(Hopefully, my description of the design makes sense….basically; Table 2’s data describes what’s in each column of Table 1).
So, the question, if I want to write a ‘SELECT’ on Table 1, how can I use the data in Table 2 as aliases (or column) headers.
I’m currently going down the path of building dynamic SQL statements in T-SQL….but before I get too far, wanted to vet this idea here (it’s always been a fantastic resource for me)
Thanks in advance!
View 12 Replies
View Related
Jun 17, 2008
TABLE1
======================
PriceList
---------
1
2
3
1
2
3
1
2
3
Price
-----
777
888
999
777
888
999
777
888
999
(pretend these columns are side by side)
======================
I need to make a query to:
SELECT PRICE AS 'PRICE1' WHERE PRICELIST = 1
AND SELECT PRICE AS 'PRICE2' WHERE PRICELIST = 2
AND SELECT PRICE AS 'PRICE3' WHERE PRICELIST = 3
the output that i want is:
PRICE1
------
777
777
777
PRICE2
------
888
888
888
PRICE3
-----
999
999
999
(pretend these columns are also side by side)
View 6 Replies
View Related
May 6, 2008
Hi,
is it possible to assign a numeric value as a column alias:
select ... as 1234 from ...
does not work.
Cheers
View 3 Replies
View Related
Feb 15, 2007
Does anyone know how to create SQL Server aliases for Itanium servers?
Alternatively does anyone know how to use SMO to create a SQLAliasCollection for a specific server? I can't seem to find how to get that collection.
I've added aliases that work for 32 bit applications but they don't seem to be used for 64 bit applications.
View 3 Replies
View Related
Oct 5, 2006
Formatting question. The query below is failing on the columns with spaces in the name. I've tried brackets and single quotes with no luck. How should this be formatted?
----------------------------
SELECT x.trkuniq, s.mstuniq, t.meetuniq,
c.coursec AS Course_Code,
c.descript AS Course_Name,
[q.cactus #] AS Cactus_#
s.sectionn AS Section,
RTRIM(f.lastname) + ', ' + RTRIM(f.firstname) AS Teacher, f.funiq,
t.termc AS Term_Code, zd.cycldayc AS Day,
zp.periodc AS Period, zp.periodn,
p.schoolc AS School
FROM mstmeet t INNER JOIN
mstsched s ON t.mstuniq = s.mstuniq INNER JOIN
trkcrs x ON s.trkcrsuniq = x.trkcrsuniq INNER JOIN
course c ON x.crsuniq = c.crsuniq INNER JOIN
track p ON p.trkuniq = x.trkuniq INNER JOIN
facdemo f ON s.funiq = f.funiq INNER JOIN
courses q ON c.coursec = [q.course number] INNER JOIN
trkper zp ON t.periodn = zp.periodn AND
x.trkuniq = zp.trkuniq INNER JOIN
trkcycle zd ON t.cycldayn = zd.cycldayn AND
x.trkuniq = zd.trkuniq
View 4 Replies
View Related
Apr 25, 2007
It's a pretty basic question but I haven't been able to find any examples out there. I dimmed a dataadapter and would like to reuse later in my code (line 3 in the code below). What is the correct syntax to do this? Dim da As New SqlDataAdapter("SELECT * FROM myTable", conn)da.Fill(myDataTable)da.______ ("SELECT * FROM myTable2", conn)da.Fill(myDataTable2)
View 2 Replies
View Related
Jul 10, 2004
Hi,
I am getting the following error:
The SqlParameter with ParameterName '@pk' is already contained by another SqlParameterCollection
If I could work out what code to post I would, but I can say that I am managing my Sql data in my code by caching small arrays of SqlParameter objects as pulled from the database. If I need to update the DB I change the cached SqlParameter and re-insert it. If I perform a SELECT I check the cache first to see if I already have the SqlParameter.
However, currently, I am experiencing the above error when performing a select, then later an update, followed by another update.
Would I be correct in saying that a SqlParameter object can only be used once for a database operation and should be discarded? Would I be correct if I said that the SqlCommand object should be discarded? I am barking up the wrong tree entirely?
Distressed in DBLand,
Matt.
View 2 Replies
View Related
May 1, 2006
I can create a SqlCacheDependency, and link it to a cached item in httpcontext cache. When something change, it will remove the cached item from the cache. I think I have to redo the process when that happens - prepare sql command, create SqlCacheDependency and insert the item into cache. Now I only need a notification from my SQL when something changes in one of my table, I don;t need read anything from db, and I think I should find a way to not recreate the SqlCacheDependency object everytime?
any suggestion?
View 2 Replies
View Related
Mar 4, 2015
TableX
-------
X1
X2
X3
TableY
-------
Y1
Y2
Y3
I need to write a query with the following joins:
TableX.X1 = TableY.Y1
TableX.X2 = TableY.Y2
What is the proper way to do this in SQL? Would it be:
select x.*
from TableX x
join TableY y1 on y1.Y1 = x.X1
join TableY y2 on y2.Y2 = x.X2
Is there a more proper way to do this without creating multiple aliases of the same table?
View 2 Replies
View Related
Nov 14, 2007
Just stumbled over the interesting bug -
if you have an SQLExpress server and the "allow remote connection" in SQL Instance is turned ON, it doesn't means that it works if you create some aliases for this instance
If Im trying to connect to SQLExpress via new created alias ('localhost' for example) I got the "SQL Server does not allow remote connections" error. Albeit, connecting to ".SQLExpress" is successful.
I've checked the properties of locahost DB instance in SQL Management Studio - remote connection is allowed.
I've restarted the SQLExpress instance.
There is no SQLServer or other instances on my PC
But it doesn't work
How to fix this and allows to connect to sql instance via aliases?
View 2 Replies
View Related
Dec 6, 2007
HI,
I have a view where I want to add Rundate and Prev Month Rundate as computed columns to simplify my joins and calculations.
Rundate will be a select from another table that has a list of rundates for each month like this.
SELECT MAX(fm_dateend)
FROM dbo.tbl_FiscalMonth
WHERE fm_dateend <= getdate()) AS smalldatetime) AS CurrRunDate
Now, I want to add Prev Month Rundate on the basis of CurrRunDate but it does not accept CurrRunDate. The query is like this
SELECT MAX(fm_dateend)
FROM dbo.tbl_FiscalMonth
WHERE fm_dateend < CurrRunDate) AS smalldatetime) AS PrevMonthRunDate
Can anyone help me to work around with the alias as I dont like to put a whole bunch of code inplace of CurrRunDate(Alias).
Thanks,
Shariq
View 1 Replies
View Related
Dec 15, 2007
Hello, Can I reuse the deleted primary key id? I'm using SQLServer2005. cheers,imperialx
View 6 Replies
View Related
Nov 14, 2006
What is the best code pratice to use do the following code,
SELECT
fo.no as LNum,
fo.name as LName,
sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END) as In1,
sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END) as In2,
sum(In1+In2)/10 as inDec,
from fo group by fo.no,fo.name order by fo.name
instead of
SELECT
fo.no as LNum,
fo.name as LName,
sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END) as In1,
sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END) as In2,
((sum(CASE fo.docnome WHEN "In" THEN fo.etotal ELSE 0 END))+sum(CASE fo.docnome WHEN "In2" THEN fo.etotal ELSE 0 END)))/10 as inDec,
from fo group by fo.no,fo.name order by fo.name
I cant use functions and procedures. Is there any better and cleaner way to code this, reusing the calculated values?
View 1 Replies
View Related
Jul 23, 2005
I wonder if there is a solution for this in SQL 2000 (or do I have towait for SQL 2005)?I am currently in the middle of developing a 'Yahoo' style portal whichwill be rolled out in about 20 or so countries. I have set up in SQLServer one database per country. All the portals have the samefunctionality - but show different data.Is it possible to have a single database which holds storedproceedures, functions and views and have the individual countrydatabases use these?Note: I want to avoid using EXEC sp_executesql.I look forward to some good news on this! Thank you in advance.Dadou.
View 2 Replies
View Related
Apr 19, 2008
Hi. I'm starting to try out LINQ to SQL, and so I'm using SQL Express (on Vista) to experiment. My problem is that once I create and delete a database, I can never use the name again. If, after deleting the old database, I try to create another one with the same name (say Acct1), I get "Create failed for Database 'Acct1'. An exception occurred while executing a Transact-SQL statement or batch. The logical file name "Acct1" is already in use. Choose a different name. Error: 1828"
I am trying to create the database in SQL Server Management Studio Express. That database name does not appear in the list of databases: there is only AdventureWorks and the system databases. The .MDF and .LDF files have been deleted. Not just sent to the recycle bin, but permanently deleted.
I have already used up Acct1, Acct2, and Acct3, just to try out different scenarios. Each time, I delete the old database before trying to create a new one with the same name, but I am forced to always supply a new, different name. I have checked the directory with hidden files and system files showing to be sure there is no old file lurking there somewhere.
Is there a way to delete these old "logical file names"? I can't even find any reference to their existence except for the message that says they are already in use.
View 11 Replies
View Related
Jul 29, 2015
with c1 As (
select 1 As '1' , 2 Â As '2' , 3 As '3'
)Â
, c2 As (
select 4 Â As '4', 5 Â As '5', Â 6 As '6'
union all
select 1 , 2 ,3 from c1 Â -- >>>>> Â select from c1 hereÂ
) select * from c2Â
 union all
 select * from c1 -- >>>>>> and select from c1 hereÂ
According to the query above , I try to reuse the subquery by put  the subquery into 'with cte'  name (c1)  then i select this  2 times .
if I do this way , how many time this subquery (c1) execute ?
if 1 time then this is the right way to reuse this subquery .
if 2 times  , it is not then what should i do to reuse this subquery ?
View 3 Replies
View Related
Nov 26, 2007
I have read the articles posted online concerning different dialog reuse strategies. Most of them create a new table in the sender to hold dialog ids. I was wondering what is wrong, if anything, with the following approach:
Code Block
declare @dlg uniqueidentifier
select top 1 @dlg = conversation_handle from sys.conversation_endpoints where state IN ('CO')
if @dlg is null
begin
begin dialog conversation @dlg
from service [tcp://SFT3DEVSQL01:4022/TyMetrix360Audit/DataSender]
to service '//TyMetrix360Audit/DataWriter','386DDD04-7E55-466A-BE83-37EFC20910B9'
on contract [//TyMetrix360Audit/Contract] with encryption = off;
end
;send on conversation @dlg
message type [//TyMetrix360Audit/Message] (@msg)
Here I simply select a conversation handle directly from the sys.conversation_endpoints table. Can anyone see any issues with this approach?
Thanks in advance....
View 6 Replies
View Related
Jun 5, 2007
I am working on a project developing a fairly large number of reports with a team of developers. Many of these reports have common elements and code, such as common headers with user-selectable colors. Additionally, many of the common parts of the reports are at mockup stage currently, and many features will have to be added to the reports as time goes on.
We're attempting to create a generalized framework that will minimize the duplication of effort as we develop these reports, and as we go back and modify or fix them later.
What is the best way to approach this?
My first attempt was to create a report template and base all the reports off of the same template. That was fine for the first pass, but as we need to make changes later, they will not be propagated to the already existing reports.
My second attempt was to have each componant of that template reference a subreport, so that changes to the actual report template will be minimized as we go forward. This works great for minimizeing work, but it appears that you lose many features with the use of subreports, and there seems to be a pretty serious performance impact as well. I have posted about one such issue here: Pagination
If anyone has pointers about how to go about this, and where I should start, they would be greatly apreciated!
View 2 Replies
View Related
Jan 18, 2007
Hi All,
I have this query :
select col1, col2, col3, col4, col5,..... , (select col99 from tab2) as alias1 from tab1 where <condition>
order by
case @sortby
when 'col1' then col1,
when 'col2' then col2,
when 'col3' then col3,
when 'col99' then col99
end
when i execute the above query it gives me the following error message.
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'col99'.
Thanks in advance.
Thanvi.
View 5 Replies
View Related
Jun 19, 2014
I have a table in which there is a column of type identity(1,1),in the same table i have bulk data, and i have deleted a row from my table, so now i want to reuse that same Id again, how to do that.
View 3 Replies
View Related
Mar 4, 2008
Hello,
I have an ID column which is an Identity column, and it counts from 1 upwards.
If I have 10 records, I have ID's number 1 to 10.
If I then add another record, it will give the ID number 11.
Imagine I then delete ID number 9
Is it possible to get the next record I create to take number 9 instead of 12 ?
View 5 Replies
View Related
Feb 7, 2006
Hello
I have some common functions that i use in several script tasks. How du i store a function globaly so that i can use it from different projects and still only have to edit it one place ?
View 4 Replies
View Related
Jun 12, 2007
I have several stage to star (i.e. moving data from a staging table through the key lookups into a fact table) ETL transformations in a single SSIS package. Each fact table has a different set of measures but the identical foreign key set, e.g. ConsultantKey, SubsidiaryKey, ContestKey, ContestParamKey and MonthKey.
Currently I have to replicate the key lookup (Surrogate Key Pipeline, or SKP) for each data flow. If I could cache each dimension one time in the package and reuse it for each stage to fact it would be much more efficient.
Is there a way for me to reuse a common data flow?
View 6 Replies
View Related
Oct 31, 2006
I have a question regarding the nature of virtual sql servers, specificially what protocol is used to communicate to the server when a request is made by a client.
For example, if a scheduled job is run on the virtual sql server, what determines the protocol used (e.g. TCP/IP, named pipes etc.) by SQL Server agent? Is it the client network alias set up on the virtual server?
I am asking because currently the client aliases on some of our virtual sql servers are using named pipes and I think this is causing a problem with our backups.
Thanks,
Adrian.
View 8 Replies
View Related
Apr 4, 2008
Can someone please shed some light on what seems to me to be a common requirement.
If I create an alias or linked server to Server1 - say Alias1 - on Server1 and then use that name in a query on Server1, a remote/distributed query is always used (even though we are running on the local server and that overhead is completely unnecessary).
Is SQL Server really not capable of deciding that
select * from Alias1.db1.dbo.table1
and
select * from Server1.db1.dbo.table1
should be optimized and executed exactly the same when Alias1 is Server1, but that it is a distributed query ONLY when Alias1 is really referring to a remote server? I realize that the four part name is not necessary when I am referring to objects on the current server, but I am trying to write code that is server instance independent.
It just seems that if that is not possible, then the only way to create system independent stored procs that can run in dev, staging, and production environments and work with multiple databases on multiple servers is to create all sorts of scripts to regenerate all the procs whenever you move a database between servers?
If SQL Server is even close to the enterprise big iron server that MS now claims it is, it surely needs to support running in dev, staging, and production environments and work with multiple databases on multiple servers?!
I'm really looking for someone to tell me I'm missing something simple, and of course you can do this - but complex workarounds are invited too :-)
This is not something I am investigating as an academic exercise, I am already doing this, but I have to figure out how to do it better because with all these unnecessary distributed queries, performance is horrible.
View 2 Replies
View Related
Jan 25, 2008
I've added a typed DataSet and dragged a table across from the server explorer. When I click configure on the table adapter, then click previous back to the "Choose Your Data Connection" dialog. The only option is the new connection that was just created when I added the sql server to the server explorer. Is there anyway to reuse my existing web.config connection string? My goal is to have a single connection string in my web.config.
Thanks.
-David
View 3 Replies
View Related
