We've run into this recently because a user was trying to get a count of cancellation reasons by date. Looking at the Debugging Output SQL somehow the Left Outer Join is trying to match ahr.id = ahrcc.id where it should be ahr.cancel_cause = ahrcc.id
SELECT * FROM (SELECT "ahrcc"."id" AS "Cause ID",
"ahrcc"."label" AS "Cause Label",
"ahr"."cancel_cause" AS "Cancelation cause",
COUNT(DISTINCT "ahr"."id") AS "Hold ID"
FROM action.hold_request AS "ahr"
LEFT OUTER JOIN action.hold_request_cancel_cause AS "ahrcc" ON ("ahr"."id" = "ahrcc"."id")
WHERE date("ahr"."cancel_time") BETWEEN $_27678$2016-03-20$_27678$ AND $_27678$2016-03-26$_27678$
GROUP BY 1, 2, 3
ORDER BY "ahrcc"."id" ASC, "ahrcc"."label" ASC, "ahr"."cancel_cause" ASC, COUNT(DISTINCT "ahr"."id") ASC
) limited_to_1048575_hits LIMIT 1048575
See the end of line 6.
I can't see any reason in fm_IDL.xml that this would be the case, however.
We've run into this recently because a user was trying to get a count of cancellation reasons by date. Looking at the Debugging Output SQL somehow the Left Outer Join is trying to match ahr.id = ahrcc.id where it should be ahr.cancel_cause = ahrcc.id
SELECT * FROM (SELECT "ahrcc"."id" AS "Cause ID", "cancel_ cause" AS "Cancelation cause", hold_request_ cancel_ cause AS "ahrcc" ON ("ahr"."id" = "ahrcc"."id") ."cancel_ time") BETWEEN $_27678$ 2016-03- 20$_27678$ AND $_27678$ 2016-03- 26$_27678$ cancel_ cause" ASC, COUNT(DISTINCT "ahr"."id") ASC to_1048575_ hits LIMIT 1048575
"ahrcc"."label" AS "Cause Label",
"ahr".
COUNT(DISTINCT "ahr"."id") AS "Hold ID"
FROM action.hold_request AS "ahr"
LEFT OUTER JOIN action.
WHERE date("ahr"
GROUP BY 1, 2, 3
ORDER BY "ahrcc"."id" ASC, "ahrcc"."label" ASC, "ahr"."
) limited_
See the end of line 6.
I can't see any reason in fm_IDL.xml that this would be the case, however.