Need help running SQL formula to generate Price Per ounce

First run this to validate the results: SELECT i.ItemLookupCode, i.description, (i.price/(cast(i.subdescription2 as decimal)/29.57)) as ppo, i.subdescription2 as size FROM item i WHERE i.subdescription2 is not null AND i.subdescription2 '' AND cast(i.subdescription2 as decimal) 0

You really do need to validate the above results; I think you may need an additional filter in the where clause to apply an isNumeric test. I'd have just added it, but I don't remember the syntax off the top of my head...

If you like the results, then: TAKE A BACKUP OF YOUR DATABASE Run this UPDATE version of the above SELECT: UPDATE item set subdescription1 = (i.price/(cast(i.subdescription2 as decimal)/29.57)) WHERE subdescription2 is not null AND subdescription2 '' AND cast(subdescription2 as decimal) 0

Remember to TAKE A BACKUP OF YOUR DATABASE before running any UPDATE, INSERT or DELETE statements on your database. Better yet would be to create a test database and run the statements there before doing it in your production data.

Hello

i have tried those buy they do not seem to work.

I have changed the caption of sub description fields but that should not have any affect on the formula any ideas /.

when i run """" SELECT i.ItemLookupCode, i.description, (i.price/(cast(i.subdescription2 as decimal)/29.57)) WHERE subdescription2 is not null AND subdescription2 '' AND cast(subdescription2 as decimal) 0 """ i get "An error occurred while executing query:

Invalid column name 'subdescription2'."

when i run the update version """"" UPDATE item set subdescription1 = (i.price/(cast(i.subdescription2 as decimal)/29.57)) WHERE subdescription2 is not null AND subdescription2 '' AND cast(subdescription2 as decimal) 0 """""""""""" i get

An error occurred while executing query:

The multi-part identifier "i.price" could not be bound.

Thanks for the help

Robert

"Glenn Adams [MVP Retail Mgmt]" wrote: