Hello
I need a formula that would run as SQL Query to generate a Price per ounce and inset it in Sub Description field 1 in items Properties.
The formula to calculate price per ounce is as fallows PPO=price/size Since most sizes are in ML we would have to convert ml to OZ ppo=price /(size filed / 29.57)
I have entered size in Description field 2
Thanks for all the help
Robert
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:
BeanSmart website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.