Unexpected Casting With Sum && Coalesce
Sep 28, 2006
If I have an SQL query which returns an aggregate of several decimal fields
like so:
(sum(COALESCE(myDecimal1, 0)+
sum(COALESCE(myDecimal2, 0)+
sum(COALESCE(myDecimal3, 0)) as MyTotal
I get an rounded integer in MyTotal.
However, if I do the following:
sum(COALESCE(myDecimal1, 0)+
COALESCE(myDecimal1, 0)+
COALESCE(myDecimal1, 0)) as MyTotal
I get a (proper) decimal value.
Does anyone know why the first case returns an Integer?
- Don
View 4 Replies
ADVERTISEMENT
Dec 18, 2008
if I 'print' a MONEY value, or cast a MONEY variable to VARCHAR, it automatically rounds it to two decimal places. Maybe that's a built-in convenience, but I'd like to make it not do that.My workaround right now is to first cast my MONEY variabled to DECIMAL(30,4), and then cast the result to VARCHAR, but I'd like to avoid that step if possible.Consider the following
query:DECLARE @UnitCost MONEY SET @UnitCost = .0167 SELECT @UnitCost,
CAST(@UnitCost AS VARCHAR(30)),
CAST(CAST(@UnitCost AS DECIMAL(30, 4)) AS VARCHAR(30)) -
- Results in: 0.0167, 0.02, 0.0167
View 5 Replies
View Related
Jul 23, 2005
Does anybody know how could I calculate the new date(adding @c to @bor subtracting @b to @c), having for example a declaration like this:DECLARE @a CHAR(12)DECLARE @b DATETIMEDECLARE @c INTSET @b='3.04.04';SET @c=6and to calculate the number of days between two dates with this kindof declaration(@a-@b or @b - @a):DECLARE @a CHAR(12)DECLARE @b DATETIMEDECLARE @c INTSET @a='12.2.04';SET @b='3.04.04';Thanks in advance.
View 1 Replies
View Related
Aug 25, 2004
Can someone please help me with this? I'm losing all my hair trying to figure it out. I've tried all that I can think of. I am getting the Error converting data type varchar to numeric.
Thanks
Sam "O"
cmdInsert = New SqlCommand( "INSERT INTO tmp_blank (sessionid, ssn,job,sun,mon,tue,wed,thu,fri,sat,totcol)
SELECT '" & Session.SessionId & "', ssn,job,sun,mon,tue,wed,thu,fri,sat,
(cast(sun as numeric(4,2)) + cast(mon as numeric(4,2)) +
cast(tue as numeric(4,2)) + cast(wed as numeric(4,2)) +
cast(thu as numeric(4,2)) + cast(fri as numeric(4,2)) +
cast(sat as numeric(4,2))) as totcol from
" & strTableName, conTimeCard)
intUpdateCount = cmdInsert.ExecuteNonQuery()
View 4 Replies
View Related
Jul 3, 2007
Hello,
I'm new to SQL Server and I have to finish a VB.Net program from an employee that has left the company.
I have a table with column 'vanafdatum' witch has datatype char but they stored only dates in this column ( like this: 13/09/2006).
When I try to run the next querie I receive an error message "The conversion of a char data type to a datetime data type resulted in an out of range datetime value".
This is the querie I try to run
SELECT MAX(CAST(vanafdatum AS datetime)) AS vanaf_datum
FROM tarieven
WHERE (vanafdatum <
(SELECT getdate()))
Is there any way I can cast the char datatype to a datetime datatype without getting this error.
I allready tried to change the data type from char to datetime, but doesn't work either.
A possible solution is to erase the column an manually fill in all the fields again in the data type datetime, but that seems a little stupid to do.
So I wonder if there is a solution that can change the data type for all rows in the database without losing any data.
View 5 Replies
View Related
Aug 30, 2006
I've got an ftp task that will be downloading a couple of files each night. today they're called
blah20060830blah
the date value in the middle changes each day. I'm trying to adjust this value with an expression. The expression doesn't want to cast my getdate function into a string that this property will accept. I know i'm missing something stupid.
Thank you
View 23 Replies
View Related
Feb 6, 2007
Hi,
I would like to know if casting is supported in SQL Server Compact Edition. I get an error when i attempt to cast a datetime or bigint value to nvarchar or ntext. CAST(datetime AS nvarchar(15))
Does anybody knows if its supported or how could i cast values?
Thank you
View 1 Replies
View Related
Nov 5, 2007
I have a pulldown menu which has like 4 options
producta productb productc and all
I am trying to retrieve the maximum build number value for these products and display on the gridview as per some other conditions like user selected OS etc
Now clicking on All, I want to display the maximum build number values for productA,ProductB ,ProductC
and I am trying to use coalesce but unable to get my result.
I end up seeing only one value which is the maximum of everything.Instead I want the maximums of A B and C and display them concatenated with commas.
If I do the following with no max funciton, i see all the values but i just want max from each branch.
DECLARE @buildList varchar(100)select @buildlist=COALESCE(@buildList + ', ', '') + convert(varchar(10),build) from results
where branch in ('ProductA','Product B','ProductC')
select @buildList
Please let me know how to do this.
View 8 Replies
View Related
Apr 29, 2008
I have a stored procedure which receives a dynamically built WHERE clause. This is then appended to the sql query within like....'select * from table' +@where_clause.
I know that I am possibly leaving myself open to sql injection so I wanted to find an alternate way of handling this. I stumbled across an article which speaks of using COALESCE as a way to sidestep using dynamic WHERE clauses....http://www.sqlteam.com/article/implementing-a-dynamic-where-clause
In my application the user can enter 1-to-many textboxes as search criteria. What I have been doing is a series of IF statements to determine if each textbox is populated or not and build the WHERE clause accordingly.If tx_lastname.Text <> "" Then
If (InStr(sqlwhere, "where")) Then
sqlwhere = sqlwhere & " AND lname like '" & tx_lastname.Text & "'"
Else
sqlwhere = " where lname like '" & tx_lastname.Text & "'"
End If
End If
If tx_firstname.Text <> "" Then
If (InStr(sqlwhere, "where")) Then
sqlwhere = sqlwhere & " AND fname like '" & tx_firstname.Text & "'"
Else
sqlwhere = " where fname like '" & tx_firstname.Text & "'"
End If
End IfAnd so forth. But the above article seems to insinuate that I provide a static WHERE clause like this...'select * from table WHERE LNAME = COALESCE(@lname, lname) and COALESCE(@fname, fname). And this would handle it.Have any of you ever used this before? This is my first question. My other question is could this particular method be made compatible to fit with the LIKE operator?My user needs to be able to search based on close matches. For instance, if they enter 'JOHN' in the last name field, the results should contain 'JOHN', 'JOHNSON', 'JOHNS', etc.Any help would be appreciated.
View 15 Replies
View Related
May 11, 2008
A few people have mentioned that i should use coalesce in the following statement. the problem is i don't know where i should be using itCan someone show me where i should place it? 1 SELECT (SELECT Location
2 FROM Location_Table
3 WHERE (Property_Table.LocationID = LocationID)) AS Location,
4 (SELECT TypeOfProperty
5 FROM Type_Table
6 WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty, PropertyID, LocationID, TypeID, Title, Description, Price, Bedrooms
7 FROM Property_Table
8 WHERE (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) OR
9 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) OR
10 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) OR
11 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@MaxPrice, 0) IS NULL) AND (TypeID = @TypeID) AND (LocationID = @LocationID) OR
12 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (Price <= @MaxPrice) OR
13 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) AND (Price <= @MaxPrice) OR
14 (NULLIF (@MinPrice, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) AND (Price <= @MaxPrice) OR
15 (NULLIF (@MinPrice, 0) IS NULL) AND (TypeID = @TypeID) AND (LocationID = @LocationID) AND (Price <= @MaxPrice) OR
16 (NULLIF (@TypeID, 0) IS NULL) AND (NULLIF (@LocationID, 0) IS NULL) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR
17 (NULLIF (@TypeID, 0) IS NULL) AND (LocationID = @LocationID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR
18 (NULLIF (@LocationID, 0) IS NULL) AND (TypeID = @TypeID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice) OR
19 (TypeID = @TypeID) AND (LocationID = @LocationID) AND (Price >= @MinPrice) AND (Price <= @MaxPrice)
View 8 Replies
View Related
Jun 21, 2007
I have inherited a db with slowness claims. Last week at a MS seminar where independent SQL Consultant gave presentation on performance gotchas. One of his top 5, do not use coalesce on joins and where clause. Of course it is all over this db. Looking at below was this a bad approach?
WHEREcoalesce(PM.ALTKEYDOC,'x') = coalesce(@AK,PM.ALTKEYDOC,'x')
AND coalesce(PM.FIRSTNAME,'x') LIKE coalesce('%' + @FN + '%',PM.FIRSTNAME,'x')
AND coalesce(PM.LASTNAME,'x') LIKE coalesce('%' + @LN + '%',PM.LASTNAME,'x')
AND coalesce(PM.SEX,'x') = coalesce(@SX,PM.SEX,'x')
AND coalesce(PM.BIRTHDATE,'1/1/1900') = coalesce(@BD,PM.BIRTHDATE,'1/1/1900')
AND coalesce(PM.DIVISION,'x') = coalesce(@DI,PM.DIVISION,'x')
AND PM.STATE = @STATE
ORDER BY LASTNAME
View 2 Replies
View Related
Jan 29, 2004
Hi.
I have a piece of a store procedure I don't quite understand, as follows:
SELECT d.DealReference, d.DealId, d.IllustrationId, ci.ContactId
FROM utDeal d WITH (NOLOCK)
INNER JOIN utContactIllustration ci WITH (NOLOCK)
ON ci.IllustrationId = d.IllustrationId
WHERE d.DealReference LIKE (COALESCE(@DealReference,'%'))
What exactly is the COALESCE function doing here with the parameter?
View 9 Replies
View Related
May 14, 2008
How would i use a coalesece on this function to get null. if i use coalesce(xxxxx,0). If there is nothing in there it returns a blank space but i need to put null in there
cast([DPVisionPlan] as nvarchar(255)) [DPVisionPlan],
View 10 Replies
View Related
Oct 4, 2013
I'm new to sql server. I googled the use of coalesce and find out it is another words a replace of ISNULL.I came across a piece of code posted in the forum about the different uses of coalesce.
use adventureworks
DECLARE @DepartmentName VARCHAR(1000)
SELECT @DepartmentName = COALESCE(@DepartmentName,'') + Name + ';'
FROM HumanResources.Department
WHERE (GroupName = 'Executive General and Administration')
[code]...
View 4 Replies
View Related
Oct 17, 2005
what the hell does that mean!? how do i use it and where?
View 20 Replies
View Related
Mar 11, 2008
Here is my statement
COALESCE (Reg_HomeAdd1, N'') + N', ' + COALESCE (Reg_HomeAdd2, N'') + N', ' + COALESCE (Reg_HomeAdd3, N'') + N', ' + COALESCE (Reg_HomeAdd4, N'')
if one of the fields is null how do I stop two commas showing
ie
The Nook,West Street,,Townsville
View 3 Replies
View Related
Sep 18, 2006
I am having a problem with syntax. I am trying to sum a column where some of the values will be null and because I want to include the rows where the column may be null I am attempting to coalesce to zero.
Below is my sample:
SELECT *
FROM dbo.Student w
LEFT JOIN dbo.StudentDailyAbsence q ON q.StudentID = w.StudentID
Group BY q.StudentID
Having
(SUM(Coalesce(q.AbsenceValue),0) = 0.00)
COALESCE(SUM(q.AbsenceValue) = 0.00,0)
I have tried using the coalesce statement a couple of ways with no resolution, pls help!!
View 5 Replies
View Related
Oct 10, 2005
Hi,I've come across a problem that requires i cast a string to a date, had a go but can't get it to work properly.SELECT EventID, EventName, EventShortDesc, EventLink, EventStartDateFROM dbo.tbl_EventWHERE (CAST EventStartDate AS DATE) > GetDate()ORDER BY EventStartDate DESCBasically i only want events which haven't expired (were before the current date) to be returned. EventStartDate is held as an varchar(10) therefore needs to be cast before i can compare. Is this the best way or is there a better one? If it is how do i get it to work?Any help would be great thanks,drazic19
View 2 Replies
View Related
Feb 18, 2008
Hi All
My brain has truned to mush at the moment.
:(
I want to sort on a char(5) column with data in it that looks like
01/07
05/02
06/01
12/01
07/98
so it comes out in the human date order.
e.g
07/98
06/01
12/01
05/02
01/07
--
David
View 6 Replies
View Related
Nov 13, 2007
Hi all,
I'm trying Insert data using INSERT INTO kind of like below
INSERT INTO table1(
col1
col2
col3
col4)
SELECT
col1
col2
col3
col4
FROM
Server.DB.table2
say, col3 from table 1 is numeric (28,8) and col3 from table 2 is float.
how do I cast this so it works??
thank you!
View 9 Replies
View Related
Apr 6, 2006
Just a question about multi casting.
If I create a copy of a data set using a multi cast, do operations on one of the output sets effect the other output set?
For example, in one ouput set I'm setting a column to NULL - this is actually updating the other set as well. Is this meant to happen?
Thanks!!
View 1 Replies
View Related
Jun 18, 2007
Can anyone see what's wrong with this transformation?
((DT_STR)([IN-DLN]))
I'm trying to cast DLN as a string, but I keep getting a parsing error in the derived column tranformer...
Thanks!
Jim Work
View 7 Replies
View Related
Sep 27, 2006
Hi,I have the following table with some sample values, I want to return the first non null value in that order. COALESCE does not seem to work for me, it does not return the 3rd record. I need to include this in my select statement. Any urgent help please.Mobile Business PrivateNULL 345 NULL4646 65464 65765NULL 564654654 564 6546I want the following as my results:Number3454646564654654Select COALESCE(Mobile,Business,Private) as Number from Table returns:3454646654654 (this is a test to see if private returns & it did with is not null but then how do i include in my select statement to show any one of the 3 fields)select mobile,business,private where private is not null returns:657655646546thanks
View 1 Replies
View Related
Feb 6, 2008
Hi everybody,
I have a stored procedure that creates some temporary tables and in the end selects various values from those tables and returns them as a datatable. when returning the values, some fields are derived from other fields like percentage sold. I have it inside a Coalesce function like Coalesce((ItemsSold/TotalItems)*100, 0) this function returns 0 for every row, except for one row for which it returns 100. Does that mean for every other row, the value of (ItemSold/TotalItems)*100 is NULL ? if so, how can I fix it ? any help is greatly appriciated.
devmetz
View 4 Replies
View Related
Jun 19, 2008
Hi All
I have a problem in making out why Coalese is considered to be better than ISNULL .According to my investigation Coalesce Returns the data type of expression with the highest data type precedence.If for eg I have declared that I want to return the value only upto 3 char then why will I use Coalesce and override my requirement.Anyone with clear concept about this plz explain.
DECLARE @v1 VARCHAR(3)DECLARE @i1 INT
SELECT ISNULL(@i1, 15.00) /2,
COALESCE(@i1 , 15.00) /2,
ISNULL(@v1, 'Teaser #2'),
COALESCE(@v1, 'Teaser #2')
The result will be
7
7.500000
Tea
Teaser #2
thanks in advance
View 8 Replies
View Related
Aug 7, 2001
Hi guys,
Do you have any idea why COALESCE function gives timeout in SQL2000 and works in SQL7.0
for ex.
in SQL7.0 this statement works fine in one of my SP
ROUND(COALESCE(ABS((SELECT SUM(Amount)))))
but in SQL2000 my SP is not working and my program gives timeout. But, if I change to ISNULL it works fine. Any clues on this issue?
This problem was there in SQL65 with ISNULL and then we have changed to COALESCE. Now, again repeated in 2000.
View 1 Replies
View Related
Sep 8, 2004
hi,
my question is about using coalesce function in SQLServer. This function brings up the first not null value. my problem is i cannot get the corresponding field name. this is what i use
SELECT COALESCE(Stop1, Stop2, Stop3, ...., Stop10)
FROM ...
WHERE ProjectID = #URL.ProID#
it returns,
(no column name)
----------------
1 somebody@somthing.com
the fields in the DB goes like stop1, stop2, stop3,...
so, i need to get which stop i am .thanks in advance.
View 1 Replies
View Related
Jun 10, 2008
I'm still getting error messsage that state "encountered dividing by zero:
Is there a problem with my statement?
SELECT COALESCE (SUM(CASE WHEN ResDrec_MbrQ = 'Y' THEN 1.0 ELSE 0.0 END) * 1.0 / (SUM(CASE WHEN ResDrec_MbrQ = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResDrec_PnaltyBox = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResDrec_UnesryTrans = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResDrec_MbrAvoid = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResDrec_RefTrans = 'Y' THEN 1.0 ELSE 0.0 END)
+ (SUM(CASE WHEN Advoc_ResDrec = 'No' THEN 1.0 ELSE 0.0 END) - (SUM(CASE WHEN ResDrec_MbrQ = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResDrec_PnaltyBox = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResDrec_UnesryTrans = 'Y' THEN 1.0 ELSE 0.0 END)
+ SUM(CASE WHEN ResDrec_MbrAvoid = 'Y' THEN 1.0 ELSE 0.0 END) + SUM(CASE WHEN ResDrec_RefTrans = 'Y' THEN 1.0 ELSE 0.0 END)))), 0)
AS ResDrec_MbrQ
FROM limiaca.TPhones_Oct
View 1 Replies
View Related
Feb 9, 2007
hello,
from one of my solution, i havent had time untill now to ask detail what is this coalesce and nullif? Is nullif just like the isnull function? while coalesce is to replace null? can someone explain base on this eg??
d.LocID>= coalesce(nullif(@LocFrom, ''), d.LocID) and
d.LocID<= coalesce(nullif(@LocTo, ''), d.LocID) and
~~~Focus on problem, not solution~~~
View 9 Replies
View Related
Feb 27, 2007
(COALESCE (MaterialNumber, '') + ' - ' + COALESCE (MaterialName, '') )AS materialDescription
I get an error saying cant covert varchar topsoil to type integer
materialnumber = number or null
materialname = name
how do i get
100 - Dirt
- Top Soil
View 3 Replies
View Related
Mar 11, 2008
Please tell me what's wrong in this query. I am using SQL 2005
It gives too many arguments specified
CREATE PROCEDURE spocsearch
(
@nm nvarchar(30), @ID nvarchar(7), @custid nvarchar(10)
)
AS
SELECT nm, ID, custid, custcode
FROM Customer
WHERE
(ID = COALESCE(@ID,ID) OR ID IS NULL) AND
(nm = COALESCE(@nm,nm ) OR nm IS NULL) AND
(custid = COALESCE(@custid ,custid ) OR custid IS NULL)
View 4 Replies
View Related
May 12, 2006
Hello,
When I declare a VB variable Dim s as Decimal,
I want to cast d like this : 1452,4141,0045,47756544,04
Only with to digits after the ","
How can I perform this
View 5 Replies
View Related
Feb 25, 2005
Can anyone please telkl me what is wrong with this portion of a SQL statement. I have been racking my brain over this and can't seem to get it right...
(CASE playerstats.fgm WHEN 0 THEN 0 ELSE (cast(100.00 * ((cast(SUM(playerstats.fgm)) as Decimal(8,2))/(cast(SUM(playerstats.fga)) as Decimal(8,2)))) as decimal(8,1))) AS fgp
I had it working fine, but when playerstats.fgm was a 0 then I got a divide by 0 error. This was the code when it was working ok as long as no one entered a 0 for fgm
(cast(100.00 * (cast(SUM(playerstats.fgm) as Decimal(8,2))/cast(SUM(playerstats.fga) as Decimal(8,2))) as decimal(8,1))) AS fgp
All I am trying to do is find a percentage... when playerstats.fgm = 0 then the percentage will be 0.
Any help will be much appreciated!!! :confused:
View 1 Replies
View Related
