0

I am using "with data as" as in below query. When I run this in sql developer, it's executing fine, but in java code when I call the query as normal string or through jdbc template in xml configuration file, it gives me bad SQL grammar. Is there any alternative to the below query?

public class NppGWOrphanMessageDao extends DefaultDao {
String sql = "same sql as i posted"
private String replayGWOrphanMsgSQL;

public void setReplayGWOrphanMsgSQL(String replayGWOrphanMsgSQL) {
    this.replayGWOrphanMsgSQL = replayGWOrphanMsgSQL;
}

public String getReplayGWOrphanMsgSQL() { return replayGWOrphanMsgSQL; }

public List<Map<String, Object>> getReplayList(HashMap<String, Object> epoch) { 
    return retrieveAll(replayGWOrphanListSQL, params); 
    return retrieveAll(sql, epoch); }
}

WITH DATA AS ( 
    SELECT GLOB.ID, GLOB.CHARACTERS 
    FROM GW_LOB_STORE GLOB 
    WHERE 
        NOT EXISTS(SELECT 1 FROM GW_NPP_MSG_INTEGRITY M WHERE M.LOB_STORE_ID=GLOB.ID) 
        AND NOT EXISTS(SELECT 1 FROM GW_NPP_SAFE_STORE S WHERE S.LOB_STORE_ID=GLOB.ID)
        AND NOT EXISTS(SELECT 1 FROM GW_POISON_LOG P WHERE P.LOB_STORE_ID=GLOB.ID)
        AND GLOB.CREATED_TS > = :epoch)
SELECT 
    A.ID AS "GLOBID", 
    INQUEUEDTL.ID AS "INQUEID", 
    A.CHARACTERS AS "REQUESTBODY", 
    INQUEUEDTL.ENDPOINT_ID AS "ENDPOINTID", 
    INQUEUEDTL.HEADER AS "HEADERS" 
FROM DATA A, GW_IN_QUEUE_DETAIL INQUEUEDTL 
WHERE A.ID=INQUEUEDTL.ID;
Ori Marko
  • 56,308
  • 23
  • 131
  • 233
user3436400
  • 79
  • 2
  • 8
  • What is the error? – Ori Marko Jul 31 '17 at 04:35
  • it gives me ORA - 00911 invalid character \n – user3436400 Jul 31 '17 at 04:40
  • I think :epoch should be replaced with ? – Ori Marko Jul 31 '17 at 04:41
  • no epoc i am replacing with timestamp in the code, it gives me same error even if i hard code epoc with proper value. – user3436400 Jul 31 '17 at 04:43
  • 2
    So show your code and real sql – Ori Marko Jul 31 '17 at 04:45
  • public class NppGWOrphanMessageDao extends DefaultDao { String sql = "same sql as i posted" private String replayGWOrphanMsgSQL; public void setReplayGWOrphanMsgSQL(String replayGWOrphanMsgSQL) { this.replayGWOrphanMsgSQL = replayGWOrphanMsgSQL; } public String getReplayGWOrphanMsgSQL() { return replayGWOrphanMsgSQL; } public List> getReplayList(HashMap epoch) { //return retrieveAll(replayGWOrphanListSQL, params); return retrieveAll(sql, epoch); } – user3436400 Jul 31 '17 at 04:47
  • Please edit your question with **real** code The fact that the error was to do with sql formatting, then this makes it even more important – Scary Wombat Jul 31 '17 at 04:48
  • does epoch variable is replace with 'epochValue' (with quote signs)? – Ori Marko Jul 31 '17 at 04:52
  • @user3436400 Are you doing the formatting with the double quotes you have with the alias names such as "GLOBID", "INQUEID" etc. ? – Jacob Jul 31 '17 at 04:58
  • yes its replacing with quote signs – user3436400 Jul 31 '17 at 04:58
  • @user75ponic yes, i have done the formatting, its posted here as double quotes in real its proper sql query which workes fine in sql developer – user3436400 Jul 31 '17 at 05:00
  • `String sql = "same sql as i posted"` - this is really what I want to see. If there is formatting problems here, then *ORA - 00911 invalid character \n* may appear – Scary Wombat Jul 31 '17 at 05:00
  • @user3436400 Before the line with the `ORA - 00911 invalid character`, try printing the output of `sql` variable and copy the output and execute in SQLDeveloper. – Jacob Jul 31 '17 at 05:01
  • query, WITH DATA AS ( SELECT GLOB.ID, GLOB.CHARACTERS FROM GW_LOB_STORE GLOB WHERE NOT EXISTS(SELECT 1 FROM GW_NPP_MSG_INTEGRITY M WHERE M.LOB_STORE_ID=GLOB.ID) AND NOT EXISTS(SELECT 1 FROM GW_NPP_SAFE_STORE S WHERE S.LOB_STORE_ID=GLOB.ID) AND NOT EXISTS( SELECT 1 FROM GW_POISON_LOG P WHERE P.LOB_STORE_ID=GLOB.ID) AND GLOB.CREATED_TS > = '1501477329') SELECT A.ID AS "GLOBID", INQUEUEDTL.ID AS "INQUEID", A.CHARACTERS AS "REQUESTBODY", INQUEUEDTL.ENDPOINT_ID AS "ENDPOINTID", INQUEUEDTL.HEADER AS "HEADERS" FROM DATA A, GW_IN_QUEUE_DETAIL INQUEUEDTL WHERE A.ID=INQUEUEDTL.ID; – user3436400 Jul 31 '17 at 05:03
  • 2
    Do you send the sql code with the semi-colon (the ;) at the end? – zuckermanori Jul 31 '17 at 05:05
  • There is a space between `>` and `=`. It should be `>=`. –  Jul 31 '17 at 05:09
  • @zuckermanori, yes, we send through semi-colon at the end for al lthe queries – user3436400 Jul 31 '17 at 05:10
  • @saka 1029 i removed space, still no luck. – user3436400 Jul 31 '17 at 05:11
  • @user3436400 Can you try removing the double quotes from the column aliases and execute the Java code? – Jacob Jul 31 '17 at 05:15
  • 1
    Remove `;`. See [Understanding mysterious Oracle JDBC errors - ORA-00911: invalid character](https://stackoverflow.com/questions/11855799/understanding-mysterious-oracle-jdbc-errors-ora-00911-invalid-character). –  Jul 31 '17 at 05:20
  • 1
    @saka1029 is correct, this is what I was referring to – zuckermanori Jul 31 '17 at 05:28
  • Thanks @Zuckermanori this worked – user3436400 Jul 31 '17 at 05:59
  • @saka thanks that worked. – user3436400 Jul 31 '17 at 05:59

1 Answers1

1

For the sake of completeness:

The problem with this query is the ; at the end of it.

Oracle JDBC driver does not handle it well.

zuckermanori
  • 1,675
  • 5
  • 22
  • 31