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
ADVERTISEMENT
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
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
Wildcard To Select All Using Numeric Field
Sep 6, 2007Hi all,I've set up a page with a gridview, and I'm trying to create a query based on three parameters and to display all records when the page loads.I have created the first two parameters no problem, but I'm having problems with the last one.The parameter is to be populated by a dropdownlist, where the selectedvalue is numeric. I have done a search and found that I can't use the '%' wildcard as it is for a string data type.I have read somewhere that to get around this I can use the CHAR() function to convert to a character, I have tried this without success.When creating the ControlParameter the datatype gets set to string, which I think is not working becuase the input has to be an integer for it to conver to char, am I right?This is the query, and it is the @Application parameter that I'm having this problem with. SELECT dbo.Issue.IssueID, dbo.Issue.ReportedBy, dbo.Issue.ShortDescription, dbo.Issue.DateReported, dbo.Issue.Status, dbo.Priority.Description AS Priority,
dbo.Application.Application
FROM dbo.Issue INNER JOIN
dbo.Priority ON dbo.Issue.Priority = dbo.Priority.PriorityCode INNER JOIN
dbo.Application ON dbo.Issue.Application = dbo.Application.ApplicationID
WHERE (dbo.Issue.Status LIKE '%' + @Status) AND (dbo.Issue.AssignedTo = @AssignedTo) AND (CHAR(dbo.Application.ApplicationID) LIKE '%' + @Application)
ORDER BY dbo.Priority.PriorityCodeHas anyone managed to do this another way?Thanks
Get Numeric Result From SELECT Statement
Jun 11, 2008Hi,I have this code following my signature to SELECT a number.I don't know what am I missing, because it always return the number which the variable banID was initialized (32 in this case).The strangest thing is that if I run the statement inside MS SQL SERVER, I get the right result.Any help would be appreciated.Warm Regards,Mário Gamito--// Get inserted user IDint banID = 32 ;SqlConnection myConn = new SqlConnection("Data Source=192.168.1.6; Initial Catalog=db1; User=sa; Password=secret");try{myConn.Open()}catch (Exception e){Console.WriteLine(e.ToString());}try{SqlCommand myCommand = new SqlCommand("SELECT MAX(PublisherID) FROM table1", myConn);banID = Convert.ToInt32(myCommand.ExecuteScalar);}catch (Exception e){ Console.WriteLine(e.ToString());}Response.Write(banID);
View 3 Replies View RelatedSELECT Only If Value Is Numeric From Varchar Field
Oct 24, 2001I have a field with State and Zip (ie; CA94526) which is a varchar field. I have lots of data that is invalid and need SELECT all records that the right(myfield,5) IS NOT Numeric. Can this be done?
Thanks!
Can A Select Be Done On A Varchar Field For Only The Rows That Are Numeric?
Jan 4, 2000I am not sure if this is the right place, but here's my question:
with a field being varchar, can only the rows that are numeric be selected? For example-
ID Data1
1 don
2 jerry
3 3030
4 1234
5 susan
6 4321
Does SQL have an IsNumeric type function that can be used in the where clause?
Don
Select Statment On Column That Can Contain Alpha And Numeric Values
Sep 13, 2007Hi,
I have a little bit of a problem I cannot seem to figure it out. Is it possible to write a Select statement that contains a WHERE column_name > desired_numeric_value
The tricky part it that the column is of CHAR type and can contain numeric grades ranging from 0-100 or the letter I for Incomplete.
My SQL was working perfect when this column contained only numbers as soon as a record with I was added I get the following error:
Character to numeric conversion error
This report will be used to find students who have failing grades. Thanks for any help!
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...
SELECT Query - Different Columns/Number Of Columns In Condition
Sep 10, 2007I am working on a Statistical Reporting system where:
Data Repository: SQL Server 2005
Business Logic Tier: Views, User Defined Functions, Stored Procedures
Data Access Tier: Stored Procedures
Presentation Tier: Reporting ServicesThe end user will be able to slice & dice the data for the report by
different organizational hierarchies
different number of layers within a hierarchy
select a organization or select All of the organizations with the organizational hierarchy
combinations of selection criteria, where this selection criteria is independent of each other, and also differeBelow is an example of 2 Organizational Hierarchies:
Hierarchy 1
Country -> Work Group -> Project Team (Project Team within Work Group within Country)
Hierarchy 2
Client -> Contract -> Project (Project within Contract within Client)Based on 2 different Hierarchies from above - here are a couple of use cases:
Country = "USA", Work Group = "Network Infrastructure", Project Team = all teams
Country = "USA", Work Group = all work groups
Client = "Client A", Contract = "2007-2008 Maint", Project = "Accounts Payable Maintenance"
Client = "Client A", Contract = "2007-2008 Maint", Project = all
Client = "Client A", Contract = allI am totally stuck on:
How to implement the data interface (Stored Procs) to the Reports
Implement the business logic to handle the different hierarchies & different number of levelsI did get help earlier in this forum for how to handle a parameter having a specific value or NULL value (to select "all")
(WorkGroup = @argWorkGroup OR @argWorkGrop is NULL)
Any Ideas? Should I be doing this in SQL Statements or should I be looking to use Analysis Services.
Thanks for all your help!
Character To Numeric Conversion Error- Select Statment On Char Column
Sep 18, 2007Hi guys/ladies I'm still having some trouble formatting a select statement correctly.
I am using a sqldatasource control on an aspx page. It is connecting via odbc string to an Informix database.
Here is my select statement cut down to the most basic elements.
SELECT commentFROM informix.ipr_stucomWHERE (comment > 70)
The column "comment" contains student grades ranging from 0-100 and the letters I, EE, P, F, etc. Therefore the column is of a char type. This is a problem because I cannot run the above statement without hitting an alpha record and getting the following error
"Character to numeric conversion error"
How can I write this statement where it will work in the datasource control and have it only look at numeric values and skip the alpha values?
I have tried case with cast and isnumeric... I don't think that I have the formating correct.
I have also used:
WHERE (NOT (comment = ' I' OR comment = ' EE' OR comment = ' NG' OR comment = ' WP' OR comment = ' WF' OR comment = ' P' OR comment = ' F'))
This works but is very clunky and could possibly break if other letters are input in the future. There has to be a better way.I am sorry for my ignorance and thanks again for your help.
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
How To Select Columns When Columns Change
Aug 13, 2007I have a scenario that reminds me of a pivot table and I am wondering if there is a way to handle this in SQL.
I have four tables. Product Line, Item, Property, and Value.
A Product Line has many items and an item can have many property's and a property can have many values.
I want to select a product line and show all the items with the Property's as column headers and the Values as the data. The thing I am having trouble with is the property's for an item are variable from a few to a whole bunch.
Any help would be appreciated.
Thanks,
vmon
Formatting A Select Of A Numeric Column As Format -9999.9999
Aug 25, 2006scott writes "I am relatively new to SQL Server, but after a couple of hours searching help, I'm stuck.
I am trying to select a numeric column from a table and format it using sql. Desired format "-9999.9999" with leading sign and zeros. IS_NEGATIVE is a function to determine sign. The code below works, but seems inefficient.
Is there a better way?
SELECT
RIGHT(dbo.IS_NEGATIVE(ISNULL(COST,0)),1) + RIGHT(REPLICATE('0',10) + RIGHT(abs(CONVERT(decimal(9,5),COST)),10),10) AS 'PRICE'
FROM ORDERS"
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
Select Permssion On Certain Columns
Dec 20, 2006I know you can restrict/grant select rights to certain columns in SQL Server table. My question is if in a select query, where you list out the columns (select colum1, column2...) that you have select rights on do you also have to have select rights on columns that apear in where clause or the columns in a join? For example say I have select rights on column1 and column2 of test table, but not on column3. If I run a select statement like this: select column1, column2 from test where column3 = 'abc' Will I get a select persmission denied because the query is using column3 in the where clause. David
View 2 Replies View RelatedSELECT Columns Programatically
Jul 31, 2007Hi, I've written a SELECT statement that returns columns dependant on a bitwise parameter @Populate.SELECTCASE WHEN (1 & @Populate) = 1 THEN [Column1] ELSE Null END AS [Column1],CASE WHEN (2 & @Populate) = 2 THEN [Column2] ELSE Null END AS [Column2],CASE WHEN (4 & @Populate) = 4 THEN [Column3] ELSE Null END AS [Column3],CASE WHEN (8 & @Populate) = 8 THEN [Column4] ELSE Null END AS [Column4],etcIs this the most efficient way to acheive this since I am only seeing a small performance gain. It still returns all columns but depending on @Populate will leave some columns with Null values.Any help much appreciated, thanks.
View 7 Replies View RelatedSelect Two Columns At Same Time
Aug 27, 2007
how can i retrieve two columns from sqltable with - seperating the results and a common column name
someth like this
select id ,name from user
id name
1 a
2 b
3 c
i need the result set to be
my_reports
1-a
2-b
3-c
is that possible, if so, could anyone tell me how to do?
Select From Two Different Field Columns.
May 22, 2006OK, i'm sure this is an easy one, but can't seem to figure it out. Basically need to search against two database columns (fields) to see if either contain the work "test". Here is an example of the statement but of course dosn't work correctly:
SELECT * FROM tbl_db WHERE skills LIKE 'test' AND title LIKE 'test'
Please note the AND is incorrect.
What i need to accomplish:
if one record contains 'test' in its skills field then SELECT it.
if one record contains 'test' in the title field the SELECT it.
I need to make sure that is 'test' is in both skills and title fields then only display once.
Thanks,
Jake
Select Columns That May Exist
Jul 16, 2007I am working with several similar tables that for the most part contain the same columns. I need to export these tables to different csv files, and the csv file needs to have all possible column names and blank fields if the column does not exist in the table. Basically a set and subset thing.
for example
TABLE AA has three columns A, B, C
and
TABLE AB has 2 columns B, D
I am working with over 200 tables, and am planning on automating this with c#. I could use the information_schema to create the SQL statement for each tabel but it sems like I should also be able to do this using one select statement.
I want to write a select statement that combines this.
SELECT
[A],
[B],
[C],
'' AS [D]
FROM
[AA]
SELECT
'' AS [A],
[B],
'' AS [C],
[D]
FROM
[AB]
How To Put Select Output Into Different Columns?
Oct 27, 2004I have a table like:
ID Disc
----------------
1 BUSH
2 JOHN
1 GOLE
2 MIKE
I would like output depending on ID to put Disc into two columns. Like:
ID Disc1 Disc2
----------------------------
1 BUSH NULL
2 NULL JOHN
1 GOLE NULL
2 NULL MIKE
Any help will be appreciated. Thanks
ZYT
Select Where LIKE From Multiple Columns
May 20, 2008I am using mySQL and the following query works fine:
SELECT * from listings where name LIKE "%$trimmed%" order by name";
and so does this query:
SELECT * from listings where keywords LIKE "%$trimmed%" order by name";
however, I can't seem to combine the two with an OR statement as this query only returns the results from the first LIKE column
select * from listings where name LIKE "%$trimmed%" or keywords LIKE "%$trimmed%" order by name
I want to be able to search both columns and return a row if the NAME column or the KEYWORDS columns contains a string.
Select Max Int Column And Other Columns
Feb 27, 2015I have three records, with one column that is an identity/PK. I want the last record inserted and the whole record's information from the query, using a where clause to limit the record returned from the table.
I get an error if I do not have the group by added. However, with this query, I still get three records returned.
SELECT max([tablePK])
,[orderGUID]
,[tblPeopleFK]
,[tbl_shippingAddressFK]
,[tblBillingMethodFK]
,[shippingMethod]
[Code] ....
Select Data From Top N Columns
Aug 8, 2005Suppose you have table with many columns and often you need data from first 15 or 20 columns. In that case you have to specify all the columns in your select statement. This procedure will select top N columns you want. All you have to do is to supply table name and number of columns you want
Here is the procedure
Create procedure TopNcolumns (@tableName varchar(100),@n int)
as
Declare @s varchar(2000)
set @s=''
If exists(Select * from information_Schema.tables where table_name=@tablename and table_type='Base Table')
Begin
If @n>=0
Begin
set rowcount @n
Select @s=@s+','+ column_name from information_schema.columns
where table_name=@tablename order by ordinal_position
Set rowcount 0
Set @s=substring(@s,2,len(@s)-1)
Exec('Select '+@s+' from '+@tablename)
End
else
Select 'Negative values are not allowed' as Error
End
else
Select 'Table '+@tableName+' does not exist' as Error
Madhivanan
Failing to plan is Planning to fail
Combine Columns In Select
Aug 20, 2007I would appreciate any help with my following problem... lets say
i have...
select A.firstname + '' + B.lastname as fullname, 'Their Home is ' + A.City + ' ' + (select top 1 C.State from States C where C.City = A.City) as Location
from tableA A, TableB B
Where A.id = b.id
This is not the actual statement but follows the same kinda logic... the problem that i get is that some of the rows in both my fullname column and in my location column show up as null... how would i fix it so for instance even if the state is missing it would still show: their home is LA or if just the last name is available it would show the lastname?
Thank you
Insert First N Columns From Select
Mar 1, 2006I have to insert data from about 30 tables into a single table (Users),to do so i used a cursor and a bit of dynamic sql, this should workfine if the tables have to do the select from had the same number ofcolumns, how ever they don't.I only need the first 5 columns from each the table, in the case wherethey have more than the 5 i need for my 'Users' table i get this error:'An explicit value for the identity column in table 'Users' can only bespecified when a column list is used and IDENTITY_INSERT is ON.'.Is there a way to select only the first five columns from a table, ifnot is there another solution for my problem?My Sql query is the following:DROP TABLE UsersCreate Table Users(ID INT identity PRIMARY KEY,TIPO VARCHAR(255),NOME VARCHAR(255),USERNAME VARCHAR(255),EMAIL VARCHAR(255),GROUPID VARCHAR(255))DECLARE @Table VARCHAR(255)DECLARE @Sql VARCHAR(8000)DECLARE sysCursor CURSORFORSELECT name FROM sysobjects where xtype='U'OPEN sysCursorFETCH NEXT FROM SysCursor into @Tablewhile @@FETCH_STATUS<>-1BEGIN/** INSERE VALORES NA TABELA DE TESTE*/SET @SQL = 'INSERT INTO UsersSELECT * FROM ['+ @Table +']'EXECUTE (@SQL)SET @SQL = 'UPDATE Users SET GROUPID=' + @Table +'WHERE GROUPID IS NULL'EXECUTE (@SQL)print @tableFETCH NEXT FROM SysCursor INTO @TableENDCLOSE sysCursor/** APAGA VALORES INVÁLIDOS DA TABELA DE TESTE*/DELETE FROM Users WHERE TIPO IS NULLDELETE FROM Users WHERE NOME='Nome'DEALLOCATE sysCursorI hope you can give me hand, thank you in advance.
View 8 Replies View RelatedThree Different Select Statements In SP - Cant Get All Columns
Sep 3, 2007
Hi,
I have a SP having three differnet select statements like,
IF @reporttype ='A'
SELECT Column1, column2
FROM table1
IF @reporttype = 'B'
SELECT Column3, column4
FROM table2
IF @reporttype = 'C'
SELECT column5, Column6
FROM table3
Now I need three different reports for all three reporttypes.
If i creating first report for @reporttype A, it is executing fine and giving me the Columns which i need.
But while creating reprots for reporttype B and C, I m getting columns column1 and column2 . Its not displaying Column3, Column4, Column5, Column6.
Can anybody help me with this?
Reading Columns After Select Statement
Apr 28, 2004Afer I am getting a DataSet from SELECT* statement I would like to know the value of each field
How do I do that?