Use this prompt to analyze a single Oracle query and get either a cross‑compatible rewrite or a PostgreSQL‑only alternative when needed.

Prompt: 

You are an expert in SQL and Oracle→PostgreSQL migrations. I will give you ONE Oracle SQL statement (a query or view definition).

Your job:

1) Determine whether it can be rewritten to be cross-compatible (runs in both Oracle and PostgreSQL).

2) ALWAYS produce a fully rewritten PostgreSQL query that runs as-is.

   - Do NOT ask follow-up questions or leave TODOs. Make safe, documented assumptions.

Conversion requirements (apply wherever relevant):

- Replace Oracle outer joins “(+)” with ANSI joins (e.g., LEFT JOIN … ON …).

- Replace NVL→COALESCE, DECODE→CASE; remove FROM dual; SYSDATE→CURRENT_DATE; SYSTIMESTAMP→CURRENT_TIMESTAMP.

- Convert ROWNUM filters to ORDER BY … LIMIT … (if no ordering exists, state the ordering assumption used).

- Convert LISTAGG/DelimList(…) to set-based aggregation (string_agg) using joins and GROUP BY.

  • If SQL is built dynamically (string concatenation, EXECUTE IMMEDIATE, or helper like DelimList), inline its logic as a normal SELECT with joins (no dynamic SQL in the final answer).

- Replace subqueries nested inside function calls (e.g., NVL((SELECT …), …) or subqueries that only return constants to concatenate) with CASE WHEN EXISTS(…) THEN … END or equivalent joins.

- Move implicit comma joins in FROM/WHERE into explicit JOIN … ON clauses.

- Remove ORDER BY from view definitions; ordering belongs in the consumer SELECT.

- Use standard date/time literals and interval syntax that both engines understand for cross-compatible output.

- If Oracle-specific hierarchy (CONNECT BY/START WITH) appears, rewrite using a recursive CTE in PostgreSQL and note any semantic differences.

Output format (use EXACTLY this structure):

Compatibility: [Yes / No]

Reason(s) Incompatible (if applicable):

- [List each Oracle-specific feature that prevents cross-compatibility]

Cross-Compatible Query (if possible):

sql

Copy

Edit

-- SQL that runs in both Oracle and PostgreSQL (no Oracle-only syntax)

-- Provide the FULL rewritten query/view here

PostgreSQL-Only Alternative (always include):

sql

Copy

Edit

-- FULL PostgreSQL-specific rewrite (no TODOs). Expand any DelimList/dynamic SQL

-- into set-based joins + string_agg; convert (+), NVL/DECODE; remove DUAL; etc.

Notes & Assumptions:

- [Briefly note any assumptions made (e.g., added ORDER BY primary key before LIMIT).]

Please analyze the following Oracle query or view:

sql

Copy

Edit

-- Paste the Oracle SQL here