java.sql.SQLException: ORA-00900: invalid SQL statement in Ecllips

2
votes

I am trying to execute the below query from Ecllips i am getting the following error. 

rs = stmt.executeQuery("select"
+"sum(pr.amount)"
+"from trans_log tl,"
+"payment_detail pp,"
+"pft_trans_reltn pr,"
+"pft_encntr pe,"
+"encounter e,"
+"account a"
+"where"
+"pr.beg_effective_dt_tm >= TO_DATE('01/JUN/2015 00:00:00', 'dd/mm/yy hh24:mi:ss') AND pr.beg_effective_dt_tm <= TO_DATE('30/JUN/2015 23:59:59', 'dd/mm/yy hh24:mi:ss')"
+"and pp.payment_detail_id=tl.payment_detail_id"
+"and tl.activity_id=pr.activity_id"
+"and tl.active_ind=1"
+"and tl.trans_type_cd=10982.00"
+"and pr.parent_entity_name='PFTENCNTR'"
+"and pe.pft_encntr_id=pr.parent_entity_id"
+"and a.acct_id=pe.acct_id"
+"and a.acct_type_cd=649377.00"
+"and pe.encntr_id=e.encntr_id"
+"and e.organization_id=589723.00");

I have tried as suggested in this post Oracle Post Link

I am unable to figure out where i am going wrong.

I've tried to add single quotes, but same error:

pr.beg_effective_dt_tm >= TO_DATE(\"01/JUN/2015 00:00:00\", \"dd/mm/yy hh24:mi:ss\")

Errr:java.sql.SQLException: ORA-00900:

2
javasqloracle
you need whitespaces in between the statements - SomeJavaGuy
i have removed the white space and statements i wrote the entire query in single line but got same error - Chaitanya Pujari
the db i interacting with is oracle is so i have added oracle - Chaitanya Pujari

2 Answers

4
votes

Add whitespaces on every line. Try to use like this:

rs = stmt.executeQuery("select"
+" sum(pr.amount)"
+" from trans_log tl,"
+" payment_detail pp,"
+" pft_trans_reltn pr,"
+" pft_encntr pe,"
+" encounter e,"
+" account a"
+" where"
+" pr.beg_effective_dt_tm >= TO_DATE('01/JUN/2015 00:00:00', 'dd/mm/yy hh24:mi:ss') AND pr.beg_effective_dt_tm <= TO_DATE('30/JUN/2015 23:59:59', 'dd/mm/yy hh24:mi:ss')"
+" and pp.payment_detail_id=tl.payment_detail_id"
+" and tl.activity_id=pr.activity_id"
+" and tl.active_ind=1"
+" and tl.trans_type_cd=10982.00"
+" and pr.parent_entity_name='PFTENCNTR'"
+" and pe.pft_encntr_id=pr.parent_entity_id"
+" and a.acct_id=pe.acct_id"
+" and a.acct_type_cd=649377.00"
+" and pe.encntr_id=e.encntr_id"
+" and e.organization_id=589723.00");
1
votes

if you have 

rs = stmt.executeQuery("select"
+"sum(pr.amount)"

then the string of the sql that it is trying to execute is

selectsum(pr.amount)

as you can see you need some white space.

Also the date mask

TO_DATE('01/JUN/2015 00:00:00', 'dd/mm/yy hh24:mi:ss')

is incorrect, try using MON instead of mm

see http://www.techonthenet.com/oracle/functions/to_date.php