public inbox for [email protected]  

help / color / mirror / Atom feed

From: William Alves Da Silva <[email protected]>
To: Sam Stearns <[email protected]>
Cc: pgsql-sql <[email protected]>
Subject: Re: Oracle ==> Postgres View
Date: Tue, 15 Oct 2024 18:49:19 -0300
Message-ID: <[email protected]> (raw)
In-Reply-To: <CAN6TVjmdUGuFrCn4HcUd4Q2uvNXZSEe_4TXG7neQip_m6dGiBQ@mail.gmail.com>
References: <CAN6TVjmdUGuFrCn4HcUd4Q2uvNXZSEe_4TXG7neQip_m6dGiBQ@mail.gmail.com>

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 <[email protected]> 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
> 
>  <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link><oracle_view.txt...;

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Oracle ==> Postgres View
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox