An error in query db node

sindhus over 8 years ago

trying to use the following select query within a query DB node for a custom Oracle data source. Can anyone please suggest where I'm going wrong?

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

The query returns proper results when used without the where clause but the where clause is causing an issue. I get this error message:

There was a problem during the validation of the SQL statement with the database
The SQL Query is invalid, Invalid column index

Discussion posts and replies are publicly visible

0 rodgraham
Certified Lead Developer
over 8 years ago

As I recall you don't need the single quotes around the account values. Try without those
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 vinitl522
A Score Level 2
over 8 years ago

Please remove single quotes from activity class input variables and then try.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 aloks0189
Certified Lead Developer
over 8 years ago

@sindhus basically in terms of Databases, Invalid Column Index most likely occurs when you are trying to get or set a value which is not in range. But here i am agree with @rodgraham, as you do not need to pass single quotes for passing the condition values as a normal text
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 sparshs over 8 years ago

As suggested by above practitioners please remove single quotes from the parameters passed.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Rama Thummala
Certified Lead Developer
over 8 years ago

@sindhus

As suggested above , if you giving acps in single quotes Appian will treat them as string, it won't replace the values.Please pass acps or pvs without any quotes like APPLICATION_NAME=ac!applicationName
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 sindhus over 8 years ago

If I remove quotes, it throws an error stating table/view does not exist. I'm certain that the view exists and it returns values also when used
without the where condition
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 rodgraham
Certified Lead Developer
over 8 years ago

Can you supply the sql you're now trying to add? That's giving the new error
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 sparshs over 8 years ago

please provide your query here.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 sparshs over 8 years ago

why are you using web while selecting your table i.e web.vw_oracle_dds_inventory.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 bhanuprakashm over 8 years ago

@sindhus,

Let me clarify one thing 'web' is the schema name?
First of all remove the quotes for ac!s and Check the datatypes for passing ac!s to the query.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel