Convert oracle query to postgresql

Question

I need to convert oracle query to postgresql

(select PK_, 
        'Statement of Holdings' as NOTIFICATIONNAME_ , 
        CASE NOTIFICATIONSSTATUS_ 
             WHEN 'Processed'              THEN 'Succeeded' 
             WHEN 'Processed With Failure' THEN 'Failed' 
             WHEN 'Impacted'               THEN 'Succeeded'  
             WHEN 'Impact Failed'          THEN 'Failed' 
             WHEN 'Archived'               THEN 'Succeeded' 
             WHEN 'Prepared'               THEN 'Running' 
                                           ELSE 'Planned' 
        END as STATUS , 
        DIRECTION_, 
        UPDATEDATE_ as updatedate_, 
        (to_date('19700101', 'YYYY-MM-DD HH24:MI:SS' ) + (( 1 / 24 / 60 / 60 / 1000) * UPDATEDATE_)) as MODIFICATIONDATE  
from atementOfHoldingsNotification_ 
where (DIRECTION_ is not null) and (updatedate_ > 1609462800000) ) 

union all 

(select PK_, 
        'Statement of Transactions' as NOTIFICATIONNAME_ , 
        CASE NOTIFICATIONSSTATUS_  
             WHEN 'Processed'              THEN 'Succeeded' 
             WHEN 'Processed With Failure' THEN 'Failed' 
             WHEN 'Impacted'               THEN 'Succeeded'  
             WHEN 'Impact Failed'          THEN 'Failed' 
             WHEN 'Archived'               THEN 'Succeeded' 
             WHEN 'Prepared'               THEN 'Running' 
                                           ELSE 'Planned' 
        END as STATUS , 
        DIRECTION_, 
        UPDATEDATE_ as updatedate_, 
        (to_date('19700101', 'YYYY-MM-DD HH24:MI:SS' ) + (( 1 / 24 / 60 / 60 / 1000) * UPDATEDATE_)) as MODIFICATIONDATE  
from entOfTransactionsNofitication_ 
where (DIRECTION_ is not null) and (updatedate_ > 1609462800000)) 

union all 

(select PK_, 
        'Statement of Pending Transactions' as NOTIFICATIONNAME_ , 
        CASE NOTIFICATIONSSTATUS_ 
             WHEN 'Processed'              THEN 'Succeeded' 
             WHEN 'Processed With Failure' THEN 'Failed' 
             WHEN 'Impacted'               THEN 'Succeeded'  
             WHEN 'Impact Failed'          THEN 'Failed' 
             WHEN 'Archived'               THEN 'Succeeded' 
             WHEN 'Prepared'               THEN 'Running'
                                           ELSE 'Planned' 
        END as STATUS , 
        DIRECTION_, 
        UPDATEDATE_ as updatedate_,

I got this issue when i executed:

ERROR: operator does not exist: date + numeric LINE 4: ..._, (to_date('19700101', 'YYYY-MM-DD HH24:MI:SS' ) + (( 1 / 2... ^ HINT: No operator matches the given name and argument types. You might need to add explicit type casts. SQL state: 42883 Character: 586

As it's currently written, your query won't work neither in oracle because doesn't end properly. Also looks like there are lots of repetitions inside your subqueries, maybe this can be simplified a lot. Can you share your sample data, expected output and a description of what your code is supposed to do? — lemon, Jan 12 '23 at 14:15
What is MODIFICATIONDATE ? And why such complex code for a simple calculation on a date or timestamp? — Frank Heikens, Jan 12 '23 at 14:16
Hi @lemon i need to convert this to postgresql any help please ? to_javadate(trunc(current_timestamp)) — aymen krifa, Jan 12 '23 at 14:21
@FrankHeikens I have had a look at the question myself. Turns out I think I can answer yours instead. See [here](https://stackoverflow.com/questions/37305135/oracle-convert-unix-epoch-time-to-date). Not the ideal way to do it though, [this](https://database.guide/convert-a-unix-timestamp-to-a-date-value-in-oracle/) looks like a much better method to me. — Atmo, Jan 12 '23 at 14:21
`date + 1` in Oracle means adding 1 day to a date. In PostgreSQL you can do `date + interval '1 day'` — The Impaler, Jan 12 '23 at 14:34
I need to convert to_javadate(trunc(current_timestamp)) to postgresql please — aymen krifa, Jan 12 '23 at 14:36
Your updatedate_ field appears to be an elapsed number of seconds. If that is so, see https://stackoverflow.com/questions/28528028/adding-integer-seconds-to-a-timestamp-in-postgresql — Chris Maurer, Jan 12 '23 at 14:38

score 3 · Answer 1 · answered Jan 12 '23 at 14:33

Instead of:

to_date('19700101', 'YYYY-MM-DD HH24:MI:SS' ) + (( 1 / 24 / 60 / 60 / 1000) * UPDATEDATE_

You can use TIMESTAMP and INTERVAL literals:

TIMESTAMP '1970-01-01 00:00:00.000 UTC' + UPDATEDATE_ * INTERVAL '0.001' SECOND

Which works in both Oracle fiddle and PostgreSQL fiddle.

Convert oracle query to postgresql

1 Answers1