Monday, August 04, 2008

UNION with DISTINCT via ODBC to ORACLE

In a VBA application (MS Access XP) with linked tables to Oracle the following query completely crashes Access!

SELECT A1.V_ID, A1.PR_ID, A1.AREA_ID, A1.TYPE,
A1.AREA, A1.AREA_MAIN_ID, A1.ACTIVE
FROM AREAS AS A1
UNION SELECT DISTINCT A2.V_ID, A2.PR_ID,
-1 AS AREA_ID, A2.TYPE, '' AS AREA,
A2.AREA_MAIN_ID, -1 AS ACTIVE
FROM AREAS AS A2;


Solution: convert the distinct to a group by

SELECT A1.V_ID, A1.PR_ID, A1.AREA_ID, A1.TYPE,
A1.AREA, A1.AREA_MAIN_ID, A1.ACTIVE
FROM AREAS AS A1
UNION (
SELECT A2.V_ID, A2.PR_ID, -1 AS AREA_ID, A2.TYPE,
'' AS AREA, A2.AREA_MAIN_ID, -1 AS ACTIVE
FROM AREAS AS A2
GROUP BY A2.V_ID, A2.PR_ID, A2.TYPE, A2.AREA_MAIN_ID);

No comments: