0

I'm trying to use this SQL in my spring batch reader. The SQL seems to have a problem: 

select DISTINCT ces.COR_ENBL_STG_ID 
from HBX_BATCH_COR_ENBL_STG ces 
Inner JOIN HBX_INDV_NEG_ACTION ina ON ces.INDV_ID=ina.INDV_ID 
where ces.DISP_PROCESSED_FLAG='Y'  
AND ina.NEG_ACTION_RUN_RSN_CD in('11054','11055','11065') 
AND ces.PGM_BGN_DT+90<'#{jobExecutionContext['latest.completed.startTime']}'

The value of latest.completed.start.time is coming as '07-Oct-16 12:38:58.000000109 PM' from a tasklet using jdbctemplate and hence the SQL is throwing the following error : ORA-01858: a non-numeric character was found where a numeric was expected.

Just FYI the column ces.PGM_BGN_DT is a Date Type. I tried TO_DATE function but it didn't help. Any suggestions please?

Jayvee
  • 10,670
  • 3
  • 29
  • 40
K Sharma
  • 31
  • 8
  • What is data time of latest.completed.startTime? And what did you try with TO_DATE(), please show us. – Nghia Do Oct 07 '16 at 11:34
  • This error is coming from jdbc driver and not from spring batch reader so not a spring batch reader issue. You need to fix query and data. See if these two help, [SO_Q1](http://stackoverflow.com/questions/27425515/oracle-sql-to-date-to-timestamp-ora-01858-a-non-numeric-character-was-found-w) , [SO_Q2](http://stackoverflow.com/questions/29852389/getting-error-ora-01858-a-non-numeric-character-was-found-where-a-numeric-was) – Sabir Khan Oct 08 '16 at 14:20
  • @NghiaDo latest.completed.startTime is a timestamp value. I needed it to be compared with pgm_bgn_dt which was a date. The problem was because batch was taking it as string after replacing the value and persisting it inside the single quotes. I used to_timestamp and now its comparing. – K Sharma Oct 10 '16 at 06:32
  • @SabirKhan Thanks for the links. They were quite useful. :) – K Sharma Oct 10 '16 at 06:32

1 Answers1

0

As I understand latest.completed.startTime - is a string of characters. And its look like as oracle timestamp datatype. If my suggestion are true, You should use format to convert string to timestamp

to_timestamp(latest.completed.startTime, 'dd-mon-yy HH12:MI:SS.FF AM');
Michael Piankov
  • 1,989
  • 1
  • 8
  • 19
  • hey @michael Piankov thanks for the help. The batch parameter latest.completed.startTime was coming as string after getting replaced with the value due to the single quotes. I used to_timestamp and now its comparing well. Thanks much :) – K Sharma Oct 10 '16 at 06:28