dbConnect - java.sql.SQLException: Invalid column index

[BernardEAI]

Hi

I'm trying to set up a query in dbConnect. I have configured the connection correctly. I'm running the following query:

SELECT CREATE_DATE,
DELETE_DATE,
SPLUNK_DEDUP_SQID,
SPLUNK_IDX_ASC,
FROM "USER"."TABLE"
WHERE CREATE_DATE BETWEEN TO_DATE('20161101', 'RRRRMMDD') AND TO_DATE('20180821', 'RRRRMMDD')
ORDER BY SPLUNK_IDX_ASC ASC

If I run this in batch mode, the query works. However, if I run it in "rising" mode,  with SPLUNK_IDX_ASC set as the rising column, the query returns:

java.sql.SQLException: Invalid column index

No results found.

[isoutamo · Accepted solution]

Hi

in which splunk, DBX, database engine, jdbc etc. version you have into use?

Usually the there should be ? as indicating the rising column and it's value.

SELECT CREATE_DATE,
  DELETE_DATE,
  SPLUNK_DEDUP_SQID,
  SPLUNK_IDX_ASC,
FROM "USER"."TABLE"
WHERE CREATE_DATE BETWEEN TO_DATE('20161101', 'RRRRMMDD') 
        AND TO_DATE('20180821', 'RRRRMMDD')
  AND SPLUNK_IDX_ASC > ?
ORDER BY SPLUNK_IDX_ASC ASC

And then you should set value for it on checkpoint field in your input definition.

r. Ismo