karen
Posts: 99
|
| Posted: 05/20/2009, 4:31 AM |
|
Hi all,
I'm sure there's a very simple solution to my problem so I will stop cracking my head and try asking. I've been at this for 2 days (not that long but long enough for something simple). Here goes...
I have 2 tables:
1) master table storing the locations
2) transaction table recording collections at the different locations
I have created a report which sum the collections by locations and further groups the locations by their states. Each location is to print a line whether there are any transactions for it or not, i.e., if there isn't, zero would be recorded against it. I achieved this by doing a left join so all the locations will be selected. This report worked really well with query builder until we decided to add some search criteria, that is to select only a subset of the transaction (filtered by dates).
When the dates were added to the criteria, the report then only displayed those locations with transactions. The rest were not displayed at all. This is confirmed to be correct by running a similar query in MySQL. Since we wanted to have all locations displayed, it looked like I had to do a subselect, i.e., filter all the transaction table first then do the left join. So I ended up with this query and used it to replace the SQL statement in the Before Execute Select event of the report:
$fin_tithes_report->DataSource->SQL = "SELECT * \n" .
"FROM members_assemblies LEFT JOIN \n" .
"(SELECT * FROM fin_tithes \n" .
"WHERE tithes_week >= '" . $fin_tithes_search->s_tithes_week->GetValue() .
"' AND tithes_week <= '" . $fin_tithes_search->s_tithes_week1->GetValue() . "') b \n" .
"ON members_assemblies.assemblies_id = b.tithes_assembly_id \n" .
"WHERE members_assemblies.assemblies_state <> 'NIL' ";
Unfortunately I ended up with this error message:
Database Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE members_assemblies.assemblies_state <> 'NIL' ORDER BY members_assemblies.a' at line 6
I suspect that the custom SQL is not working well with the report's grouping which has been setup using the builder. Must I also do something abt those bits? Or is something else wrong with my SQL? I've tested the SQL with static dates on MySQL and it works.
I would really appreciate if someone can give me some advice on this. Many thanks in advance.
Cheers,
Karen
|
 |
 |
damian
Posts: 838
|
| Posted: 05/20/2009, 5:29 AM |
|
only suggestion i have is are you modifying the original report or are yu creating a new one... i always have trouble modifying a report after its built...
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
jjrjr1
Posts: 942
|
| Posted: 05/21/2009, 10:00 PM |
|
Hi Karen
I have always found it difficult to troubleshoot a MySql command by just looking at the PHP code that creates it. All the puncuation and php stuff sometimes makes complicated queries hard to decifer inm looking at the php.
So what I do sometimes for testing echo the query string the die(); This way you can look at the query generated and often the error pops right out.
Try this
$SQL = "SELECT * \n" .
"FROM members_assemblies LEFT JOIN \n" .
"(SELECT * FROM fin_tithes \n" .
"WHERE tithes_week >= '" . $fin_tithes_search->s_tithes_week->GetValue() .
"' AND tithes_week <= '" . $fin_tithes_search->s_tithes_week1->GetValue() . "') b \n" .
"ON members_assemblies.assemblies_id = b.tithes_assembly_id \n" .
"WHERE members_assemblies.assemblies_state <> 'NIL' ";
echo $SQL;die();
$fin_tithes_report->DataSource->SQL = $SQL;
After the echoed sql string looks ok.. Just comment out the echo statement.
Hope this helps a little.
_________________
John Real - More CodeCharge Studio Support at - http://CCSElite.com |
|
|
|
karen
Posts: 99
|
| Posted: 05/25/2009, 7:06 PM |
|
Thanks to both of you for your suggestions. I am going to take both advice and I am now trying to build a new report and also to check the echoed SQL at the same time.
Firstly, if I don't dynamically replace the SQL, where should I place the echo SQL statement?
Secondly, when I built a new report using SQL instead of tables in the visual query builder, for some reason, the builder dims out the option to build a search form towards the end of the dialog. Any ideas? I had to add the search form manually later but there must be a logical reason why it's 'not allowed'.
Thanks again for your kind advice. I will continue to try and will post update.
|
|
|
|
karen
Posts: 99
|
| Posted: 05/25/2009, 7:52 PM |
|
Ok, I have had some success with a new report as well as with editing the existing report. Both worked well except for one minor problem. When using tables in the visual query builder, we could specify to use the default value in the where condition parameter if it is empty, instead of using null. So with the date filter, if there was no date entered, all records were returned.
Now with using SQL, I have entered the default value as 01/01/0001 for the start date and 31/12/9999 for the end date. However, these values appeared to be not used as when there are no dates selected (search form), there are no records returned. I have checked that there are records by manually entering these start and end dates. I have also tried to put "" in the default values since I figured that when using Tables, I did not have to specify a default value but yet, when not entering any dates in the search form, the query returned all records.
I need to understand more abt the search criteria and how the default values affect the query. If anyone could provide any insight, pls help. Thanks!
|
|
|
|
damian
Posts: 838
|
| Posted: 05/26/2009, 5:01 AM |
|
i dont know your answer as to why it isnt working but trying to think of a way to get it to do what you want perhaps the link to your report page could include the params to pass the dates?
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
karen
Posts: 99
|
| Posted: 05/26/2009, 5:22 AM |
|
Hi Damian, thanks for your time and thought into this. I've got it working! I thought I'd try to put quotes or hash around the default dates after reading thru some other posts in the forums and the quotes worked! Learned something new 
|
|
|
|
damian
Posts: 838
|
| Posted: 05/26/2009, 5:31 AM |
|
simple solutions are best... im not much of a programmer and i always muck up my code adding " instead of ' or vice versa :)
_________________
if you found this post useful take the time to help someone else.... :)
|
|
|
|
|
