Setting up a Query Database Smart node

sswilliams over 8 years ago

I'm trying to connect to my Oracle database using the Query Database smart node in the process modeler. I run into an issue when I add a 'where' clause to my SQL statement. I get the following error: There was a problem during the validation of the SQL statement with the database. The SQL Query is invalid, ORA-00942: table or view does not exist. This is the SQL statement: select ROWNUM, APPLICATION_NAME, ENVIRONMENT, OS, INSTANCE_NAME, HOSTNAME from web.vw_oracle_dds_inventory where APPLICATION_NAME=ac!applicationName and ENVIRONMENT=ac!Environment order by HOSTNAME. If I remove the 'where' clause from the SQL statement, I'm able to save and close the node. I'm using Appian v16.2. Can someone assist me with this error? Thanks...

Discussion posts and replies are publicly visible

0 abhinavg712
Certified Lead Developer
over 8 years ago

Try saving the node without the table alias as 'web'
SELECT
ROWNUM,
APPLICATION_NAME,
ENVIRONMENT,
OS,
INSTANCE_NAME,
HOSTNAME
FROM vw_oracle_dds_inventory
WHERE APPLICATION_NAME = ac!applicationName and
ENVIRONMENT=ac!Environment order by HOSTNAME
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 sswilliams over 8 years ago

I removed 'web' from the statement. Still getting the same error. I get the following error on the statement without the 'where' clause and with 'web' removed as well: There was a problem during the validation of the SQL statement with the database. he database returned ARV's that may not be supported. Message: ORA-00942: table or view does not exist.
Is there something else that I can try?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 abhinavg712
Certified Lead Developer
over 8 years ago

techjourney.net/.../
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 bhanuprakashm over 8 years ago

@sswilliams, Can you please check the column names in the table..if anything is wrong it shows this message..before write this code in this querynode ..please check in db.
if it execute successfully there is no issues,,

Hope it will help you
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 sswilliams over 8 years ago

bhanuprakashm, the column names in the statement are correct. I can close and save the node if the 'where' clause is removed. Thanks...
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 sswilliams over 8 years ago

abhinavg712 - the things that are recommended on the website you suggested were done prior to submitting this and were done again. IE - permissions are good. The statement is working fine in our DEV and Production environments.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel