When I run the following query I get returned results:
SELECT REPORT_1_FLOW_PATTERNS_1.CLIENT_NAME, MERCHANT_INFO.RISK_MANAGER, STATS_PREV_28D_2.AVG_CREATED_TRX_NUMBER, CASE WHEN BATCHES=1 AND BATCH_START <= BATCH_STOP AND TO_NUMBER(TO_CHAR(TO_DATE('01-04-2012', 'dd-mm-rr'), 'DD'))>=BATCH_START AND TO_NUMBER(TO_CHAR(TO_DATE('01-04-2012', 'dd-mm-rr'), 'DD'))<=BATCH_STOP OR BATCH_START>BATCH_STOP AND TO_NUMBER(TO_CHAR(TO_DATE('01-04-2012', 'dd-mm-rr'), 'DD'))>=BATCH_START OR TO_NUMBER(TO_CHAR(TO_DATE('01-04-2012', 'dd-mm-rr'), 'DD'))<=BATCH_STOP THEN 1 ELSE 0 END AS BATCH, REPORT_1_FLOW_PATTERNS_1.PROCESSING_DATE
FROM MERCHANT_INFO INNER JOIN REPORT_1_FLOW_PATTERNS_1 ON MERCHANT_INFO.CLIENT_ID = REPORT_1_FLOW_PATTERNS_1.CLIENT_ID LEFT JOIN STATS_PREV_28D_2 ON MERCHANT_INFO.CLIENT_ID = STATS_PREV_28D_2.CLIENT_ID
GROUP BY REPORT_1_FLOW_PATTERNS_1.CLIENT_NAME, MERCHANT_INFO.RISK_MANAGER, STATS_PREV_28D_2.AVG_CREATED_TRX_NUMBER, CASE WHEN BATCHES=1 AND BATCH_START <= BATCH_STOP AND TO_NUMBER(TO_CHAR(TO_DATE('01-04-2012', 'dd-mm-rr'), 'DD'))>=BATCH_START AND TO_NUMBER(TO_CHAR(TO_DATE('01-04-2012', 'dd-mm-rr'), 'DD'))<=BATCH_STOP OR BATCH_START>BATCH_STOP AND TO_NUMBER(TO_CHAR(TO_DATE('01-04-2012', 'dd-mm-rr'), 'DD'))>=BATCH_START OR TO_NUMBER(TO_CHAR(TO_DATE('01-04-2012', 'dd-mm-rr'), 'DD'))<=BATCH_STOP THEN 1 ELSE 0 END, REPORT_1_FLOW_PATTERNS_1.PROCESSING_DATE;
However, when I get an error when I replace this with:
SELECT REPORT_1_FLOW_PATTERNS_1.CLIENT_NAME, MERCHANT_INFO.RISK_MANAGER, STATS_PREV_28D_2.AVG_CREATED_TRX_NUMBER, CASE WHEN BATCHES=1 AND BATCH_START <= BATCH_STOP AND TO_NUMBER(TO_CHAR(TO_DATE('01-04-2012', 'dd-mm-rr'), 'DD'))>=BATCH_START AND TO_NUMBER(TO_CHAR(TO_DATE('01-04-2012', 'dd-mm-rr'), 'DD'))<=BATCH_STOP OR BATCH_START>BATCH_STOP AND TO_NUMBER(TO_CHAR(TO_DATE('01-04-2012', 'dd-mm-rr'), 'DD'))>=BATCH_START OR TO_NUMBER(TO_CHAR(TO_DATE('01-04-2012', 'dd-mm-rr'), 'DD'))<=BATCH_STOP THEN 1 ELSE 0 END AS BATCH, REPORT_1_FLOW_PATTERNS_1.PROCESSING_DATE
FROM MERCHANT_INFO INNER JOIN REPORT_1_FLOW_PATTERNS_1 ON MERCHANT_INFO.CLIENT_ID = REPORT_1_FLOW_PATTERNS_1.CLIENT_ID LEFT JOIN STATS_PREV_28D_2 ON MERCHANT_INFO.CLIENT_ID = STATS_PREV_28D_2.CLIENT_ID
GROUP BY REPORT_1_FLOW_PATTERNS_1.CLIENT_NAME, MERCHANT_INFO.RISK_MANAGER, STATS_PREV_28D_2.AVG_CREATED_TRX_NUMBER, BATCH, REPORT_1_FLOW_PATTERNS_1.PROCESSING_DATE;
The only difference is that I have tried to simply the GROUP BY statement by replacing the calculated field with "BATCHES" (the name of the displayed field). This clearly does not work but I am wondering if there is a way I can simply the query rather than writing the whole calculation again.