I've successfully created a manual lookup to an Oracle database using the DB Connect v2 app, but when I try to use it I receive the error Script for lookup table 'db_connect_has_plan' returned error code 1. Results may be incorrect. The dbx_error log contains the following error:
java.sql.SQLException: Validate the SQL [SELECT "USER_ID","STATUS" FROM (SELECT "USER_ID","STATUS" FROM "SPLUNK"."PLANS" WHERE "PLAN_ID" = 101 AND "STATUS" = 'active') AS lookuptable WHERE "USER_ID" = ?] failed. java.sql.SQLSyntaxErrorException: ORA-00933: SQL command not properly ended
The AS in the lookuptable alias is causing the error.
How can I adjust the SQL statement so that it is valid?
Edit: Just to clarify, the query I provide for the lookup is SELECT "USER_ID","STATUS" FROM "SPLUNK"."PLANS" WHERE "PLAN_ID" = 101 AND "STATUS" = 'active' . The SQL that fails validation is generated by the app.
... View more