Multiply the quantity with -1

Question

Multiply the quantity with -1

itskumar2004

Default port

Points: 1446
More actions
September 29, 2010 at 9:49 am

#227467

Hi all,
I have table with name ABC
Table ABC:
columnname datatype
ID int not null,
quantity int null,
type varchar(20)
insert into abc values(1,2000,'standardorder')
insert into abc values(2,4000,'internalorder')
insert into abc values (3,5000,'returnorder')
The table above tells the id ,quantity and the type of the order..
now my problem is i want to multiply all the returnorder types with minus which i could do with this...
update abc
set quantity=quantity*(-1)
where type='returnorder'
and i need to put this code into my stored procedure ..which we might run this more than once while testing ..if we run more than once the quantity becomes plus again ..Is there any way i could check the column before i multiply it with -1 ,like if the column is already negative i want to leave it like that else multiply with -1..
Thank you

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Cory E. SSCertifiable Points: 7076 More actions · Answer 1

itskumar2004 (9/29/2010)
Hi all,
I have table with name ABC
Table ABC:
columnname datatype
ID int not null,
quantity int null,
type varchar(20)
insert into abc values(1,2000,'standardorder')
insert into abc values(2,4000,'internalorder')
insert into abc values (3,5000,'returnorder')
The table above tells the id ,quantity and the type of the order..
now my problem is i want to multiply all the returnorder types with minus which i could do with this...
update abc
set quantity=quantity*(-1)
where type='returnorder'
and i need to put this code into my stored procedure ..which we might run this more than once while testing ..if we run more than once the quantity becomes plus again ..Is there any way i could check the column before i multiply it with -1 ,like if the column is already negative i want to leave it like that else multiply with -1..
Thank you

I would think the easiest would be to add to the where clause:

AND Quantity > 0

-- Cory

itskumar2004 Default port Points: 1446 More actions · Answer 2

itskumar2004

Default port

Points: 1446

September 29, 2010 at 10:05 am

#1229185

yes ,you are right...

Thanks for the reply.

PaulB-TheOneAndOnly SSC Guru Points: 66773 More actions · Answer 3

itskumar2004 (9/29/2010)
I have table with name ABC
Table ABC:
columnname datatype
ID int not null,
quantity int null,
type varchar(20)
The table above tells the id ,quantity and the type of the order..
now my problem is i want to multiply all the returnorder types with minus which i could do with this...

I would double-check business requirements and implementation logic.

Usually business will ask to see two records for such order, the first one showing the order was served and the second one showing the order was returned.

I agree an easy solution would be to have positive (+) quantity in the server order and negative (-) quantity in the returned order but I wouldn't multiply quantity by -1 in original record. By doing this system will tell affected product come in but, it would not tell product went out first.

Hope this helps.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.