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?
Because you have changed from named bind variables to unnamed bind variables you need to pass the second and third bind variable values twice.
So if you were passing the values
1
,a
andb
then you would need to pass1
,a
,a
,b
,b
to bind all the variables.If you need to rewrite the query to avoid
DECODE
then:(and then you would need to pass in
1
,a
,a
,a
,b
,b
,b
to bind all the variables; however, in Oracle''
andNULL
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.)