|
|
|
|
|
Re: Losing $where when fixed selection is not present [message #2265 is a reply to message #2242] |
Mon, 17 August 2009 06:57   |
gpatti
Messages: 283 Registered: August 2008
|
Senior Member |
|
|
Tony, with further more detailed testing I've found that this isn't completely fixed under certain conditions.
If I run the OUTPUT2 task without making any specific selections (i.e. a complete report) the following SQL is generated:
SELECT cost_group_charge.*, cost_group.cost_group_desc, school.school_name
FROM cost_group_charge
LEFT JOIN cost_group ON (cost_group.cost_group_id=cost_group_charge.cost_group_id)
LEFT JOIN school ON (school.school_id=cost_group_charge.rdcaccount_id)
WHERE cost_group_charge.rdcaccount_id='7'
ORDER BY cost_group_charge.age_group_id, cost_group_seq_nbr, cost_group_charge.banner_req =>Count=86
However, if I make a couple of selections before running the report the SQL is:
SELECT cost_group_charge.*, cost_group.cost_group_desc, school.school_name
FROM cost_group_charge
LEFT JOIN cost_group ON (cost_group.cost_group_id=cost_group_charge.cost_group_id)
LEFT JOIN school ON (school.school_id=cost_group_charge.rdcaccount_id)
WHERE ( cost_group_charge.cost_group_id='SDA2' ) OR ( cost_group_charge.cost_group_id='SDA3' ) AND cost_group_charge.rdcaccount_id IN ('1', '7')
ORDER BY cost_group_charge.age_group_id, cost_group_seq_nbr, cost_group_charge.banner_req =>Count=6
This actually selects from other rdcaccount_id rows also. It needs the selections that are grouped with the OR statement to be bracketed together.
|
|
|
|
|
|
|
Re: Losing $where when fixed selection is not present [message #2270 is a reply to message #2242] |
Mon, 17 August 2009 09:55   |
gpatti
Messages: 283 Registered: August 2008
|
Senior Member |
|
|
I actually run the output2 task from a list1 transaction based on the cost_group table.
The user can set their charges by selecting cost_groups and entering a list2 task:
The outer table is cost_group (key cost_group_id)
The inner table is cost_group_charge (key rdcaccount_id and cost_group_id + a couple of others)
The intention was for users to be able to print a full scale of charges for all cost groups by running the output2 task from the list1 without making any selections, and this works fine.
However, I can't prevent a user making a selection in the list1 screen before clicking the output2 button, and this is when it goes wrong.
Because of the unusual scenario linking tables, one without rdcaccount_id, maybe it isn't worth the effort of getting the framework to handle this automatically. However, I need to ba able to code to prevent the wrong data being presented to the users, hence the suggestion in my latest post.
Further to this, the output2 task is based on the inner table, but the button is placed on the list1 screen of the outer table.
[Updated on: Mon, 17 August 2009 09:58] Report message to a moderator
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|