Re: Oracle ==> Postgres View

Hello.

I think you want this.

coalesce(CASE impact_category

WHEN 'BULK_RATE_REQUEST' THEN 1 -- To handle Portal's category for the old RIP

WHEN 'CONTRACT_BULK_RATE' THEN 1

WHEN 'SPOT_BULK_RATE' THEN 2

WHEN 'CONTRACT_HISTORY' THEN 3

WHEN 'SPOT_HISTORY' THEN 4

WHEN 'RATE_SUBMISSION' THEN 5

WHEN 'SPOT_BACKHAUL' THEN 6 END , 0)

Regards,

William Alves

On 15 Oct 2024, at 18:30, Sam Stearns <sam.stearns@dat.com> wrote:

Howdy,

I have 2 views attached.  An Oracle view written with NVL.  The same view was converted to Postgres using COALESCE.  Postgres is throwing an error:

ERROR:  syntax error at or near ","
LINE 12: ...esce(CASE WHEN impact_category='BULK_RATE_REQUEST', 1, -- To...

The problem block of code:

    coalesce(CASE WHEN impact_category='BULK_RATE_REQUEST', 1, -- To handle Portal's category for the old RIP
       'CONTRACT_BULK_RATE', 1,
       'SPOT_BULK_RATE', 2,
       'CONTRACT_HISTORY', 3,
       'SPOT_HISTORY', 4,
       'RATE_SUBMISSION', 5,
       'SPOT_BACKHAUL' THEN  6 END , 0),

Would anyone be able to advise how to correct this for Postgres, please?

Thanks,

Sam
                                                                                                                       ^

--

Samuel Stearns
Lead Database Administrator
c: 971 762 6879 | o: 503 672 5115 | DAT.com

<oracle_view.txt><postgres_view.txt>