Saturday, February 25, 2012

Adding Integers

The code below has this line
SET @.SOGallons = @.ODTGallons

I need it to add the Current value of @.SOGallons to the newly selected value of @.ODTGallons and set that as the new value of @.SOGallons.

I've tried
SET @.SOGallons = @.SOGallons + @.ODTGallons

SET @.SOGalTemp = @.SOGallons
SET @.SOGallons= @.SOGalTemp + @.ODTGallons

Neither Worked

<CODE>
FROM [CSITSS].[dbo].[Orderdt] as ODT LEFT OUTER JOIN [CSITSS].[dbo].[Orddtcom] as OCOM
ON ODT.[Companydiv] = OCOM.[Companydiv] AND ODT.[OrderNumber] = OCOM.[OrderNumber] AND
ODT.[Sequence] = OCOM.[Sequence] WHERE ODT.[Companydiv]= 'GLPC-TRANS' AND ODT.[OrderNumber] = @.OrdNum AND
([LineType] = 'IP' OR [LineType] = 'SO' OR [LineType] = 'DL' OR [LineType] = 'PU')

OPEN TC1

FETCH NEXT FROM TC1 INTO @.LT, @.ODTGallons, @.ODTComm
WHILE @.@.FETCH_STATUS=0
BEGIN
IF @.LT = 'SO'
BEGIN
SET @.SplitTest = 1
SET @.SOGallons = @.ODTGallons
IF @.SOGallons > 0
BEGIN
SET @.SOGalTest = 1
END
ELSE
BEGIN
SET @.SOGalTest = 0
END
IF @.SplitTest <> @.SOGalTest
BEGIN
SET @.SOGalTest = 0
END
END
ELSE
BEGIN
SET @.SOGalTest = 1
END
FETCH NEXT FROM TC1 INTO @.LT, @.ODTGallons, @.ODTComm
END
CLOSE TC1
DEALLOCATE TC1</CODE>LineType Gallons Commodity
DL 4000 #2 ULSD DYED
IP 7000 87 NL / ETH
PU 4000 #2 ULSD DYED
SO 7000 87 NL / ETH

There may be multiple lines of any of the above line types.

IP = Initial Pickup
PU = Additional Pickup
SO = Stop Off
DL = Final Delivery

I need to know if all of the gallons that were picked up where delivered

IP + PU = SO + DL

I'm doing a check on the validity of the commodity type as well but with the forums helps we figured that one out yesterday.|||First, your issue is NULL issue.
You said
I've tried
SET @.SOGallons = @.SOGallons + @.ODTGallons

SET @.SOGalTemp = @.SOGallons
SET @.SOGallons= @.SOGalTemp + @.ODTGallons

But before you use @.SOGallons, you need to initialise it. Otherwise it stays as NULL. And whatever value added to it becomes NULL. This is why it failed. Say, insert this line "SET @.SOGallons = 0" before "SET @.SOGallons = @.SOGallons + @.ODTGallons".

Next, in your WHILE Loop, put "SET @.SplitTest = 1" before the loop started as it is a constant.|||Hey,

Thanks.

I know VB fairly well but am completely new to SQL as of about 3 weeks ago. I always seem to know what I want to do but am continually making small syntax errox that trip me up.

No comments:

Post a Comment