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'
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.
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.
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?
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)
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)
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
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?
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?
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).
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'.
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.
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.
Using SQL Server 2000. How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.