Converting Oracle SQL Select into PostgreSQL select
I have this SQL statement:
SELECT ABX.ABX_APO_NUMBER,
COUNT(A1.PROCESS_MODE) AS NUM_PLANNING,
COUNT(A2.PROCESS_MODE) AS NUM_SETUP,
COUNT(A3.PROCESS_MODE) AS NUM_OUTPUT
FROM ABX, USER_INSTANCE U, ACTIVE_PROCESS A1,
ACTIVE_PROCESS A2, ACTIVE_PROCESS A3
WHERE U.ABX_APO_NUMBER (+) = ABX.ABX_APO_NUMBER
AND A1.PROCESS_INSTANCE_NUMBER (+) = U.INSTANCE_NUMBER
AND A1.PROCESS_MODE (+)= 'PLANNING'
AND A2.PROCESS_INSTANCE_NUMBER (+) = U.INSTANCE_NUMBER
AND A2.PROCESS_MODE (+) = 'SETUP'
AND A3.PROCESS_INSTANCE_NUMBER (+) = U.INSTANCE_NUMBER
AND A3.PROCE开发者_JAVA技巧SS_MODE (+) = 'OUTPUT'
GROUP BY ABX.ABX_APO_NUMBER
The (+) is causing fits for PG... Can I just remove those and it will mean the same thing? What would be the equivalent that Postgres would understand?
Here is your query re-written using ANSI-92 JOIN syntax:
SELECT a.abx_apo_number,
COUNT(ap1.process_mode) AS NUM_PLANNING,
COUNT(ap2.process_mode) AS NUM_SETUP,
COUNT(ap3.process_mode) AS NUM_OUTPUT
FROM ABX a
LEFT JOIN USER_INSTANCE u ON u.abx_apo_number = a.abx_apo_number
LEFT JOIN ACTIVE_PROCESS ap1 ON ap1.process_instance_number = u.instance_number
AND ap1.process_mode = 'PLANNING'
LEFT JOIN ACTIVE_PROCESS ap2 ON ap2.process_instance_number = u.instance_number
AND ap2.process_mode = 'SETUP'
LEFT JOIN ACTIVE_PROCESS ap3 ON ap3.process_instance_number = u.instance_number
AND ap3.process_mode = 'OUTPUT'
GROUP BY a.abx_apo_number
The (+)
is Oracle specific LEFT OUTER JOIN syntax. To remove it, would require that each USER_INSTANCE.instance_number
would have to have values for the process modes being all three: PLANNING, SETUP, and OUTPUT - omit one, and the abx_apo_number would not be displayed in the output.
No, you can not just remove them!
The (+) is the old notation of an OUTER JOIN
.
For example
...
FROM USER_INSTANCE U, ACTIVE_PROCESS A1
WHERE A1.PROCESS_INSTANCE_NUMBER (+) = U.INSTANCE_NUMBER
AND A1.PROCESS_MODE (+)= 'PLANNING'
...
would have to be changed to
...
FROM USER_INSTANCE U
LEFT JOIN ACTIVE_PROCESS A1
ON ( A1.PROCESS_INSTANCE_NUMBER = U.INSTANCE_NUMBER
AND A1.PROCESS_MODE = 'PLANNING'
)
...
精彩评论