Page 1 of 1
SQL Sum Help please
Posted: 30 Oct 2007, 14:51
by SBSP
I dont understand the following
@FIELD23NUM = isnull(sum(TOTALCOST),0)
@FIELD23NUM is a declared value and so is TOTALCOST
isnull dunno what the hell that is.
But what does TOTALCOST = to ?
if no where in my SQL script Totalcost is set to be value ?
Its declared as @TOTALCOST as money and thats it ?
Posted: 30 Oct 2007, 14:57
by maxxis
Wont TOTALCOST be the result of the sum?
My logic is select sum(*) as TOTALCOST ?
Posted: 30 Oct 2007, 15:12
by SBSP
maxxis wrote:Wont TOTALCOST be the result of the sum?
My logic is select sum(*) as TOTALCOST ?
Im using find and only find Total cost 3 times
When its declared and the above and at the end where its set to be 0
its weird.
One more question what do i use to mid a string in a Stored proc Eg.
20000 but i only want the first to chars Mystring = mid('20000',1,2) ?
Posted: 30 Oct 2007, 15:13
by FTB_Screamer
I think, isNull returns true if the sum of TOTALCOST columb in whatever table you are trying to query, is NULL(Does not return a value) else it will return 0.
if there isn't a columb in the table named TOTALCOST, then the query might be wrong and should be "@FIELD23NUM = isnull(sum(@TOTALCOST),0)" if you are trying to check if the @TOTALCOST parameter is null or not.
Posted: 30 Oct 2007, 15:19
by SBSP
maxxis wrote:Wont TOTALCOST be the result of the sum?
My logic is select sum(*) as TOTALCOST ?
Im using find and only find Total cost 3 times
When its declared and the above and at the end where its set to be 0
its weird.
One more question what do i use to mid a string in a Stored proc Eg.
20000 but i only want the first to chars Mystring = mid('20000',1,2) ?
Posted: 30 Oct 2007, 15:19
by FTB_Screamer
SBSP wrote:
One more question what do i use to mid a string in a Stored proc Eg.
20000 but i only want the first to chars Mystring = mid('20000',1,2) ?
You can use SUBSTRING ( expression , start , length )
eg. @MyString = SUBSTRING('20000', 1, 2)
Posted: 30 Oct 2007, 15:23
by SBSP
FTB_Screamer wrote:SBSP wrote:
One more question what do i use to mid a string in a Stored proc Eg.
20000 but i only want the first to chars Mystring = mid('20000',1,2) ?
You can use SUBSTRING ( expression , start , length )
eg. @MyString = SUBSTRING('20000', 1, 2)
Oh yes now i remember Thanks.
Ok one more question
How do i say IF VALUE(money type) < 1 and > -1 then ?
Posted: 30 Oct 2007, 15:25
by viceroy
FTB_Screamer wrote:SBSP wrote:
One more question what do i use to mid a string in a Stored proc Eg.
20000 but i only want the first to chars Mystring = mid('20000',1,2) ?
You can use SUBSTRING ( expression , start , length )
eg. @MyString = SUBSTRING('20000', 1, 2)
LEFT('20000',2)
Just so much simpler
SBSP wrote:I dont understand the following
@FIELD23NUM = isnull(sum(TOTALCOST),0)
@FIELD23NUM is a declared value and so is TOTALCOST
isnull dunno what the hell that is.
But what does TOTALCOST = to ?
if no where in my SQL script Totalcost is set to be value ?
Its declared as @TOTALCOST as money and thats it ?
Another thing...why is the ISNULL outside the sum? Bit pointless having it there and opens you up to errors in the calculations...unless you actually need it there for a specific reason
I'd have the code as
@FIELD23NUM = SUM(ISNULL(TOTALCOST,0))
SBSP wrote:How do i say IF VALUE(money type) < 1 and > -1 then ?
IF ((VALUE(money type) > -1) AND (VALUE(money type) < 1)
BEGIN
<do your stuff here>
END
You could use BETWEEN if you wanted the -1 and 1 to be included in the equation.
Would be the same as
IF ((VALUE(money type) >= -1) AND (VALUE(money type) <= 1)
BEGIN
<do your stuff here>
END
Posted: 30 Oct 2007, 15:35
by FTB_Screamer
why is the ISNULL outside the sum
Sum ignores null records so it shouldn't make a difference.
Posted: 30 Oct 2007, 15:38
by viceroy
you think? Good luck to you then
doesn't always
Posted: 30 Oct 2007, 15:41
by SBSP
Thanks People.
Thanks Vice.
VALUE(money type)
Money type is just the type of declare money
as in declare MyValue as money so i take it i remove the brackets ?
Okay wait
I think i have found a Bug In SQL
This is the initial problem.
Code: Select all
--exec upFORMAT_NUMBER_2_DEC @FIELD23NUM,15,1,@FIELD23 output
select @FIELD23 = RIGHT('00000000000000000000' + CAST(CAST(@FIELD23NUM * 100 AS INT) as varchar), 15) + CASE WHEN @FIELD23NUM > 0 THEN '+' ELSE '-'
if @FIELD23NUM is smaller bigger than -1 and smaler than +1
lets say @FIELD23NUM = -0.01 * 100 if you convert it to text it = -1 but if you convert lets say -1000 * 100 to text it = 100000
why is that ?
Posted: 31 Oct 2007, 10:59
by SBSP
Okay I modified the code abit but i get an error saying the table FIELD23 dont allow null's
How can i convert nulls to 0's as in zeros ? if it finds a null value
Oh i know my below code sucks bare with me i'm not good at this
Code: Select all
/*
declare @PLUSMINSIGN as varchar(1)
declare @FIELD23NUMNEW as money
declare @FIELD23NEW as varchar(20)
declare @FIELDREBUILT as varchar(20)
*/
set @PLUSMINSIGN = LEFT(CAST(@FIELD23NUM AS varchar),1)
IF @PLUSMINSIGN = '-' AND @FIELD23NUM > -1
set @FIELD23NUMNEW = @FIELD23NUM * -1
set @FIELD23NEW = CAST(@FIELD23NUMNEW AS varchar)
select @FIELD23 = RIGHT('00000000000000000000' + @FIELD23NEW * 100 + @PLUSMINSIGN,15)
GOTO SKIPREST;
IF @PLUSMINSIGN = '+' AND @FIELD23NUM < 1
set @FIELD23NEW = CAST(@FIELD23NUMNEW AS varchar)
select @FIELD23 = RIGHT('00000000000000000000' + @FIELD23NEW * 100 + @PLUSMINSIGN,15)
GOTO SKIPREST;
--exec upFORMAT_NUMBER_2_DEC @FIELD23NUM,15,1,@FIELD23 output
select @FIELD23 = RIGHT('00000000000000000000' + CAST(CAST(@FIELD23NUM * 100 AS INT) as varchar), 15) + CASE WHEN @FIELD23NUM > 0 THEN '+' ELSE '-' END
SKIPREST:
Posted: 31 Oct 2007, 11:09
by Ron2K
SBSP wrote:How can i convert nulls to 0's as in zeros ? if it finds a null value
Code: Select all
ISNULL(check_expression, replacement_value)
Posted: 31 Oct 2007, 11:24
by SBSP
Thats weird cus
just above the code i have its been converted
Code: Select all
@FIELD23NUM = SUM(ISNULL(TOTALCOST,0))
Posted: 31 Oct 2007, 11:29
by SBSP
Aha wait in the second IF statement i forgot to say
set @FIELD23NUMNEW = @FIELD23NUM
Code: Select all
IF @PLUSMINSIGN = '+' AND @FIELD23NUM < 1
set @FIELD23NEW = CAST(@FIELD23NUMNEW AS varchar)
select @FIELD23 = RIGHT('00000000000000000000' + @FIELD23NEW * 100 +
instead of
Code: Select all
IF @PLUSMINSIGN = '+' AND @FIELD23NUM < 1
set @FIELD23NUMNEW = @FIELD23NUM
set @FIELD23NEW = CAST(@FIELD23NUMNEW AS varchar)
select @FIELD23 = RIGHT('00000000000000000000' + @FIELD23NEW * 100 +
Leme try again
Nha this sucks (This GW Basic style coding dont work)
I'm giving up will give it to someone that knows how.
Wasted 2 days now
Posted: 31 Oct 2007, 16:27
by FTB_Screamer
viceroy wrote:you think? Good luck to you then
doesn't always
shap!!
Posted: 31 Oct 2007, 16:32
by viceroy
FTB_Screamer wrote:viceroy wrote:you think? Good luck to you then
doesn't always
shap!!
