SQL Sum Help please

SBSP · Post by **SBSP** » 30 Oct 2007, 14:51

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 ?

Post by **maxxis** » 30 Oct 2007, 14:57

Wont TOTALCOST be the result of the sum?

My logic is select sum(*) as TOTALCOST ?

SBSP · Post by **SBSP** » 30 Oct 2007, 15:12

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) ?

FTB_Screamer · Post by **FTB_Screamer** » 30 Oct 2007, 15:13

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.

SBSP · Post by **SBSP** » 30 Oct 2007, 15:19

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) ?

FTB_Screamer · Post by **FTB_Screamer** » 30 Oct 2007, 15:19

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)

SBSP · Post by **SBSP** » 30 Oct 2007, 15:23

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 ?

viceroy · Post by **viceroy** » 30 Oct 2007, 15:25

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

FTB_Screamer · Post by **FTB_Screamer** » 30 Oct 2007, 15:35

why is the ISNULL outside the sum

Sum ignores null records so it shouldn't make a difference.

viceroy · Post by **viceroy** » 30 Oct 2007, 15:38

you think? Good luck to you then

doesn't always

SBSP · Post by **SBSP** » 30 Oct 2007, 15:41

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 ?

SBSP · Post by **SBSP** » 31 Oct 2007, 10:59

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:

Post by **Ron2K** » 31 Oct 2007, 11:09

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)

SBSP · Post by **SBSP** » 31 Oct 2007, 11:24

Thats weird cus

just above the code i have its been converted

Code: Select all

@FIELD23NUM = SUM(ISNULL(TOTALCOST,0))

SBSP · Post by **SBSP** » 31 Oct 2007, 11:29

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

FTB_Screamer · Post by **FTB_Screamer** » 31 Oct 2007, 16:27

viceroy wrote:you think? Good luck to you then

doesn't always

shap!!

viceroy · Post by **viceroy** » 31 Oct 2007, 16:32

FTB_Screamer wrote:
viceroy wrote:you think? Good luck to you then

doesn't always
shap!!