[Solved] Report from Query with UNION and parameters?
Posted: Tue Jul 24, 2012 11:37 pm
I'm running a query as such:
It works fine if I run the SQL directly. However, when I generate a report from the query and try to open it, I get
(Also, if anyone knows what GROUP BY WITH ROLLUP syntax I would use to get totals for Amount for each island, that'd be great)
Code: Select all
SELECT BenID ID, CONCAT_WS(',', BenSName, BenFName) NAME, DOB, Gender Sex, Island,
FortnightRate Amount, BankNo, BankSuff
FROM Beneficiary b, Apply a, BenefitType bt
WHERE b.BankNo IS NOT NULL
AND b.benid = a.beneficiary
AND a.benefit1 = bt.bentype
AND bentype LIKE '%Child%'
AND Ben1Active = 'Active'
AND DirSignDate <=
(SELECT DATE_ADD(CURDATE(), INTERVAL (11 - IF(DAYOFWEEK(CURDATE())=1, 10,
DAYOFWEEK(CURDATE()))) DAY) AS NextWednesday)
AND Ben1EffDate <=
(SELECT DATE_ADD(CURDATE(), INTERVAL (11 - IF(DAYOFWEEK(CURDATE())=1, 10,
DAYOFWEEK(CURDATE()))) DAY) AS NextWednesday)
UNION
SELECT BenID ID, CONCAT_WS(',', BenSName, BenFName) NAME, DOB, Gender Sex, Island,
FortnightRate Amount, BankNo, BankSuff
FROM Beneficiary b, Apply a, BenefitType bt
WHERE b.BankNo IS NOT NULL
AND b.benid = a.beneficiary
AND a.benefit2 = bt.bentype
AND bentype LIKE '%Child%'
AND Ben2Active = 'Active'
AND DirSignDate <=
(SELECT DATE_ADD(CURDATE(), INTERVAL (11 - IF(DAYOFWEEK(CURDATE())=1, 10,
DAYOFWEEK(CURDATE()))) DAY) AS NextWednesday)
AND Ben2EffDate <=
(SELECT DATE_ADD(CURDATE(), INTERVAL (11 - IF(DAYOFWEEK(CURDATE())=1, 10,
DAYOFWEEK(CURDATE()))) DAY) AS NextWednesday);
I believe Base has a problem with UNION, but is there a workaround for this?Syntax error in SQL Expression
(Also, if anyone knows what GROUP BY WITH ROLLUP syntax I would use to get totals for Amount for each island, that'd be great)