If I set up db lookup in editor mode with [where] command, I will get an error when I use the db lookup.
SQL Command
SELECT XXX FROM WHERE XXX IS NOT NULL
Search
index=test |lookup db_connect_dblookup2 XXX AS XXX
Error
ERROR ScriptRunner - stderr from 'D:\Splunk\bin\PYTHON.EXE D:\Splunk\etc\apps\splunk_app_db_connect\bin\dbxlookup.py dblookup2': Traceback (most recent call last):
ERROR ScriptRunner - stderr from 'D:\Splunk\bin\PYTHON.EXE D:\Splunk\etc\apps\splunk_app_db_connect\bin\dbxlookup.py dblookup2': File "D:\Splunk\etc\apps\splunk_app_db_connect\bin\dbxlookup.py", line 114, in
ERROR ScriptRunner - stderr from 'D:\Splunk\bin\PYTHON.EXE D:\Splunk\etc\apps\splunk_app_db_connect\bin\dbxlookup.py dblookup2': raise ex
ERROR ScriptRunner - stderr from 'D:\Splunk\bin\PYTHON.EXE D:\Splunk\etc\apps\splunk_app_db_connect\bin\dbxlookup.py dblookup2': avro.ipc.AvroRemoteException: java.sql.SQLSyntaxErrorException: ora-00907 right parenthesis missing
11-01-2016 10:25:30.522 ERROR LookupOperator - Script for lookup table 'db_connect_dblookup2' returned error code 1. Results may be incorrect.
If there is not a where command, it works. Is it a bug?
If it's a bug caused by the WHERE clause a possible workaround would be to use your query as a n SQL temporary table:
SELECT * FROM (SELECT XXX FROM WHERE XXX IS NOT NULL) as tmp
thanks for your answer. i tried it, but it got error.
Invalid Query
External search command 'dbxquery' returned error code 1. Script output = "RuntimeError: Failed to run query: "SELECT * FROM (SELECT * FROM (SELECT XXX FROM WHERE XXX IS NOT NULL) AS tmp) t", params: "None", caused by: Exception(' java.sql.SQLSyntaxErrorException: ORA-00907: missing right parenthesis