telmiger
Posts: 61
|
| Posted: 06/14/2005, 8:17 PM |
|
I wrote the following custom SQL for my grid:
SELECT client_id, client_number, client_name, project_number, project_billing.project_id AS project_billing_project_id, project_opened_date,
project_title, project_charge_id, project_charges.amount AS project_charges_amount, invoice_status, payments.amount AS payments_amount,
SUM(payments.amount)AS total_amount
FROM ((project_billing RIGHT JOIN project_charges ON
project_charges.project_id = project_billing.project_id) INNER JOIN clients ON
clients.client_id = project_billing.company_id) LEFT JOIN payments ON
payments.invoice_id = project_charges.project_charge_id
WHERE project_charges.invoice_status = 3
GROUP BY project_charges.project_charge_id
HAVING (project_charges_amount > total_amount OR
total_amount IS NULL)
ORDER BY project_charge_id desc
This gives me a Count() SQL error when I try to display the page.
Searching trough the forum I figured out that the problem is with the COUNTSQL that CCS adds to count the records.
What I do not know is how to write the COUNTSQL query defining the table aliases and Sum fields in the Having clause.
|
 |
 |
peterr
Posts: 5971
|
| Posted: 06/14/2005, 8:32 PM |
|
The way I'd fix this is to find CountSQL in the generated .php page, then copy and run that SQL directly in MySQL and see what errors I'd get, then edit the SQL a little wherever MySQL is complaining, then when the SQL is finally working assign it back to CountSQL property in CCS via an event, per http://docs.codecharge.com/studio/html/Components/RTPro...P/CountSQL.html
Though maybe someone else knows exactly how to convert the above SQL to a count.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
|
