2. PHPRunner General questions
  3. calculate on calculated fields
This topic is locked

calculate on calculated fields

 		8/17/2007 3:16:53 PM
PHPRunner General questions
F
 farago author

Hi
I need to create three calculated fields, they are the different discounts Barnes and Noble will provide to customers this holiday-
pr_bn is the first calculated discount - it is the list price "pr_list" of the book * the base discount "disc"

pr_list-(pr_list(disc0.01))as pr_bn,

no problem
The other two are:

pr_memb - this is the member price calculated as a percentage of the base discount "pr_bn"

pr_bn-(pr_bn(disc_memb0.01)) AS pr_memb,

disc_ball - this is the discount that the customer recieves

*pr_memb-(pr_memb/(pr_list0.01)) AS disc_ball,,**
Obviously The last two need to be calculated on the results of previous results. That is not working - I have very limited SQL knowledge - in fact am a freakin SQL noob. Sorry for such a naive question -
Peter
Sergey Kornilov admin 8/17/2007

Some databases won't allow you to use calculated field alias in expressions.
In this case you need to use pr_list-(pr_list(disc0.01)) instead of pr_bn.
For example:

pr_list-(pr_list*(disc*0.01))-(pr_list-(pr_list*(disc*0.01))*(disc_memb*0.01)) AS pr_memb,


You definitely want to sipmlify this expression for brevity sake however it should this way.
F
 		farago author 8/18/2007

thanks Serge <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=20563&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />
got it working - not very pretty - used excel style formulas to make it go - - i suspect these are too stringy.

Its clear we are not database experts - ha ha

Is there is someone you might suggest to help us as a contractor? I have a very substantial day rate. <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=20563&image=2&table=forumreplies' class='bbc_emoticon' alt=':D' />
[codebox]pr_list-(pr_list(disc0.01)) as pr_bn,
(((pr_list-(pr_list(disc0.01))))+((pr_list-(pr_list(disc0.01)))-(pr_list-(pr_list(disc0.01)))-((pr_list-(pr_list(disc0.01)))(disc_memb0.01)))) as pr_memb,
(pr_list-((((pr_list-(pr_list(disc0.01))))+((pr_list-(pr_list(disc0.01)))-(pr_list-(pr_list(disc0.01)))-((pr_list-(pr_list(disc0.01)))(disc_memb0.01))))))/pr_list as disc_ball[/codebox]