Karen
|
| Posted: 04/01/2002, 2:31 AM |
|
I have a custom SQL on a grid type form for which one of the fields is a calculated field (ytd_sales). When I try to sort by this field, it treats it as a table field and hence a database error as follows:
Database error: Invalid SQL: select p.*, sum(ytdorder) as ytd_sales from product as p left join ytdorders on ytdorders.code = p.code group by p.code order by p.ytd_sales ASC
MySQL Error: 1054 (Unknown column 'p.ytd_sales' in 'order clause')
Session halted.
How do I correct this? I tried to replace the string in $sOrder in the Form Open event but it doesn't work.
I also had a custom sorting code in the Open Event but somehow this is causing duplicate order by statement now (not sure if it's due to the upgrade), resulting in such an error:
Database error: Invalid SQL: select p.*, sum(ytdorder) as ytd_sales from product as p left join ytdorders on ytdorders.code = p.code group by p.code order by category ASC order by category ASC
MySQL Error: 1064 (You have an error in your SQL syntax near 'order by category ASC' at line 1)
Session halted.
Pls help to shed some light on this. Thanx.
|
|
|
 |
Nicole
|
| Posted: 04/01/2002, 3:31 AM |
|
Karen,
in custom sorting code in Open event of the form you should overwrite $sOrder variable value with custom one. In this case you'll get correct "order by" clause.
|
|
|
|
Karen
|
| Posted: 04/01/2002, 5:15 PM |
|
Hi Nicole,
If I remove the custom sorting code, the table fields are sorted ok. The problem is now only the calculated field. Here's the error if I don't have any custom sorting code:
Database error: Invalid SQL: select p.*, sum(ytdorder) as ytd_sales from product as p left join ytdorders on ytdorders.code = p.code group by p.code order by p.ytd_sales ASC
MySQL Error: 1054 (Unknown column 'p.ytd_sales' in 'order clause')
Session halted.
And if I replace $sOrder in the Open Event as such:
$sOrder = str_replace("p.ytd_sales", "ytd_sales", $sOrder);
I get a totally different SQL error as follows:
Database error: Invalid SQL: select count(*) from product as p left join ytdorders on ytdorders.code = p.code group by p.code order by ytd_sales ASC
MySQL Error: 1054 (Unknown column 'ytd_sales' in 'order clause')
Session halted.
I'm not quite sure where this select count(*).... came from when it should be select p.*, sum(ytdorder) as ytd_sales....
Can you help to explain this? I'm not sure what to do next. Pls help. Thanx.
|
|
|
|
Nicole
|
| Posted: 04/02/2002, 12:19 AM |
|
Karen,
the error you getting now is from navigation sql query. "order by" clause should be truncated from it. This was a bug in one of previous CC versions and from the error I make the conclusion that you use not latest version. Upgrade to CC 2.0.5 and the problem should go away.
Download link is available at: http://codecharge.com/updates/cc2_0_5.html
|
|
|
|
Karen
|
| Posted: 04/02/2002, 4:07 PM |
|
That's it, Nicole! I just upgraded and the error has gone away. Thanx so much for the advice.
|
|
|
|
|
