Thread

1. Oracle ==> Postgres View

   Sam Stearns <sam.stearns@dat.com> — 2024-10-15T21:30:55Z

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

2. Re: Oracle ==> Postgres View

   David Rowley <dgrowleyml@gmail.com> — 2024-10-15T21:46:01Z

   On Wed, 16 Oct 2024 at 10:31, Sam Stearns <sam.stearns@dat.com> wrote:
   >     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?
   
   Have a look at the syntax of your case statement and check it against [1].
   
   David
   
   [1] https://www.postgresql.org/docs/current/functions-conditional.html
   
   
   
   

3. Re: Oracle ==> Postgres View

   William Alves Da Silva <william_silva@unochapeco.edu.br> — 2024-10-15T21:49:19Z

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

4. Re: Oracle ==> Postgres View

   Sam Stearns <sam.stearns@dat.com> — 2024-10-16T16:15:11Z

   Thank you all for the help!  William's advice did the trick.
   
   Sam
   
   
   On Tue, Oct 15, 2024 at 2:49 PM William Alves Da Silva <
   william_silva@unochapeco.edu.br> wrote:
   
   > 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
   > [image: DAT]
   > <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link>
   > <oracle_view.txt><postgres_view.txt>
   >
   >
   >
   
   -- 
   
   *Samuel Stearns*
   Lead Database Administrator
   *c:* 971 762 6879 | *o:* 503 672 5115 | DAT.com
   [image: DAT]
   <https://www.dat.com/?utm_medium=email&utm_source=DAT_email_signature_link>