Problem Query Returning Float With Comma
Mar 18, 2004
I to all
i am bilding a web page, using asp and sql server
I have a few querys in the asp script. my problem is that the values from the query results to tables with float fiels, apear with a comma
and want a dot
like area= 23,5 and I would like to have area= 23.5
in the query analyser there is no problem itīs all dots
i have my web aplication running in 3 diferent machines and in 2 of them i dont have this problem. the query results to float fiels apear with a dot
in the 3 machines the database is the same , the odbc conection is similar. i have win xp professional in 2 machines and win 2000 server in other. the machine with this problem has xp pro
something i miss in the IIS...
i am lost
some hint would be very nice
thanks for your time and replay
View 1 Replies
ADVERTISEMENT
Apr 9, 2007
I can't take full credit for this. I want to share this with Jeff Moden who did the important research for this calculation here.
All I did was just adapting some old code according to the mantissa finding Jeff made and optimized it a little
Some test codeDECLARE@SomeNumber FLOAT,
@BinFloat BINARY(8)
SELECT@SomeNumber = -185.6125,
@BinFloat = CAST(@SomeNumber AS BINARY(8))
SELECT@SomeNumber AS [Original],
CAST(@SomeNumber AS BINARY(8)) AS [Binary],
dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Converted],
@SomeNumber - dbo.fnBinaryFloat2Float(CAST(@SomeNumber AS BINARY(8))) AS [Error]
And here is the code for the function.CREATE FUNCTION dbo.fnBinaryFloat2Float
(
@BinaryFloat BINARY(8)
)
RETURNS FLOAT
AS
BEGIN
DECLARE@Part TINYINT,
@PartValue TINYINT,
@Mask TINYINT,
@Mantissa FLOAT,
@Exponent SMALLINT,
@Bit TINYINT,
@Ln2 FLOAT,
@BigValue BIGINT
SELECT@Part = 1,
@Mantissa = 1,
@Bit = 1,
@Ln2 = LOG(2),
@BigValue = CAST(@BinaryFloat AS BIGINT),
@Exponent = (@BigValue & 0x7ff0000000000000) / EXP(52 * @Ln2)
WHILE @Part <= 8
BEGIN
SELECT@Part = @Part + 1,
@PartValue = CAST(SUBSTRING(@BinaryFloat, @Part, 1) AS TINYINT),
@Mask =CASE WHEN @Part = 2 THEN 8 ELSE 128 END
WHILE @Mask > 0
BEGIN
IF @PartValue & @Mask > 0
SET @Mantissa = @Mantissa + EXP(-@Bit * @Ln2)
SELECT@Bit = @Bit + 1,
@Mask = @Mask / 2
END
END
RETURNSIGN(@BigValue) * @Mantissa * POWER(CAST(2 AS FLOAT), @Exponent - 1023)
END
Thanks again Jeff!
Peter Larsson
Helsingborg, Sweden
View 3 Replies
View Related
Jan 2, 2008
Hi
I was using a simple query to insert data in fields of type decimal
INSERT INTO Table (stringValue,intvalue) VALUES ('myString ',
" + numericvalue + ")
a query looked like this
INSERT INTO Table (stringValue,intvalue) VALUES ('myString',125)
I had to make a change to accept float point values ( 0,012 ) so I change the fields from decimal to float in the DB
Now I'm having problems in the query because of the comma. The new query looks like this
INSERT INTO Table (stringValue,intvalue) VALUES ('myString',0,012) so because of the comma the engine see three values instead two
How can write the query to insert this float values?
thanks
View 4 Replies
View Related
Apr 27, 2015
I have one sp which has param name as cordinatorname varchar(max)
In where condition of my sp i passed as
coordinator=(coordinatorname in (select ltrim(rtrim(value)) from dbo.fnSPLIT(@coordinatorname,',')))
But now my promblm is for @coordinatorname i have values as 'coorcinator1', 'coordinato2,inc'
So when my ssrs report taking these values as multiselect, comma seperated coordinator2,inc also has comma already.
View 4 Replies
View Related
Aug 22, 2006
I have this problem of inserting my query into database field. My code is as of below.
The @AVERAGESCORE parameter is derived from
Dim averagescore As Single = (122 * 1 + 159 * 2 + 18 * 3 + 3 * 4 + 0 * 5) / (122 + 159 + 18 + 3 + 0)
and the value returned is (averagescore.toString("0.00"))
However, I have error inserting the averagescore variable into a field of datatype float during the transaction. I have no problems when using non transactional sql insert methods. What could be the problem?
Try
Dim i As Integer
For i = 0 To arraySql.Count - 1
myCommand = New SqlCommand
Dim consolidatedobjitem As ConsolidatedObjItem = arraySql(i)
myCommand.CommandText = sqlStr
myCommand.Connection = myConnection
myCommand.Transaction = myTrans
With myCommand.Parameters
.Add(New SqlParameter("@AVERAGESCORE", consolidatedobjitem.getaveragescore))
End With
myCommand.ExecuteNonQuery()
Next
myTrans.Commit()
myConnection.Close()
Catch ex As Exception
Console.Write(ex.Message)
myTrans.Rollback()
myConnection.Close()
End Try
View 7 Replies
View Related
Nov 9, 2007
Using Flat File Connection Manager, I am specifying Text Qualifier = Double quotes{"}, and i have TXT file with one column for lastname and first name as "LN,FN", and settings are set to comma delimted, now the connectin manager is creating two different columns for LN and FN,
it was never a problem in DTS 2000.
any work around.
Thanks,
View 7 Replies
View Related
Mar 18, 2015
How to convert float to timestamp in single select query
E.g., i have float as 1.251152515236 ,
I want to convert this to datetime and from datetime to timestamp... i.e. 26:11:00
View 1 Replies
View Related
Nov 7, 2006
hi friends,
i am using a text box named comments in my jsp application...when i enter text with some wild characters ...for ex ' technology' (inverted coma)....and i press submit buttonthe page becomes blank....i am using mssql as database......datatype for comments is varchar.....
i don know where the problem lies
please help me to tackle the issue....
thanks in advance
Ganesh BS
View 1 Replies
View Related
Aug 2, 2012
linking two tables together to get an end result
find below the code i have used
The first part of the query provides me with the info i need
SELECT sub.*,
case when rm_sales_band = '2M to 4M' then 'Kirsty' else RM end as rm
into #rmtmp
[Code].....
View 1 Replies
View Related
Jul 23, 2005
Hi,I'n in an environment where I cannot make stored procedures. Now I needto make a query with a subquery in the SELECT part which gives a commaseparated list of results:SELECTp.id,listFunction(SELECT name FROM names WHERE name_parent=p.id) AS'nameList'FROM projects AS pThis query should return something like:1, "john,mike,petra"2, "bob,carl,sandra,peter,etclistFunction is (of course) not (yet) defined. Is this possible withoutthe use of stored procedures?Mike
View 12 Replies
View Related
Jul 23, 2008
I'm trying to query an XML column in SQL. The problem I am having is that when multiple values exist it retuns them all in one field. I'd like to insert a comma between values.
View 5 Replies
View Related
Aug 29, 2007
Hey all, i'm making the pages meta keywords on my site dynamic, and i was wondering is there is a string, for example "Dell 17" Monitor Brand New", that would split it into each word for the meta keywords. example "Dell, 17", Monitor, Brand, New" (and possibly to not put a comma on the last word?)
View 1 Replies
View Related
Jun 28, 2005
Hi, I need a sql that returns the query result as comma seperated list of values, instead of several rows. Below is the scenario... Table Name - Customer Columns - CustomerID, Join DateSay below is the data of Customer table ...CustomerID JoinDate1 04/01/20052 01/03/20033 06/02/20044 01/05/20025 09/07/2005Now i want to retrieve all the customerid's who have joined this year. Below is the query that i use for this case.Select CustomerID from Customer where JoinDate between '01/01/2005' and GetDate()This gives the below result as two rows.CustomerID15But i need to get the result as '1,5' (comma seperated list of resulting values).Any help is highly appreciatedThanks in AdvanceRamesh
View 4 Replies
View Related
Nov 30, 2005
Hi, I need to search a column in my database (varchar:50) that contains a comma delimited string of numbers (i.e. 1, 2, 3, 4, 5, 10). Currently, I am doing the following SQL query:
SELECT * FROM people WHERE clubs_belongs_to LIKE '%1%'
Where 1 is the number I'm searching for. The problem with the query above is that it returns records that contain 1, 10, 11, 12, 13, etc. in the clubs_belongs_to field. I want the query to only return those people who belong to club number 1, not 10, 11, 12, etc. Please help. Thanks in advance. I have tried using IN instead of LIKE, but that didn't seem to get the results I wanted either.
View 4 Replies
View Related
Nov 21, 2006
I have following 2 queries which return different results.
declare @accountIdListTemp varchar(max)
SELECT COALESCE(@accountIdListTemp + ',','') + CONVERT(VARCHAR(10),acct_id)
FROM (SELECT Distinct acct_id
FROM SomeTable) Result
print @accountIdListTemp
The above query return the values without concatenating it.
declare @pot_commaSeperatedList varchar(max)
SELECT DISTINCT acct_id
into #accountIdListTemp
FROM SomeTable
SELECT @pot_commaSeperatedList = COALESCE(@pot_commaSeperatedList + ',','') + CONVERT(VARCHAR(100),acct_id)
FROM #accountIdListTemp
print @pot_commaSeperatedList
drop table #accountIdListTemp
This query returns result as concatenated string of comma separated values.
If i want to get similar result in a single query how can i get it?
View 4 Replies
View Related
Jan 29, 2014
Below is the sample data
create table #sample
(name varchar(100),
id int)
insert into #sample values ('customerid','15339119')
insert into #sample values ('Title','15339119')
insert into #sample values ('firstname','15339119')
insert into #sample values ('prevcr','2146822710')
insert into #sample values ('currcr','2146822710')
insert into #sample values ('brandcode','2146822710')
I need output as attached ...
View 5 Replies
View Related
Dec 20, 2005
Hi All
I am working on a query to get all the datetime values in a column in a table into a comma separated text.
eg.
ColumnDate --------------------------- 2005-11-09 00:00:00.0002005-11-13 00:00:00.0002005-11-14 00:00:00.0002005-11-16 00:00:00.000
I wanted to get something like
2005-11-09, 2005-11-13, 2005-11-14, 2005-11-16
Have just started SQL and hence am getting confused in what I think should be a relatively simple query. Any help will be much appreciated. Thanks
View 1 Replies
View Related
Mar 7, 2008
I am currently working on an application that requires, insertion of the results of a SQL Query in to the field of another table, in the form of a comma separated values.
For example, to explain things in detail:
create table dbo.phone_details
(country varchar(20),
state varchar(30),
list_of_toll_free_numbers text)
insert into dbo,phone_details
values
( 'USA', 'CA', 'select Phone from phone_table where substring(phone, 1, 3) in ('800', '866', '877', '888')' )
The final output I desire is:
country state list_of_toll_free_numbers
---------- ------- -----------------------------------------
USA CA 8009877654, 8665764398, 8776543219
View 8 Replies
View Related
Apr 10, 2006
I have a file which contains comma separated columns. One of columns contains names of companies. Sometimes the names of the companies have a comma as part of the name. For those, the value is surrounded by double-quotes.
But it seems that SSIS ignores the double quotes and ONLY looks for the column separator. This causes my value to be split in half.
Traditionally, I thought parsers that deal with this type of import do not automatically take the first comma following the double-quote as the column separator but instead look for the first comma following the ending quote. (i.e. Look at how Excel performs imports...)
I cannot set the column separator of the column to double-quote comma since only those values that HAVE a comma in them are qualified.
Any ideas?
Here is sample fie content to see what I mean:
342123, Jason, 12345
21, Kim,4567
32.43, John Paul, 1245
23, "Mr. T", 98764
12, "Peter, Paul, Mary", 09643
The last entry should be imported as 12 in the first column, "Peter, Paul, Mary" in the second column and 09643 in the third but instead ends up as 12 in the first, "Peter in second column and Paul, Mary", 09643 in the last.
(Oddly enough, if I remove the first column of numbers the import works like it is supposed.)
View 3 Replies
View Related
Jun 11, 2008
I have the following query.
select top 3 dbo.oncd_incident.open_date,dbo.onca_product.description,dbo.onca_user.full_name,dbo.oncd_incident.incident_id,email, dbo.oncd_contact.first_name,dbo.oncd_contact.last_name,dbo.oncd_contact.contact_id
from dbo.oncd_incident
inner join dbo.oncd_incident_contact on dbo.oncd_incident_contact.incident_id=dbo.oncd_incident.incident_id
inner join dbo.oncd_contact on dbo.oncd_contact.contact_id=dbo.oncd_incident_contact.contact_id
inner join dbo.oncd_contact_email on dbo.oncd_contact_email.contact_id=dbo.oncd_contact.contact_id
inner join dbo.onca_user on dbo.oncd_incident.assigned_to_user_code=dbo.onca_user.user_code
inner join dbo.onca_product on dbo.onca_product.product_code=dbo.oncd_incident.product_code
where dbo.oncd_incident.incident_status_code='CLOSED'
and email is not null
and dbo.oncd_incident.open_date>DateAdd(wk,-2,getdate()) and dbo.oncd_incident.completion_date>=DateAdd(dd,-2,getdate()) and
dbo.oncd_incident.assigned_to_user_code in (select user_code from dbo.onca_user)
order by newid()
I want the query to be executed for each row returned by the sub query.If I use IN keyword it returns top 3 rows for any 3 of the users.But I want top 3 rows to be returned for each of teh user.Please help.
View 6 Replies
View Related
Jul 6, 2007
Hi,
My Select query returing the one column:
Options
1
2
3
Form this Options column I have to check the Checkbox1, checkbox2, checkbox3 out of total 6 checkboxes in asp.net form. I am getting the Options column from database.
How to store that resultset.
Thanks in advance.
View 2 Replies
View Related
Oct 21, 2007
Hi! I have a sql query in stored procedure: SELECT Salutation + ' ' + FirstName + ' ' + LastName AS fullname
Ok, this returns a value if salutation is not null, but if the salutation is null it doesn't return any value, I was thinking if the saluation is null then I would atleast get the firstname and last name. Any help appreciated on this.
View 4 Replies
View Related
Nov 15, 2007
so I know how to use tsql to print a value in just the query, but when I insert a column, I want it to return the value of the ID that was just created. I don't know how to do this and send the value back to asp.net so that I can move on with that value. Can any body help me with this? this is what I have so far. thanks for the help!@title varchar(40) = null,
@intro varchar(max) = null
AS
if @title is null
begin
print 'Title Cannot Be Null'
return 0
end
if @intro is null
begin
print 'Intro Cannot Be Null'
return -1
end
BEGIN
SET NOCOUNT ON;
INSERT INTO dbo.Issue
(Title, Intro)
VALUES
(@title, @intro)
return SCOPE_IDENTITY()
View 3 Replies
View Related
Oct 22, 2001
Greetings!
I need to run a select statement that only returns 50 rows. How do I limit the amount of rows returned? Normally the query will return hundreds of rows but all I need is the first 50 it retrieves. I have looked in the BOL and can only find help with a block cursor not just a query.
Thanks a million
zachary
View 1 Replies
View Related
Oct 24, 2006
how can i get a row number in a query in ms/sql
View 7 Replies
View Related
May 29, 2008
Hello everyone,
I set up a View in SQL Server 2005. The syntax checks ok, however, when I execute it, it doesn't return any data.
This is my Query:
SELECT DATEPART(hh, Time) AS Time, COUNT([Recipient-Address]) AS [CountOfRecipient-Address], ROUND(SUM([Total-bytes]) / 1048576, 2)
AS [SumOfTotal-Bytes]
FROM dbo.TrackingLog
WHERE (RIGHT([Recipient-Address], LEN([Recipient-Address]) - PATINDEX([Recipient-Address], '@')) IN
(SELECT Domains
FROM dbo.Domains))
GROUP BY DATEPART(hh, Time)
The part that I am most concerned about is the WHERE section. If I remove it, I get some data returned. If I don't, obviously I don't get anything back.
Any suggestion would be great.
Thanks
View 4 Replies
View Related
Apr 18, 2006
I want to get the count of rows in the table which match the status. I am writing dynamic query for it..
Create Procedure Dyn_Get_CountByStatus
(
@TableName varchar(200),
@Status int
)
as
Begin
Declare @strQuery varchar(500)
Declare @count int
set @strQuery = 'select count(*) from '+@TableName + 'where status=' + @Status
set @count =exec(@strQuery)
return @count
End
GO
This query is not working. How can get the desired result using dynamic query
View 3 Replies
View Related
Sep 21, 2007
Is there a way to set SQL Server 2005 Express so that I can return null values? For example, the following query will not return any values:
SELECT *
FROM tbl_form_values
where fldVALUE IS NULL;
it does return values with:
SELECT *
FROM tbl_form_values
where fldVALUE = '';
I have an Oracle background and all null values are true nulls not empty strings. I would like to be able to use the first query. Also other functions such as COALESCE work very nice with nulls. I can do the following in Oracle but not in SQL Server:
SELECT fldID, fldMID, fldFID,
COALESCE(fldVALUE, 'n/a');
FROM tbl_form_values
This will return the values in fldVALUE if they are available and n/a for all NULL values.
Any help would be appreciated.
Thanks,
E
View 1 Replies
View Related
Oct 19, 2006
I am having problems with a SQL query.
Table has 10 fields and I need to return them all. The three most importaint, at least for the filter I need are:
id, studentid, date, canceled.
I need to return the last max(date) grater than or equal to @dateparam which is not canceled for each studentid
I have worked out some solutions but am not happy with them. Specially woried about performance when the table grows. I am expecting in full production a table growth of about 3 million records per month.
what would be grate is if there where a way of returning a the coresponding id like in:
select studentid, max(date), related(id) as ids
from tablea
where canceled=0
group by studentid
then I could do:
Select *
from tablea inner join (select studentid, max(date), related(id) as ids
from tablea a
where canceled=0
group by studentid
) b on (a.id=b.ids)
View 5 Replies
View Related
Aug 18, 2006
I'm building a db to collect equip fault data in SQL 2005 and need to modify my query to be able to select/display "ALL" records. I'm currently using a sp to assign a shift to each record and then have a query to select all records by shift. I'd like to add an option to the query to be able to select ALL records, regardless of shift. I've included the sp & query I am currently using. Any help would be appreciated.
Thanks
ALTER PROCEDURE [dbo].[p_dtu_Store_Line_Fault_Data]
-- Add the parameters for the stored procedure here
@AssetID int,
@Timestamp datetime,
@FaultCode int,
@State int
AS
BEGIN
SET NOCOUNT ON;
IF @State = 3
BEGIN
INSERT LineFaultData (FaultCode, AssetID, StartTime, Duration, Shift)
VALUES (@FaultCode, @AssetID, @Timestamp, 0,
CASE WHEN DATEPART(hh,@Timestamp) BETWEEN 7 AND 14 THEN 'DAYS'
WHEN DATEPART(hh,@Timestamp) BETWEEN 15 AND 22 THEN 'AFTERNOONS'
ELSE 'NIGHTS'
END)
END
IF @State <> 3
BEGIN
DECLARE @Count int
SET @Count = (SELECT Count(*) FROM LineFaultData WHERE AssetID = @AssetID AND Duration = 0)
IF @Count <> 0
BEGIN
DECLARE @StartTime datetime
SET @StartTime = (SELECT Top 1 StartTime FROM LineFaultData WHERE AssetID = @AssetID and Duration = 0)
UPDATE LineFaultData
SET Duration = DateDiff(s,@StartTime, @Timestamp)
WHERE AssetID = @AssetID and Duration = 0 and StartTime = @StartTime
END
END
END
SELECT TOP (1000) dbo.LineFaultDescription.Station, dbo.LineFaultData.StartTime, dbo.LineFaultData.Duration, dbo.LineFaultDescription.FaultDescription,
dbo.LineFaultDescription.FaultCategory, dbo.LineFaultData.Shift
FROM dbo.LineFaultDescription INNER JOIN
dbo.LineFaultData ON dbo.LineFaultDescription.FaultCode = dbo.LineFaultData.FaultCode AND
dbo.LineFaultDescription.AssetID = dbo.LineFaultData.AssetID
and (StartTime < '{@End Date}' and StartTime > '{@Start Date}')
WHERE (dbo.LineFaultData.AssetID = {Asset_ID})
AND (dbo.LineFaultData.Shift = '{@Shift}')
ORDER BY dbo.LineFaultData.StartTime DESC
View 4 Replies
View Related
Mar 28, 2008
Happy Friday!
A while since I have posted a question, and this one is probably real easy.
I am trying to store numeric values from a php form in MSSQL 2000 database. However, the columns are set to float and if the value is 1.00, when entered into the table it is saved as 1
If I change the column type to money, the query fails, with an error message of conversion of datatype varchar to datatype money statement terminated.
anybody know what I need to do? do I need to do something in my query to specify that this is NOT varchar data?
View 2 Replies
View Related
Feb 29, 2008
I need some help. I have a function that used to use ADO.NET to return a dataview. Now I'm using linq, and I don't know how to make this work anymore.my original function looked like this: public DataView getIssue() { //do some ADO, return the dataview return ds.Tables["Articles"].DefaultView; } With this I could write Gridview1.dataSource = getIssue();I want to do the same thing with Linq, but I'm running into some trouble:Here's my function now:public DataView getIssue() { var query = from a in db.Articles join i in db.Issues on a.IssueID equals i.IssueID select a; DataView dv = new DataView(); dv = query.asdatatable(); return query; }OK, first off I can't use asdatatable since I am using a join, so I can't make the results of my query a dataview. I can't return the results of my query in this function, something that seemed simple to do in Linq now seems like something that may only be possible in ADO.Thanks for your time
View 5 Replies
View Related
Apr 19, 2001
This question has been posted on the site before but I could not find any resolution....I want to return rows 11 - 20 from a query that returns 100 records without using a cursor or temp table.
The closest query I have found is a query that numbers the rows, but I can't seem to use rownumber in a between clause...
Use Pubs
SELECT emp_id, lname, fname, job_id,
(SELECT COUNT(*) FROM employee e2 WHERE e2.emp_id <= e.emp_id AND e2.job_id = 10) AS rownumber
FROM employee e
WHERE job_id = 10
ORDER BY emp_id
Thanks,
Jim
View 1 Replies
View Related
