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 :lol:

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 :evil:

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 +
:roll:


Leme try again :wink:


Nha this sucks (This GW Basic style coding dont work) :lol:

I'm giving up will give it to someone that knows how.
Wasted 2 days now :evil:

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!!
:?: