Another Coding error - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Another Coding error

Author

Topic

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-13 : 17:46:20

Hi guys
I have another divide by zero error, but I thought the below code gets rid of the 0 and replaces it with one but obvously not can anyone see were I am going wrong please ?

I keep getting this error Server: Msg 8134, Level 16, State 1
on the below red line but unless my logic is way wrong shouldn't the green line set the @packsize to 1 if it is a 0.

IF @isl = 'G'
SELECT @packsize = (select gsypack from main_product
where prodcode = @prodcode)
IF @packsize = cast(0 as numeric)
SET @packsize = cast(1 as numeric) IF ISNULL(cast(@packsize as varchar(20)),' ') = ' '
begin
SET @error = 'Guernsey packsize does not exsist for this product and will be set to 1'
INSERT INTO itemsales_error
VALUES (@indent, @error)
--set packsize to 1 if there is a null value
SET @packsize = '1'
--update the cases qty and the packsize to the new value
UPDATE itemsales_test
SET packsize = @packsize,
cases_qty = qtysold/packsize
WHERE IDCol = @indent
end
ELSE
-- update the guernsey packsize and the cases qty in the same record as @indent if the packsize has a value
begin
UPDATE itemsales_test
SET packsize = @packsize,
cases_qty = qtysold/packsize
WHERE IDCol = @indent
end

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-13 : 17:57:43

It is very hard to read your code. Could you put code tags around your code to retain the formatting, plus bold the areas that are giving you problems?

Tara Kizer
aka tduggan

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-13 : 18:00:21

sorry I just did Tara I realize it was messy I hope this is better.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2006-06-13 : 18:03:55

Can't you just use a CASE statement?

SET cases_qty = CASE WHEN packsize <> 0 THEN qtysold/packsize ELSE qtysold END

Tara Kizer
aka tduggan

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-13 : 18:12:00

Yeah maybe that is a possibility but I need to determine if the @packsize is null then I definitly need to input the appropriatte error in the itemsales_error table but I wil do what you suggestted on the following Line :

cases_qty = qtysold/packsize

and see if this works.
I just can't understand why mine doesn't though.
thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-06-13 : 19:30:40

[code]
UPDATE itemsales_test
SET packsize = @packsize,
cases_qty = CASE WHEN @packsize IS NULL THEN 0
WHEN @packsize = 0 THEN 0
ELSE qtysold / @packsize
END
WHERE IDCol = @indent[/code]

Peter Larsson
Helsingborg, Sweden

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-13 : 20:44:26

Thanks for the quick responses guys after a thorough investigation I have realised were my problem lies.

I am trying to achieve two nested if statements but am failing miserably with the following highlighted red lines:

SET @indent = @@identity
IF @isl = 'G'
SELECT @packsize = (select gsypack from main_product
where prodcode = @prodcode)
IF ISNULL(cast(@packsize as varchar(20)),'0') = '0'
begin
SET @error = 'Guernsey packsize does not exsist for this product and will be set to 1'
print 'fault'
INSERT INTO itemsales_error
VALUES (@indent, @error)
--update the cases qty and the packsize to the new value
UPDATE itemsales_test
SET packsize = @packsize,
cases_qty = CASE WHEN packsize <> 0 or packsize <> null THEN qtysold/packsize ELSE qtysold END
WHERE IDCol = @indent
end
ELSE
-- update the guernsey packsize and the cases qty in the same record as @indent if the packsize has a value begin
UPDATE itemsales_test
SET packsize = @packsize,
cases_qty = CASE WHEN packsize <> 0 THEN qtysold/packsize ELSE qtysold END
WHERE IDCol = @indent
end

no matter what @isl is set to ie it doesn't have to be 'G' the second If statement will run

(I have several @isl variables that each take a packsize from the main_product table depending on that variables value)

Is there anyway I can embed the second if within the first as to only allow the second if to run if the first has been succesful else I am going to have to change this completely.

I also reckon the ISNULL(cast(@packsize as varchar(20)),'0') = '0' is always returning a true as Fault is always printing even though I know @packsize has always got a value in my testbed.

sorry for the mass of questions.

PSamsig
Constraint Violating Yak Guru

384 Posts

Posted - 2006-06-14 : 01:24:08

You are lacking a BEGIN/END then.

SET @indent = @@identity 
IF @isl = 'G'
BEGIN
   SELECT @packsize = (select gsypack from main_product
      where prodcode = @prodcode)
   IF @packsize IS NULL OR @packsize = 0
   begin 
      SET @error = 'Guernsey packsize does not exsist for this product and will be set to 1' 
      print 'fault'
      INSERT INTO itemsales_error
      VALUES (@indent, @error)
      --update the cases qty and the packsize to the new value
      UPDATE itemsales_test
      SET packsize = 1,
        cases_qty = qtysold
      WHERE IDCol = @indent
   end 
   ELSE
   -- update the guernsey packsize and the cases qty in the same record as @indent if the packsize has a value
   begin 
      UPDATE itemsales_test
      SET packsize = @packsize,
         cases_qty = qtysold/@packsize
      WHERE IDCol = @indent
   end
END

EDIT: simply had to fix some more (inlcuding Pesos correction), was hurting my eyes.

-- This one's tricky. You have to use calculus and imaginary numbers for this. You know, eleventeen, thirty-twelve and all those.

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-14 : 17:59:32

That works pretty well thanks Paul my only concern is that the itemsales_error table indent IDCOL is a foreign key to the parent itemsales_test table and if I am getting a packsize that is equal to 0 or null I seem to be getting duplicate 'Guernsey packsize does not exsist for this product and will be set to 1' error message in the itemsales_error table for one individual record in the itemsales_test table and if there is no error I am getting null in the error column for the itemsales_error table.

I know this is badly explained but can anyone advise why these duplicates are occuring please from the above code ?

Jim77
Constraint Violating Yak Guru

440 Posts

Posted - 2006-06-14 : 19:02:56

Being an idiot again I had a statement after the the one above that didn't have a begin ...end... round it and this was the one adding a duplicate @error into the table, thanks for the patience guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources