Numeric Aliases For Columns
May 6, 2008Hi,
is it possible to assign a numeric value as a column alias:
select ... as 1234 from ...
does not work.
Cheers
ADVERTISEMENT
Numeric Expressions And Aliases
Mar 8, 2006I'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
Aliases && Columns Name W/ Spaces
Oct 5, 2006Formatting 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
T-SQL (SS2K8) :: Aliases In OPENQUERY - Get Selected Columns By Their Ordering Number
Apr 9, 2014I have the following query
SELECT [KPI].*
FROM
OPENQUERY(LINKED_OLAP,'SELECT
HEAD(TAIL(DESCENDANTS
(TAIL([Time].[CalendarMonth].[Year],1), [Time].[CalendarMonth].[Month]),4),3) ON COLUMNS,
([Game].[Game Code].&[1] ,
[Code] ...
The last three columns are dynamically generated because they change during time. Next month they will be different.
I like to introduce aliases for them and to have them in the select as 'TWO_MONTHS_AGO','ONE_MONTH_AGO', 'CURRENT_MONTH'
I wonder if exists something like [KPI].(0), [KPI].(1), and etc.. of the OPENQUERY to get the selected columns by their ordering number...
How Can I Select Numeric Columns Only?
Oct 3, 2006hi all,
how can i select numeric (int, money, numeric, etc) columns only
from syscolumns.
i need to run a sum() aggregate on all numeric columns
thanks,
joey
SQL Server 2014 :: Capturing Min Max And Avg Of All Numeric Columns Within A Database
Jul 1, 2015I'm trying to capture Column Statistics Profile as if I was using SSIS data profiling task. I do not have this option and would like to see how I could go about capturing the min max and avg of all numeric columns within a database.
View 0 Replies View RelatedIdentity Columns Fails To Increment After 2147483585 - Numeric Data Type
Nov 20, 2007I had a strange problem today with one of the identity fields in a frequently used table. It appears that the Identity column for a table had stopped incrementing after it reached 2147483585. Since I had inherited this table, I am not sure if the identity column type has been modified from int to numeric, but the current type is Numeric (9) which is 19 precision and 0 scale value.
When resetting the seed to 1, it started working. I tried creating a temp table with numeric value and it increments well beyond billions with no problems.
Has anyone encountered this? Any best practices around defining Identity data type (ie. use int or bigint and avoide Numeric)?
Thanks
NS
T-SQL (SS2K8) :: Arithmetic Overflow Error Converting Numeric To Data Type Numeric
Jun 10, 2014when I run below query I got Error of Arithmetic overflow error converting numeric to data type numeric
declare @a numeric(16,4)
set @a=99362600999900.0000
The 99362600999900 value before numeric is 14 and variable that i declared is of 16 length. Then why this error is coming ? When I set Length 18 then error removed.
Arithmetic Overflow Error Converting Numeric To Data Type Numeric
Mar 21, 2006Guys
I'm getting the above when trying to populate a variable. The values in question are :
@N = 21
@SumXY = -1303765191530058.2251000000
@SumXSumY = -5338556963168643.7875000000
When I run, SELECT (@N * @SumXY) - (@SumXSumY * @SumXSumY) in QA I get the result OK which is -28500190448996439680147097583285.072256 ie 32 places to left of decimal and 6 to the right
When I try the following ie to populate a variable with that value I get the error -
SELECT R2Top = (@N * @SumXY) - (@SumXSumY * @SumXSumY)@R2Top is NUMERIC (38, 10)
Any ideas ??
Removing Non-numeric Characters From Alpha-numeric String
Oct 24, 2007Hi,
I have one column in which i have Alpha-numeric data like
COLUMN X
-----------------------
+91 (876) 098 6789
1-567-987-7655
.
.
.
.
so on.
I want to remove Non-numeric characters from above (space,'(',')',+,........)
i want to write something generic (suppose some function to which i pass the column)
thanks in advance,
Mandip
TSQL Function To Return Numeric Value Of Non Numeric Field
Jul 20, 2006I need to replace Access Val() functions with similiar function in sql.
i.e. Return 123 from the statement: SELECT functionname(123mls)
Return 4.56 from the satement: SELECT functionname(4.56tonnes)
Any one with ideas please
Thanks
George
Aliases
May 7, 2008Please 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'
Table Aliases?
Nov 9, 2004Does 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
Aliases To Get Around Error
Mar 26, 2015trying 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.
How To Use Aliases In Typed Dataset
Jun 27, 2007I have a SELECT query with an alias in it.
The intellisense shows all field except the alias one.
What goes wrong?
Using Data As Column Aliases
Jan 15, 2008Hi,
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!
Multi Aliases With The Same Column?
Jun 17, 2008TABLE1
======================
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)
64 Bit Server Aliases SQL 2005
Feb 15, 2007Does 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.
Reuse Of Field Aliases
Jan 3, 2007I 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?
SQL 2012 :: Multiple Aliases Of Same Table?
Mar 4, 2015TableX
-------
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?
Aliases And Remote Connection Failure
Nov 14, 2007Just 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?
Refering Aliases In The Same View/ Query
Dec 6, 2007HI,
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
Column Aliases In Case Statement In Order By
Jan 18, 2007Hi 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.
Pattern Matching - Searching For Numeric Or Alpha Or Alpha-Numeric Characters In A String
Aug 18, 2006Hi,
I was trying to find numeric characters in a field of nvarchar. I looked this up in HELP.
Wildcard
Meaning
%
Any string of zero or more characters.
_
Any single character.
[ ]
Any single character within the specified range (for example, [a-f]) or set (for example, [abcdef]).
Any single character not within the specified range (for example, [^a - f]) or set (for example, [^abcdef]).
Nowhere in the examples below it in Help was it explicitly detailed that a user could do this.
In MS Access the # can be substituted for any numeric character such that I could do a WHERE clause:
WHERE
Gift_Date NOT LIKE "####*"
After looking at the above for the [ ] wildcard, it became clear that I could subsitute [0-9] for #:
WHERE
Gift_Date NOT LIKE '[0-9][0-9][0-9][0-9]%'
using single quotes and the % wildcard instead of Access' double quotes and * wildcard.
Just putting this out there for anybody else that is new to SQL, like me.
Regards,
Patrick Briggs,
Pasadena, CA
Clustering, Virtual Sql Servers And Client Network Aliases
Oct 31, 2006I 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.
Server Aliases/linked Servers Cause Remote Queries
Apr 4, 2008Can 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.
RS2k Issue: PDF Exporting Report With Hidden Columns, Stretches Visible Columns And Misplaces Columns On Spanned Page
Dec 13, 2007Hello:
I am running into an issue with RS2k PDF export.
Case: Exporting Report to PDF/Printing/TIFF
Report: Contains 1 table with 19 Columns. 1 column is static, the other 18 are visible at the users descretion. Report when printed/exported to pdf spans 2 pages naturally, 16 on the first page, 3 on the second, and the column widths have been adjusted to provide a perfect page span .
User A elects to hide two of the columns, and show the rest. The report complies and the viewable version is perfect, the excel export is perfect.. the PDF export on the first page causes every fith column, starting with the last column that was hidden to be expanded to take up additional width. On the spanned page, it renders the first column on that page correctly, then there is a white space gap equal to the width of the hidden columns and then the rest of the cells show with the last column expanded to take up the same width that the original 2 columns were going to take up, plus its width.
We have tried several different settings to see if it helps this issue or makes it worse. So far cangrow/canshrink/keep together have made no impact. It is not possible to increase the page size due to limited page size selection availablility for the client. There are far too many combinations of what the user can elect to show or hide to put together different tables to show and hide on the same report to remove this effect.
Any help or suggestion on this issue would be appreciated
SELECT Column Aliases: Refer To Alias In Another Column?
Apr 9, 2008Using 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.
View 11 Replies View RelatedSELECT Column Aliases: Refer To Alias In Another Column?
Apr 10, 2008Using 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 data
Note how the "speed" alias is used in the definition of "acceleration" alias but this doesn't work.
Numeric(4,3)
Sep 6, 2001Hi everyone,
In sql server 7.0 what does in mean when we have numeric(4,3). What does precision and scale means?
Thank you
Transact SQL :: Select And Parse Json Data From 2 Columns Into Multiple Columns In A Table?
Apr 29, 2015I have a business need to create a report by query data from a MS SQL 2008 database and display the result to the users on a web page. The report initially has 6 columns of data and 2 out of 6 have JSON data so the users request to have those 2 JSON columns parse into 15 additional columns (first JSON column has 8 key/value pairs and the second JSON column has 7 key/value pairs). Here what I have done so far:
I found a table value function (fnSplitJson2) from this link [URL]. Using this function I can parse a column of JSON data into a table. So when I use the function above against the first column (with JSON data) in my query (with CROSS APPLY) I got the right data back the but I got 8 additional rows of each of the row in my table. The reason for this side effect is because the function returned a table of 8 row (8 key/value pairs) for each json string data that it parsed.
1. First question: How do I modify my current query (see below) so that for each row in my table i got back one row with 19 columns.
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B
If updated my query (see below)Â and call the function twice within the CROSS APPLY clause I got this error: "The multi-part identifier "A.ITEM6" could be be bound.
2. My second question: How to i get around this error?
SELECT A.ITEM1,A.ITEM2,A.ITEM3,A.ITEM4, B.*, C.*
FROM PRODUCT A
CROSS APPLY fnSplitJson2(A.ITEM5,NULL) B, Â fnSplitJson2(A.ITEM6,NULL) C
I am using Microsoft SQL Server 2008 R2 version. Windows 7 desktop.
T-SQL (SS2K8) :: Select Group On Multiple Columns When At Least One Of Non Grouped Columns Not Match
Aug 27, 2014I'd like to first figure out the count of how many rows are not the Current Edition have the following:
Second I'd like to be able to select the primary key of all the rows involved
Third I'd like to select all the primary keys of just the rows not in the current edition
Not really sure how to describe this without making a dataset
CREATE TABLE [Project].[TestTable1](
[TestTable1_pk] [int] IDENTITY(1,1) NOT NULL,
[Source_ID] [int] NOT NULL,
[Edition_fk] [int] NOT NULL,
[Key1_fk] [int] NOT NULL,
[Key2_fk] [int] NOT NULL,
[Code] .....
Group by fails me because I only want the groups where the Edition_fk don't match...
SQL Server 2014 :: Creating A Table With Updatable Columns And Read-only Columns
May 26, 2015Here is My requirement, I'm not sure if this is possible. Creating table called master like col1, col2 col3, col4 , col5 ...Where Col1, col2 are updatable - this can be done easily
Col3, col4 are columns in another table but these can be just a read only ?? Is this possible ? this is possible with View but not friendly with share point CRUD...Col 5 is a computed column of col 2 and col5 ? if above step can be done then sure this can be done I guess.