SQL Sum Help please
SQL Sum Help please
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 ?
@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 ?
Im using find and only find Total cost 3 timesmaxxis wrote:Wont TOTALCOST be the result of the sum?
My logic is select sum(*) as TOTALCOST ?
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) ?
-
- Registered User
- Posts: 424
- Joined: 28 Jun 2006, 02:00
- Location: Johannesburg
- Contact:
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.
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.
Im using find and only find Total cost 3 timesmaxxis wrote:Wont TOTALCOST be the result of the sum?
My logic is select sum(*) as TOTALCOST ?
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) ?
-
- Registered User
- Posts: 424
- Joined: 28 Jun 2006, 02:00
- Location: Johannesburg
- Contact:
Oh yes now i remember Thanks.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)
Ok one more question
How do i say IF VALUE(money type) < 1 and > -1 then ?
LEFT('20000',2)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)
Just so much simpler
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 reasonSBSP 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 ?
I'd have the code as
@FIELD23NUM = SUM(ISNULL(TOTALCOST,0))
IF ((VALUE(money type) > -1) AND (VALUE(money type) < 1)SBSP wrote:How do i say IF VALUE(money type) < 1 and > -1 then ?
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
-
- Registered User
- Posts: 424
- Joined: 28 Jun 2006, 02:00
- Location: Johannesburg
- Contact:
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.
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 ?
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 '-'
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 ?
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
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:
- Ron2K
- Forum Technical Administrator
- Posts: 9050
- Joined: 04 Jul 2006, 16:45
- Location: Upper Hutt, New Zealand
- Contact:
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)
Kia kaha, Kia māia, Kia manawanui.
Thats weird cus
just above the code i have its been converted
just above the code i have its been converted
Code: Select all
@FIELD23NUM = SUM(ISNULL(TOTALCOST,0))
Aha wait in the second IF statement i forgot to say
set @FIELD23NUMNEW = @FIELD23NUM
instead of
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
set @FIELD23NUMNEW = @FIELD23NUM
Code: Select all
IF @PLUSMINSIGN = '+' AND @FIELD23NUM < 1
set @FIELD23NEW = CAST(@FIELD23NUMNEW AS varchar)
select @FIELD23 = RIGHT('00000000000000000000' + @FIELD23NEW * 100 +
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
-
- Registered User
- Posts: 424
- Joined: 28 Jun 2006, 02:00
- Location: Johannesburg
- Contact: