Thanks a lot guys for all your help, and it is working now.
It had nothing to do w/ the date... I just had to close it and reopen it for it to 'rewrite' itself in Base SQL, or whatever you'd like to call it.
That being said, I still have one more issue... my
WITH ROLLUP clause is not coming through.
I am running my queries as such:
View 1
Code: Select all
SELECT BenID, CONCAT_WS(',', BenSName, BenFName) NAME, DOB, Gender Sex, Island,
FortnightRate Amount, BankNo, BankSuff, DirsignDate, Ben1EffDate
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'
UNION
SELECT BenID ben, CONCAT_WS(',', BenSName, BenFName) NAMES, DOB bod, Gender, Island place,
FortnightRate, BankNo NO, BankSuff suf, dirsigndate, ben2effdate
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';
Since MySQL doesn't allow subqueries for views, I did a view of view 1 as such:
Code: Select all
CREATE VIEW MVParaUnRoll AS SELECT IFNULL(Island, 'Grand Total') Location, IFNULL( NAME, CONCAT_WS(' ', Island, 'Total')) Person, IFNULL(BenID,0) ID, IFNULL(DOB, '->') Born, IFNULL(Sex, '->') 'M/F', DirS
ignDate, Ben1EffDate, IFNULL(BankNo, '->') Bank, IFNULL(BankSuff, '->') Suff, COUNT(BenID) ID_Total, FORMAT(SUM(Amount),2) 'Total'
FROM MVParamTest
GROUP BY Island, Name WITH ROLLUP;
This looks fine on the command line prompt of MySQL (though, I should probably change my IfNulls to CASE WHENs, and change the name of my 'M/F' column). I run it in Base with this query:
Code: Select all
SELECT Location, Person, ID, Born, "M/F", Bank, Suff, ID_Total, Total
FROM MVParaUnRoll
It also looks fine,
until I add contraints to the query in either the back or front end and as parameters or dates: this appears to be an SQL issue.
I add 1 constraint such as:
Here, it looks about how it should, both with or without parameters, but I do get a MySQL Warning:
Warning 1292 Incorrect date value: '2004' for column 'DirSignDate' at row 12
When I look at the DirSignDate for row 12, it's actually a subtotal row for an island and should be null, but because of bugs in MySQL, gets the DirSignDate of the row immediately above it (which I can live with). Moreover, the DirSignDate for that row is not '2004' and is in a valid date format. All I can guess is that maybe MySQL is trying to ROLLUP the dates for that island? Even if this is the case, it would be doing so incorrectly.
If I add another contstraint
I get two of the same warnings for each constraint on the same row. Now, however, It reports the next row (13), and then stops... leaving out 1 more row of data, 1 more subtotal, and the grand total, which would be a dealbreaker.
I'd like to mark this as solved, since it seems to be more of a MySQL issue w/ views than Base, but I'm only doing views as a workaround for Base.
You guys have been a great help. I'm very grateful I learned SQL on Oracle instead of MySQL, though.