Perhaps I should have split this up into two mails, but I saw them related
in the difference of databases
On mysql Ver 11.12 Distrib 3.23.32, for pc-linux-gnu (i686)
this works
SELECT SQL_BUFFER_RESULT DATE_FORMAT(ORDERHEADER.AvailableAt,'%y/%m/%d') AS
'Date', (@tl:=SUM(IF(DISPATCHLOG.ID_STATUS = 30 AND ORDERLINE.Step =
1,1,0))) AS 'Total', (@wb:=SUM(IF(DISPATCHLOG.Details LIKE 'Web O/E%' AND
DISPATCHLOG.ID_STATUS = 30 AND ORDERLINE.Step = 1,1,0))) AS 'Web',
(@sys:=SUM(IF(DISPATCHLOG.Details = 'O/E by System' AND
DISPATCHLOG.ID_STATUS = 30 AND ORDERLINE.Step = 1,1,0))) AS 'System',
(@can:=(SUM(IF(DISPATCHLOG.ID_STATUS = 43 AND ORDERLINE.Step =
1,1,0))-SUM(IF(DISPATCHLOG.ID_STATUS = 73 AND ORDERLINE.Step = 1,1,0)))) AS
'Cancelled', (@tl-@wb-@sys-@can) AS 'Balance' FROM ORDERHEADER LEFT JOIN
ORDERLINE ON ORDERHEADER.ID = ORDERLINE.ID_ORDERHEADER LEFT JOIN DISPATCHLOG
ON ORDERLINE.ID = DISPATCHLOG.ID_ORDERLINE WHERE (ORDERHEADER.AvailableAt
BETWEEN '[param:Start Date]' AND '[param:End Date]') GROUP BY 'Date' ORDER
BY 'Date' DESC
but on mysql Ver 11.13 Distrib 3.23.36, for pc-linux-gnu (i686) it doesnt
calculate the balance column
--------------
SELECT @com:=SUM(PAYROLL.Amount) AS 'Commissions', '0.00' AS 'Other',
@com+'' AS 'Total', DATE_FORMAT('[param:StartDate]', '%y/%m/%d') AS
'StartDate', DATE_FORMAT('[param:EndDate]', '%y/%m/%d') AS 'EndDate' FROM
CONTRACTOR LEFT JOIN PAYROLL ON CONTRACTOR.ID = PAYROLL.ID_CONTRACTOR WHERE
Aka = '[param:Aka]' AND (PAYROLL.CreateStamp BETWEEN '[param:StartDate]' AND
DATE_FORMAT('[param:EndDate]', '%Y%m%d235959'))
This works fine on the older version(3.23.32), but on the newer(3.23.36), I
needed a GROUP BY Aka
SQLException: General error: Mixing of GROUP columns
(MIN(),MAX(),COUNT()...) with no GROUP columns is illegal if there is no
GROUP BY clause
==============================
SQLState: S1000
VendorError: 1140
--------------
I do not thing the data is relative to this question.
Note: about the use of [param:Variable], these select statements are stored
in the database, they are put through a custom parser that prompts for these
variables when the statement is used to generate forms
1. How could I get the first statement return a value for 'Balance' in the
newer version?
2. What is that SQL Exception telling me?
3. And out of curiosity, why is there now a requirement for the GROUP BY
when there wasnt before?
Luc Foisy
---------------------------------------------------------------------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
