Equivalent of ORACLE QUERY with bind variable IN EDB/Postgres

Question

What will be equivalent of following oracle's query in Edb/Postgres?

SELECT proc_start_date "Process Start Date",
       filename "Filename",
       event_source "Event Source",
       
FROM   Sample_table
WHERE  process_start_date BETWEEN (TRUNC(:eff_date, 'dd') - CAST(:1 AS INTEGER)) AND
                                  (TRUNC(:eff_date, 'dd') + 86399/86400) AND
       proc_end_date IS NOT NULL AND
       filename LIKE DECODE(:2, NULL, '%', '', '%', :2) AND 
       event_source LIKE DECODE(:3, NULL, '%', :3)
ORDER by 1, 2, 3

What changes should i do to run above query on edb database using perl DBI.

I tried following in edb:

SELECT proc_start_date "Process Start Date",
       filename "Filename",
       event_source "Event Source",
       
FROM   sample_table
WHERE  process_start_date BETWEEN (TRUNC(:eff_date, 'dd') - CAST(? AS INTEGER)) AND
                                  (TRUNC(:eff_date, 'dd') + 86399/86400) AND
       proc_end_date IS NOT NULL AND
       filename LIKE DECODE(?, NULL, '%', '', '%', ?) AND 
       event_source LIKE DECODE(?, NULL, '%', ?)
ORDER by 1, 2, 3

But it throws error saying there is 5 bind variables but value is passed only for 3. Also is there any other changes needed in query to run it on edb?

MT0 · Answer 1 · 2022-12-15T14:16:51.397

But it throws error saying there is 5 bind variables but value is passed only for 3.

Because you have changed from named bind variables to unnamed bind variables you need to pass the second and third bind variable values twice.

SELECT proc_start_date "Process Start Date",
       filename "Filename",
       event_source "Event Source",
FROM   sample_table
WHERE  process_start_date BETWEEN (TRUNC(:eff_date, 'dd') - CAST(? AS INTEGER)) AND
                                                              -- ^ one
                                  (TRUNC(:eff_date, 'dd') + 86399/86400) AND
       proc_end_date IS NOT NULL AND
       filename LIKE DECODE(?, NULL, '%', '', '%', ?) AND 
                         -- ^ two                  ^ three
       event_source LIKE DECODE(?, NULL, '%', ?)
                             -- ^ four        ^ five
ORDER by 1, 2, 3

So if you were passing the values 1, a and b then you would need to pass 1, a, a, b, b to bind all the variables.

If you need to rewrite the query to avoid DECODE then:

SELECT proc_start_date "Process Start Date",
       filename "Filename",
       event_source "Event Source",
FROM   sample_table
WHERE  process_start_date BETWEEN (TRUNC(:eff_date, 'dd') - CAST(? AS INTEGER))
                              AND (TRUNC(:eff_date, 'dd') + 86399/86400)
AND    proc_end_date IS NOT NULL
AND    ( filename LIKE ? OR ? IS NULL OR ? = '' )
AND    ( event_source LIKE ? OR ? IS NULL OR ? = '' )
ORDER by 1, 2, 3

(and then you would need to pass in 1, a, a, a, b, b, b to bind all the variables; however, in Oracle '' and NULL are identical so you would not need the ? = '' comparison and could remove it and go back to only 2 copies of the 2nd and 3rd parameters.)

This requires changing oracle's `DECODE` into a `CASE` statement too I think. — lemon, Dec 15 '22 at 14:12
@lemon: I think EDB provides an Oracle compatibility layer that supports `decode()` — , Dec 15 '22 at 14:15

Equivalent of ORACLE QUERY with bind variable IN EDB/Postgres

1 Answers1