Routine with PostgreSQL not working

Five Stars

Routine with PostgreSQL not working

Hello,

 

I am using a routine which returns date type value.
I have 2 jobs where I have used the same routine.
In first job I'm using Oracle database and in second job database is PostgreSQL.
Both database connections have same data with same datatypes.
When I'm running the job with Oracle connection my routine is doing well
But when I change the connection to postgreSQL it it giving error.
Attaching the screenshots for reference.

 

Thanks,
Trupti


Accepted Solutions
Five Stars

Re: Routine with PostgreSQL not working

Hi,

PLease remove extra slashes which are not part of postgresql(e.g. routine part)
 "select * from \"DWH\".\"STG\".\"STG_TEST_TABLE\" a
 where \"a\".\"LAST_UPDATED_DATE\" >=
 TO_DATE('\"+Load_max_upaded_date.getValue(\"LOAD_DIMESION\"+\"'yyyy-MM-dd')"
 
needs to be changed with

 "select * from \"DWH\".\"STG\".\"STG_TEST_TABLE\" a
 where \"a\".\"LAST_UPDATED_DATE\" >=
 TO_DATE('"+Load_max_upaded_date.getValue("LOAD_DIMESION"+"'yyyy-MM-dd')"
 
 Thanks,
 Rajashree

 

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think 

---
Don't forget to give kudos when a reply is helpful.
Thanks

All Replies
Five Stars

Re: Routine with PostgreSQL not working

Hi,

PLease remove extra slashes which are not part of postgresql(e.g. routine part)
 "select * from \"DWH\".\"STG\".\"STG_TEST_TABLE\" a
 where \"a\".\"LAST_UPDATED_DATE\" >=
 TO_DATE('\"+Load_max_upaded_date.getValue(\"LOAD_DIMESION\"+\"'yyyy-MM-dd')"
 
needs to be changed with

 "select * from \"DWH\".\"STG\".\"STG_TEST_TABLE\" a
 where \"a\".\"LAST_UPDATED_DATE\" >=
 TO_DATE('"+Load_max_upaded_date.getValue("LOAD_DIMESION"+"'yyyy-MM-dd')"
 
 Thanks,
 Rajashree

 

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think 

---
Don't forget to give kudos when a reply is helpful.
Thanks