mySQL view and mathematical formulas 1

[compudude86]

ok, i have a mysql query,

(CREATE VIEW `Book`.`view` AS
SELECT ID, Description, Size, Pack, List, Net, Discount, NetBtl, SRP, Vendor, Date, CONCAT(ROUND((srp-netbtl)*100/srp),'%') as GPM FROM Products

and i need to add in the formulas (List-Discount=Net)and (Net/Pack=NetBtl) along with it, but all attempts have caused syntax errors. also, i want to add dollar signs in front of the numbers on list,net,discount,netbtl,and SRP. any help would be appreciated.

 

[r937]

CREATE VIEW Book.view 
AS
SELECT ID
     , Description
     , Size
     , Pack
     , List
     , Discount
     , List-Discount as Net
     , (List-Discount)/Pack as NetBtl
     , SRP
     , Vendor
     , `Date`
     , CONCAT(ROUND(100.0*
          (srp-((List-Discount)/Pack))/srp),'%') as GPM   FROM Products

r937.com | rudy.ca

 

[compudude86]

ok, i used that query, but now my NetBtl field has a number like 12.000000000 in it, instead of the 12.00 field it was before. how do i fix this?

 

[r937]

format the value in your scripting language

r937.com | rudy.ca

 

[compudude86]

i have only a very basic knowledge of mysql, so i would not know how to do that. i have set the field in the table that the view gets its data from as 19,2 but its not making a difference

 

[r937]

are you running the query in the mysql command line, and then printing the results off your screen to give you your users?

or are you using some sort of front-end application language like php or asp or coldfusion?

r937.com | rudy.ca

 

[compudude86]

i am using something called dataface as my frontend, and i run the query in mysql query browser to create "view" which the frontend gets its information from

 

[r937]

ah well, probably it's not that easy to adjust the number of decimals in the dataface frontend

perhaps try this --

instead of

(List-Discount)/Pack as NetBtl

try

round( (List-Discount)/Pack ,2 ) as NetBtl


r937.com | rudy.ca

 

[compudude86]

ahh, that worked perfectly. thank you very much!!