Hi 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.PriorityCode
Has anyone managed to do this another way?
Thanks
I 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?
Is it possible to replace a string value that uses a wildcard? For example, I'm trying to update a field on a table and replace the field value where it starts with a specific two character pattern and remove it from the string. The field is a delimited string and I want to replace any occurrence of ;A0% or ;A0* with a null value. I don't want to replace the entire field with a null value.
Let's say my field value is this:
;00236;08231;06106 Washington DC;06106 CCA Wash DC;05120;A0106;
I want the result to be this:
;00236;08231;06106 Washington DC;06106 CCA Wash DC;05120;
I was trying several variations:
UPDATE myTable SET myField = REPLACE(myField,'XX%','')
I need create a field to store tax rate. I need only 2 decimal points. I defined the field as decimal, precision=5 and scale=2. Does it mean that it can hold value from 0.00 to 999.99?
Right this has to be a Micro$oft mess-up surely...?I'm running SQL 2k standard with SP3. I have a table which I'm tryingto query using a LIKE operator on a varchar field as follows....WHERE dbo.tbl_pm_projects.SeniorManagerID LIKE '%'....In actual fact the % is passed in by the application when the userselects "All managers" from the drop down list used to select theManager to filter by. If they select a manager's name from the listit becomes LIKE 'ajames' or whatever.BUT - the table currently contains 2972 records. If I take out theWHERE clause the SELECT returns all records - fine - but if I put thewhere clause in it returns only 1682!! I thought the % was meant tomatch, and I quote the SQL server Books Online files here; "Any stringof zero or more characters." Anyone explain to me whats going onhere?TIANiall
I am in the midst of writing a query to return a range of product BIN LOCATIONS from a warehouse stock levels program.I know the start and end BIN LOCATIONS for the warehouse, e.g.: Start - #00000, and End - Z10000.However I cannot hard code these into the program, instead I want my operator to designate the first letter of both the start and end BINS locations, and then hit the 'go button'.Towards this end I have declared a couple of variables in my SQL, as follows -
Code: declare @strBin varchar(10) set @strBin = '' if @strBin = '' set @strBin = '#%'
declare @endBin varchar(10) if @endBin = '' set @endBin = 'z%'
My challenge is writing a relevant WHERE clause for my select statement, I have tried the following -
Code: where BINLOCAT.BINLABEL between @strBin and @endBin order by BINLOCAT.BINLABEL
Whilst the clause above does not cause any errors, neither does it return any results.My first thought is that the BETWEEN operator does not accept wildcards. But if this is correct then how do I go about allowing an operator to enter the start, and end BINS without typing the entire BIN string (e.g.: #00000)? I have tried >= @strBin and <= @endBin, but I am having a similar issue with no errors, and no data returned.
Hello, everyone! What are the precision & the scale values in the numeric field for?(as also in int, etc).I need to have a field that is exactly 6 digits in length. However , when I enter the value , in the length column , it defaults to either 5 or 9 , depending on the precision values. Also when I enter the data , that field accepts not only 9(defined with precision of 10), but more than that, till about 15 digits!!…I think I am not clear on the use of precision…what do I need to define the field as so it accepts only 6 digits? Please enlighten me . Thanks in advance!
HiI have a character field (char ot varchar) that I want to force only tocontain numeric characters.Can that be done by way of defining a constraint on the field ?or by any other way in the field/table definition ?What id the syntax ?Anyone have examples ?ThanksDavid Greenberg
I need to insert a null valvue when the user does not impute any text. here is my code If cell_phone.Text = "" Then cell_phone.Text = "dbnull.value" End IfDim mySqlConnection As New SqlConnection mySqlConnection.ConnectionString = ConfigurationManager.ConnectionStrings("Call_ListConnectionString").ConnectionString Dim strSQL As String = "INSERT INTO Employees(Clock_ID, Last_Name, First_Name, Cell_Phone, Home_Phone, Work_Phone, Email, Primary_Day_Phone, Primary_Night_Phone, Blackberry_Number) VALUES ('" & clock_id.Text & "','" & last_name.Text & "','" & first_name.Text & "','" & work_phone.Text & "','" & home_phone.Text & "','" & cell_phone.Text & "','" & email.Text & "','" & prime_day_lst.SelectedValue & "','" & prime_night_lst.SelectedValue & "','" & blackberry.Text & "')"Dim mySqlCommand As New SqlCommand(strSQL, mySqlConnection) mySqlConnection.Open() mySqlCommand.ExecuteNonQuery() mySqlConnection.Close() THanks Mike
Hello All,I'm trying to parse for a numeric string from a column in a table. WhatI'm looking for is a numeric string of a fixed length of 8.The column is a comments field and can contain the numeric string inany positionHere's an example of the values in the column1) Fri KX 3-21-98 5:48 P.M. arrival Cxled ATRI #27068935 3-17-982) wed.kx10/26 Netrez 95860536Now I need to parse through these lines and return only the 8 digitnumbers in itThe result set should be2706893595860536This is what I've done so farDeclare @tmp table(Comments_Txt varchar(255))Insert into @tmpselect Comments_Txt from Reservationselect * FROM @tmp where Comments_Txtlike ('%[0-9][0-9][0-9][0-9][0-9][0**9]%')But it returns the entire comments field in the result set. What I needis a way to return just those 8 digits.Any Ideas??Thanks in advance!!!
I have a numeric data field called Price and this has a value of 0.000 in the db. when i create a package to extract this data to a flat file, the value is displayed as .000 what should i do to make it appear as 0.000 in the flat file.
I tried using a derived column expression where i check if the db value is 0.000 and display it as a string "0.000". this works fine if the OLEDB source is a sql command but fails if the OLEDB source is a sql command from variable.
How to get the max length of numeric field in a DataSet? I have a DataSet bound to an Access database. Is it possible to get the maximum length of numeric field of a table in the DataSet? Many fields in the database tables have maximum length values set in ...
HI, Thanks in advance for taking your time to read this post. I am trying to write a SQL query using MS SQL 2005 that will read the value of a field and tell if it is alpha or numeric. I have tried the following but it does not work: select field1 from table1 where left(field1,2)='[0-9]' select field1 from table1 where isnumber(left(field1,2) tried with a =1 at the end and without and =1 at the end the goal is to read through a field and format it so if a field looks like this 12xxx111xx I can change it to look like 12-xxx-111-xx. Any help is greatly apprecaited
how i convert varchar sal field to numeric in query select sum(sal) from emp1 error:the sum or average aggregate operation cannot take a varchar data type as an argument.
I have a field defined as varchar(8) but this field should not contain any letters, needs to be only numbers. How can I validate the data if it contains only numbers? Any ideas?
I am transfering data from a DB2 table to a SQL 2005 table using the OLE DB Source, Data Converstion then the OLE DB Destionation.
So, I have a numeric (Precision 3, Scale 2) field with NULL value in the DB2 table.
I'm trying to transfer these data to a SQL2005 table and I am receiving this error message below:
"[Destination Table TFACIL [18]] Error: There was an error with input column "COMB_OPPT_PRCT" (2865) on input "OLE DB Destination Input" (31). The column status returned was: "The value violated the integrity constraints for the column.". "
The field must accept null because of the APPLICATION ( i can't change it, im not the owner ).
I am putting a SELECT statement together where I need to evaluate a results field, to determine how the color indicator will show on a SSRS report. I am running into a problem when I try to filter out any non-numeric values from a varchar field, using a nested CASE statement.
For example, this results field may contain values of '<1', '>=1', '1', '100', '500', '5000', etc. For one type of test, I need a value of 500 or less to be shown as a green indicator in a report, and any value over that would be flagged as a red. Another test might only allow a value of 10 or less before being flagged with a red.
This is why I setup a CASE statement for an IndicatorValue that will pass over to the report to determine the indicator color. Using CASE statements for this is easier to work with, and less taxing on the report server, if done in SQL Server instead of nested SSRS expressions, especially since a variety of tests have different result values that would be flagged as green or red.
I have a separate nested CASE statement that will handle any of the values that contain ">" or "<", so I am using the following to filter those out, and then convert it to an int value, to determine what the indicator value should be. Here is the line of the script that is erring out"
case when (RESULT not like '%<%') or (RESULT not like '%>%') then CASE WHEN (CONVERT(int, RESULT) between 0 and 500) THEN '2' ELSE '0'
The message I am getting is: Conversion failed when converting the varchar value '<1' to data type int.
I thought a "not like" statement would not include those values for converting to an int, but that does not seem to be working correctly. I did also try moving the not to show as "not RESULT like", and that did not change the message.
How I can filter out non-numeric values before converting the rest of the varchar field (RESULT) to int, so that it is only converting actual numbers?
I have a list of movies that show throughout the year. I would like to assign a unique numeric identifier to each text field.
I have provided some sample data with the output I would like. The Movie_ID in the sample data is just made up, feel free to assign any numeric identifier, preferably of the same length but not a necessity.
Hi there - I would like to share this strip of code with our SQL 2000DBA community. The code below strips all non-numeric characters from agiven string field and rebuilds the string. Very simple, but I had tobuild it from scratch due the lack of info on this specific matter. Iam sure there are better solutions out there, although I will be gladif this script can help anyone. Feel free to modify and comment itback.Regards,Rubem Linn JuniorMCSE, .NET developerWeb Apps Specialist------------------------------------------------------- BEGIN---------------------------------------------------DECLARE @String_Length AS INTEGER -- Length of the given stringDECLARE @Original_String as NVARCHAR(50) -- The field to stripnon-numeric charsDECLARE @counter as integer -- simple counter variableDECLARE @Stripped_String as nvarchar(50) -- The field after beenstripped-- Get the length of the field (string) to be parsedSELECT @String_Length = len(someStringField) FROM SomeTable WHEREFilterID = 001-- Get the field (string) to be parsedSELECT @Original_String = someStringField FROM SomeTable WHEREFilterID = 001-- Set counter variable to 1SELECT @counter = 1-- Reset this variableSELECT @Stripped_String = ''-- Initiate loop from 1 to the Length of the given stringWHILE (@counter) <= @String_LengthBEGIN-- Check if the char in the lap is numericif substring(@Original_String,@counter,1) LIKE '[0-9]'BEGIN-- Load this variable with the non-numeric-- data stripped from the original stringselect @Stripped_String = @Stripped_String +substring(@Original_String,@counter,1)END-- Increment the counter by oneselect @counter = @counter + 1END-- Print the original string with all charactersPRINT @Original_String-- Print the numeric data that was stripped outPRINT RTRIM(LTRIM(@Stripped_String))
Hi, Let's say I have 1000 registered users in database table and each of them has numeric ranking value. How can I get the position of each user in comparison to other users ranking value?
Hi,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);
Hi, 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!
I am trying to setup an indicator value for an SSRS report to show green and red values on a report, based on the NRESULT value. The problem I am facing is that I have several different CASE statements that have the same logic, and they are processing just fine. NRESULT is a decimal field, so no conversion should be necessary. I do not know why I am getting the "Arithmetic overflow error converting varchar to data type numeric." error message.
Below is the CASE statement where the error is occurring. It is in the part of the ELSE CASE. The first CASE works just fine when the ELSE CASE is commented out. If I also change the ELSE CASE statement to say "else case when LEFT(NRESULT,1) = '-' then '0'", then it processes fine, too, so it has to be something I am missing something in the check on negative values. I do need the two checks, one for positive and one for negative values, to take place.
case when LEFT(NRESULT,1) <> '-' then --This portion, for checking positive values, of the CASE statement works fine. CASE WHEN LEFT(ROUND(NRESULT,2),4) between 0.00 and 0.49 THEN '2' --Green ELSE CASE WHEN LEFT(ROUND(NRESULT,2),4) > 0.49 THEN '0' --Red ELSE '3' --White END END else case when LEFT(NRESULT,1) = '-' then --This portion, for checking negative values, of the CASE statement is producing the conversion error message.
[code]....
I checked the NRESULT field, and there are not any NULL values in there, either.
Hi 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.
when 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.
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)
