calc on the fly problem

You cannot have two as offer_total.
Give the last expression another alias.

Hi Sergey.
I know - I just added the first "offer_total" (the one in brackets) to demonstrate that it works until here as finalized expression.

Any other tip or hint?
Thanks in advance

Test yuor query in phpMyAdmin - this may give you more meaningful error message.

Well, works fine and correct in phpmyadmin!
I've played around with it - if I ad i.e. "...+ 5 as offer_total" after "...100 * `offer_disc_4`)" or any other numeric combination it works fine.

But as soon as I ad some maths including a `field_name`it fails?!?!
is anybody familiar with this bug?

PHPRunner simply passes SQL query to the database. If it works in phpMyAdmin it should work in PHPRunner as well.
Post the exact SQL query here that works in phpMyAdmin.
You can also post the script that creates this table in MySQL.

CREATE TABLE `ges_dwh_v2` (

`id` int(11) NOT NULL auto_increment,

`id_user` varchar(6) default NULL,

`id_group` varchar(10) default NULL,

`flag_comp` varchar(20) default NULL,

`flag_con` varchar(20) default NULL,

`flag_inq` varchar(20) default NULL,

`flag_inqdet` varchar(20) default NULL,

`flag_rep` varchar(20) default NULL,

`flag_bu_re` varchar(20) default NULL,

`flag_pass_act` varchar(20) default NULL,

`act_date` date default NULL,

`act_inq_status` varchar(20) default NULL,

`act_resp` varchar(20) default NULL,

`comp_enter_date` date default NULL,

`comp_name` varchar(100) default NULL,

`comp_typ` varchar(30) default NULL,

`comp_industry` varchar(30) default NULL,

`comp_prod_category_1` varchar(55) default NULL,

`comp_prod_device_1` varchar(55) default NULL,

`comp_prod_branch_1` varchar(55) default NULL,

`comp_distributes` mediumtext,

`comp_employees` varchar(15) default NULL,

`comp_found_source` varchar(50) default NULL,

`comp_crm_status` varchar(10) default NULL,

`comp_complete` char(3) default NULL,

`compadr_adress_1` varchar(50) default NULL,

`compadr_adress_2` varchar(50) default NULL,

`compadr_adress_3` varchar(50) default NULL,

`compadr_zip` int(11) default NULL,

`compadr_city` varchar(30) default NULL,

`compadr_country` varchar(30) default NULL,

`compadr_region` varchar(25) default NULL,

`compadr_subregion` varchar(35) default NULL,

`compadr_tel` varchar(30) default NULL,

`compadr_fax` varchar(30) default NULL,

`compadr_email` varchar(40) default NULL,

`compadr_website` varchar(100) default NULL,

`compadr_complete` char(3) default NULL,

`con_mr_mrs` varchar(4) default NULL,

`con_first_name` varchar(20) default NULL,

`con_last_name` varchar(30) default NULL,

`con_department` varchar(40) default NULL,

`con_function` varchar(40) default NULL,

`con_phone` varchar(30) default NULL,

`con_mobil` varchar(30) default NULL,

`con_fax` varchar(30) default NULL,

`con_email` varchar(40) default NULL,

`con_nl_status` varchar(15) default NULL,

`con_note` text,

`con_found_source` varchar(30) default NULL,

`con_camp_part` varchar(30) default NULL,

`con_complete` char(3) default NULL,

`inq_date` date default NULL,

`inq_forward` varchar(6) default NULL,

`inq_per` varchar(20) default NULL,

`inq_orig` varchar(20) default NULL,

`inq_note` tinytext,

`inq_multi` char(3) default NULL,

`inq_task` varchar(15) default NULL,

`inq_priority` varchar(6) default NULL,

`inq_complete` char(3) default NULL,

`inqdet_series_1` varchar(30) default NULL,

`inqdet_type_1` varchar(20) default NULL,

`inqdet_subtype_1` varchar(20) default NULL,

`inqdet_category_1` varchar(10) default NULL,

`inqdet_family_1` char(3) default NULL,

`inqdet_area_1` char(3) default NULL,

`inqdet_units_1` int(11) default NULL,

`inqdet_series_2` varchar(30) default NULL,

`inqdet_type_2` varchar(20) default NULL,

`inqdet_subtype_2` varchar(20) default NULL,

`inqdet_category_2` varchar(10) default NULL,

`inqdet_family_2` char(3) default NULL,

`inqdet_area_2` char(3) default NULL,

`inqdet_units_2` int(11) default NULL,

`inqdet_series_3` varchar(30) default NULL,

`inqdet_type_3` varchar(20) default NULL,

`inqdet_subtype_3` varchar(20) default NULL,

`inqdet_category_3` varchar(10) default NULL,

`inqdet_family_3` char(3) default NULL,

`inqdet_area_3` char(3) default NULL,

`inqdet_units_3` int(11) default NULL,

`inqdet_application` mediumtext,

`inqdet_purpose` varchar(15) default NULL,

`inqdet_note` mediumtext,

`inqdet_complete` char(3) default NULL,

`offer_date` date default NULL,

`offer_handled` varchar(25) default NULL,

`offer_letter` text,

`offer_series_1` varchar(30) default NULL,

`offer_type_1` varchar(25) default NULL,

`offer_subtype_1` varchar(25) default NULL,

`offer_artnum_1` int(8) default NULL,

`offer_category_1` varchar(10) default NULL,

`offer_family_1` char(3) default NULL,

`offer_area_1` char(3) default NULL,

`offer_units_1` int(11) default NULL,

`offer_disc_1` char(3) default NULL,

`offer_price_1` varchar(7) default NULL,

`offer_reduction_1` varchar(20) default NULL,

`offer_total_1` varchar(20) default NULL,

`offer_series_2` varchar(30) default NULL,

`offer_type_2` varchar(25) default NULL,

`offer_subtype_2` varchar(25) default NULL,

`offer_artnum_2` int(8) default NULL,

`offer_category_2` varchar(10) default NULL,

`offer_family_2` char(3) default NULL,

`offer_area_2` char(3) default NULL,

`offer_units_2` int(11) default NULL,

`offer_disc_2` char(3) default NULL,

`offer_price_2` varchar(7) default NULL,

`offer_reduction_2` varchar(20) default NULL,

`offer_total_2` varchar(20) default NULL,

`offer_series_3` varchar(30) default NULL,

`offer_type_3` varchar(25) default NULL,

`offer_subtype_3` varchar(25) default NULL,

`offer_artnum_3` int(8) default NULL,

`offer_category_3` varchar(10) default NULL,

`offer_family_3` char(3) default NULL,

`offer_area_3` char(3) default NULL,

`offer_units_3` int(11) default NULL,

`offer_disc_3` char(3) default NULL,

`offer_price_3` varchar(7) default NULL,

`offer_reduction_3` varchar(20) default NULL,

`offer_total_3` varchar(20) default NULL,

`offer_series_4` varchar(30) default NULL,

`offer_type_4` varchar(25) default NULL,

`offer_subtype_4` varchar(25) default NULL,

`offer_artnum_4` int(8) default NULL,

`offer_category_4` varchar(10) default NULL,

`offer_family_4` char(3) default NULL,

`offer_area_4` char(3) default NULL,

`offer_units_4` int(11) default NULL,

`offer_disc_4` char(3) default NULL,

`offer_price_4` varchar(7) default NULL,

`offer_reduction_4` varchar(20) default NULL,

`offer_total_4` varchar(20) default NULL,

`offer_series_5` varchar(30) default NULL,

`offer_type_5` varchar(25) default NULL,

`offer_subtype_5` varchar(25) default NULL,

`offer_artnum_5` int(8) default NULL,

`offer_category_5` varchar(10) default NULL,

`offer_family_5` char(3) default NULL,

`offer_area_5` char(3) default NULL,

`offer_units_5` int(11) default NULL,

`offer_disc_5` char(3) default NULL,

`offer_price_5` varchar(7) default NULL,

`offer_reduction_5` varchar(20) default NULL,

`offer_total_5` varchar(20) default NULL,

`offer_series_6` varchar(30) default NULL,

`offer_type_6` varchar(25) default NULL,

`offer_subtype_6` varchar(25) default NULL,

`offer_artnum_6` int(8) default NULL,

`offer_category_6` varchar(10) default NULL,

`offer_family_6` char(3) default NULL,

`offer_area_6` char(3) default NULL,

`offer_units_6` int(11) default NULL,

`offer_disc_6` char(3) default NULL,

`offer_price_6` varchar(7) default NULL,

`offer_reduction_6` varchar(20) default NULL,

`offer_total_6` varchar(20) default NULL,

`offer_total_pos1` varchar(20) default NULL,

`offer_total_pos2` varchar(20) default NULL,

`offer_total_pos3` varchar(20) default NULL,

`offer_total` varchar(20) default NULL,

`offer_tax` varchar(20) default NULL,

`offer_pay` varchar(35) default NULL,

`offer_pay_opt` varchar(10) default NULL,

`offer_disc` varchar(50) default NULL,

`offer_inco` varchar(15) default NULL,

`offer_inco_opt` varchar(40) default NULL,

`offer_val` varchar(20) default NULL,

`offer_val_opt` varchar(10) default NULL,

`offer_deltim` varchar(15) default NULL,

`offer_deltim_opt` varchar(15) default NULL,

`offer_gtc` varchar(4) default NULL,

`offer_gtc_opt` varchar(210) default NULL,

`offer_note` text,

`rep_date` date default NULL,

`rep_subject` varchar(20) default NULL,

`rep_text` mediumtext,

`rep_upload` varchar(20) default NULL,

`rep_complete` char(3) default NULL,

`query_note` tinytext,

`query_complete` tinyint(4) default NULL,

PRIMARY KEY (`id`)

) TYPE=InnoDB AUTO_INCREMENT=1348 ;
-------------------------------------------------------------------------------------

[color=#FF0000]now the query-code that works in phpmyadmin and doesn't in phprunner:

-------------------------------------------------------------------------------------
select `id`,

`id_user`,

`id_group`,

`flag_comp`,

`flag_con`,

`flag_inq`,

`flag_inqdet`,

`flag_rep`,

`flag_bu_re`,

`flag_pass_act`,

`act_date`,

`act_inq_status`,

`act_resp`,

`comp_enter_date`,

`comp_name`,

`comp_typ`,

`comp_industry`,

`comp_prod_category_1`,

`comp_prod_device_1`,

`comp_prod_branch_1`,

`comp_distributes`,

`comp_employees`,

`comp_found_source`,

`comp_crm_status`,

`comp_complete`,

`compadr_adress_1`,

`compadr_adress_2`,

`compadr_adress_3`,

`compadr_zip`,

`compadr_city`,

`compadr_country`,

`compadr_region`,

`compadr_subregion`,

`compadr_tel`,

`compadr_fax`,

`compadr_email`,

`compadr_website`,

`compadr_complete`,

`con_mr_mrs`,

`con_first_name`,

`con_last_name`,

`con_department`,

`con_function`,

`con_phone`,

`con_mobil`,

`con_fax`,

`con_email`,

`con_nl_status`,

`con_note`,

`con_found_source`,

`con_camp_part`,

`con_complete`,

`inq_date`,

`inq_forward`,

`inq_per`,

`inq_orig`,

`inq_note`,

`inq_multi`,

`inq_task`,

`inq_priority`,

`inq_complete`,

`inqdet_series_1`,

`inqdet_type_1`,

`inqdet_subtype_1`,

`inqdet_category_1`,

`inqdet_family_1`,

`inqdet_area_1`,

`inqdet_units_1`,

`inqdet_series_2`,

`inqdet_type_2`,

`inqdet_subtype_2`,

`inqdet_category_2`,

`inqdet_family_2`,

`inqdet_area_2`,

`inqdet_units_2`,

`inqdet_series_3`,

`inqdet_type_3`,

`inqdet_subtype_3`,

`inqdet_category_3`,

`inqdet_family_3`,

`inqdet_area_3`,

`inqdet_units_3`,

`inqdet_application`,

`inqdet_purpose`,

`inqdet_note`,

`inqdet_complete`,

`offer_date`,

`offer_handled`,

`offer_letter`,

`offer_series_1`,

`offer_type_1`,

`offer_subtype_1`,

`offer_artnum_1`,

`offer_category_1`,

`offer_family_1`,

`offer_area_1`,

`offer_units_1`,

`offer_disc_1`,

`offer_price_1`,

`offer_series_2`,

`offer_type_2`,

`offer_subtype_2`,

`offer_artnum_2`,

`offer_category_2`,

`offer_family_2`,

`offer_area_2`,

`offer_units_2`,

`offer_disc_2`,

`offer_price_2`,

`offer_series_3`,

`offer_type_3`,

`offer_subtype_3`,

`offer_artnum_3`,

`offer_category_3`,

`offer_family_3`,

`offer_area_3`,

`offer_units_3`,

`offer_disc_3`,

`offer_price_3`,

`offer_series_4`,

`offer_type_4`,

`offer_subtype_4`,

`offer_artnum_4`,

`offer_category_4`,

`offer_family_4`,

`offer_area_4`,

`offer_units_4`,

`offer_disc_4`,

`offer_price_4`,

`offer_series_5`,

`offer_type_5`,

`offer_subtype_5`,

`offer_artnum_5`,

`offer_category_5`,

`offer_family_5`,

`offer_area_5`,

`offer_units_5`,

`offer_disc_5`,

`offer_price_5`,

`offer_series_6`,

`offer_type_6`,

`offer_subtype_6`,

`offer_artnum_6`,

`offer_category_6`,

`offer_family_6`,

`offer_area_6`,

`offer_units_6`,

`offer_disc_6`,

`offer_price_6`,

`offer_tax`,

`offer_pay`,

`offer_pay_opt`,

`offer_disc`,

`offer_inco`,

`offer_inco_opt`,

`offer_val`,

`offer_val_opt`,

`offer_deltim`,

`offer_deltim_opt`,

`offer_gtc`,

`offer_gtc_opt`,

`offer_note`,

`rep_date`,

`rep_subject`,

`rep_text`,

`rep_upload`,

`rep_complete`,

`query_note`,

`query_complete`,

`offer_units_1` (`offer_price_1` / 100) `offer_disc_1` as offer_reduction_1,

`offer_units_1` `offer_price_1` - (`offer_units_1` `offer_price_1` / 100 `offer_disc_1`) as offer_total_1,
`offer_units_2` (`offer_price_2` / 100) `offer_disc_2` as offer_reduction_2,

`offer_units_2` `offer_price_2` - (`offer_units_2` `offer_price_2` / 100 `offer_disc_2`) as offer_total_2,
`offer_units_1` `offer_price_1` - (`offer_units_1` `offer_price_1` / 100 `offer_disc_1`) +

`offer_units_2` `offer_price_2` - (`offer_units_2` `offer_price_2` / 100 `offer_disc_2`) as offer_total_pos1,
`offer_units_3` (`offer_price_3` / 100) `offer_disc_3` as offer_reduction_3,

`offer_units_3` `offer_price_3` - (`offer_units_3` `offer_price_3` / 100 `offer_disc_3`) as offer_total_3,
`offer_units_4` (`offer_price_4` / 100) `offer_disc_4` as offer_reduction_4,

`offer_units_4` `offer_price_4` - (`offer_units_4` `offer_price_4` / 100 `offer_disc_4`) as offer_total_4,
`offer_units_3` `offer_price_3` - (`offer_units_3` `offer_price_3` / 100 `offer_disc_3`) +

`offer_units_4` `offer_price_4` - (`offer_units_4` `offer_price_4` / 100 `offer_disc_4`) as offer_total_pos2,
`offer_units_5` (`offer_price_5` / 100) `offer_disc_5` as offer_reduction_5,

`offer_units_5` `offer_price_5` - (`offer_units_5` `offer_price_5` / 100 `offer_disc_5`) as offer_total_5,
`offer_units_6` (`offer_price_6` / 100) `offer_disc_6` as offer_reduction_6,

`offer_units_6` `offer_price_6` - (`offer_units_6` `offer_price_6` / 100 `offer_disc_6`) as offer_total_6,
`offer_units_5` `offer_price_5` - (`offer_units_5` `offer_price_5` / 100 `offer_disc_5`) +

`offer_units_6` `offer_price_6` - (`offer_units_6` `offer_price_6` / 100 `offer_disc_6`) as offer_total_pos3,
`offer_units_1` `offer_price_1` - (`offer_units_1` `offer_price_1` / 100 `offer_disc_1`) +

`offer_units_2` `offer_price_2` - (`offer_units_2` `offer_price_2` / 100 `offer_disc_2`) +

`offer_units_3` `offer_price_3` - (`offer_units_3` `offer_price_3` / 100 `offer_disc_3`) +

`offer_units_4` `offer_price_4` - (`offer_units_4` `offer_price_4` / 100 `offer_disc_4`) +

`offer_units_5` `offer_price_5` - (`offer_units_5` `offer_price_5` / 100 `offer_disc_5`) +

`offer_units_6` `offer_price_6` - (`offer_units_6` `offer_price_6` / 100 `offer_disc_6`) as offer_total
From `ges_dwh_v2` WHERE flag_inq='INQUIRY'

I noticed I could not do any maths on an already calculated field.

I just used the original expression rather than the calculated field and it was fine.

Dont recall the exact message will edit the post and add it here next time a mess up MySql tab!

Not sure if this applies to PHPmyadmin or just PHPrunner.

I just found this by trial and error.
Cheers Chris

I understand that - it's because the values of already coded calculations don't exist in the database yet since execution is missing at that point. So you have to base the calculations on formulas including already existing values - that's why these simple calculations appear so complicated.
[color=#0000FF]"I just used the original expression rather than the calculated field and it was fine."
I don't get the point what you mean here?
greets, scuba

Thanks scuba you have put it better than me.

I meant what you have described.
ie if a+b+c=d and e+f+g+h=i MySql cannot add d+i=j ir has to do (a+b+c)+(e+f+g+h)=j
Cannot see why your expression would work in PHPmyadmin but not PHPrunner.

Unless it is the sheer size of the thing.
Does it still bomb if you remove or simplify a portion above and leave the 2 "problem" lines?
Cheers Chris

Hi,
this query works fine on my test box.

Please download and install latest update of PHPRunner:

http://www.asprunner.com/files/phprunner-setup.exe
If it doesn't help send your project file to [email=support@xlinesoft.com]support@xlinesoft.com[/email] for investigation.

Jane, thanks for your tip - I'll do so.
Also I found out that the problem seems to be the size of the query. As Osluk suggested I've simplified the query and everything works fine now.
thanks to all and keep up the great support!!