jukhooa
Posts: 5
|
| Posted: 05/13/2005, 1:07 PM |
|
Hi,
I have the following issue.
1. Created a simple grid.
2. Datasource is the following SQL statement:
select FROM_DAYS(TO_DAYS(vdate)) as day, COUNT(*)
from logging group by day;
3. It should give a list of all the dates (without the timestamp) and the hits for that day. In MySql the query is working perfectly.
4. But when I create th pages I get the following error during runtime:
Database error: Invalid SQL: SELECT COUNT(*) FROM logging group by day;
MySQL Error: 1054 (Unknown column 'day' in 'group statement')
Session halted.
So this means that CCS changed my SQL query and there for it is not functioning.
Doing ds->CountSQL(....) did not help me here in the before build select event.
What am I doing wrong? Does someone have a workaround?
Kind regards, Anand.
_________________
Cheers,
Anand |
 |
 |
mrachow
Posts: 509
|
| Posted: 05/17/2005, 4:28 AM |
|
I'm not completely sure but I think Studio had not changed your SQL statement but the statement you see is the one Studio creates for itself to compute the number of records for the grid.
In doing that Studio removes the slected columns and substitues them by count(*) changing nothing else.
Sorry but not knowing what to do at the moment.
_________________
Best regards,
Michael |
|
|
|
jukhooa
Posts: 5
|
| Posted: 05/17/2005, 4:38 AM |
|
Michael,
Yes that could be the case. I now have the workround that during record insert I use the sql currdate() in a separate field and I use this field for the group by which works correct. Hope that in feature release of codecharge studio the handeling for "complex" sql statements will be improved.
Regards, Anand.
_________________
Cheers,
Anand |
|
|
|
peterr
Posts: 5971
|
| Posted: 05/17/2005, 10:37 AM |
|
ds->CountSQL should work. I recommend focusing on debugging that issue. Any SQL complexity is OK, except for the Count, since simlpy adding the COUNT clause to any SQL doesn't always work. Not sure if that can be improved.
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
|
|
|
Ronald
|
| Posted: 05/22/2005, 10:39 AM |
|
Use the BeforeExecuteSelect event to overwrite the CodeCharge CountSQL and write your own CountSQL.
It should be something like:
select COUNT(day), FROM_DAYS(TO_DAYS(vdate)) as day
from logging group by day
It is necessary to define "day" before you can group it. A simpler and more flexible solution would be to store the date and the time in separate columns.
|
|
|
|
|
