Hi, Facing issue with DB query node while using simple sub-query. sel

riteshp over 9 years ago

Hi, Facing issue with DB query node while using simple sub-query.

select TOP 12 ID from (select ID from PROJECT WHERE ID=ac!prjId )_result

While validation showing error:

"Errors
There was a problem during the validation of the SQL statement with the database

The SQL Query is invalid, com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near the keyword \\'WHERE\\'."

If i remove inner "WHERE ID=ac!prjId" condition it works fine. Appian 7.6 + MSSQL Server2008 + Jboss 6.2

Thanks, Ritesh
...

Discussion posts and replies are publicly visible

0 Mike Cichy
Appian Employee
over 9 years ago

Maybe it is cause by the underscore after the closed parentheses?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 riteshp over 9 years ago

Hi Mike, Thanks for reply. underscore is kept as prefix to variable name. if i remove inner "where" condition it works

Thanks, Ritesh
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 marcoc200 over 9 years ago

whatis the value of the ac!prjld?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 riteshp over 9 years ago

Hi Marco,
It is an integer variable but it is created externally i.e. once query validated, it should be there in input section.

Thanks, Ritesh
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 marcoc200 over 9 years ago

Have you tried to use this statemnt directly on sqlserver substituting the value of the ac! with a value of type integer that you know exist? it works correctly?
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 riteshp over 9 years ago

Hi Marco, Thanks for reply! Yes, it works, it works even with more complex query. In fact we have same query in one of our process model, working fine. Now after upgrade, when we are trying to update process model it does not allow the same query to validate. Thanks.
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 marcoc200 over 9 years ago

try with this:
select TOP 12 ID from (select ID from HSE WHERE [AppianHSE].[dbo].[HSE].ID=ac!oldID)_result substituting [AppianHSE].[dbo].[HSE].ID with [PROJECT].ID
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 riteshp over 9 years ago

Hi Marco,
Tried with this and got below error.

"The database returned ARV's that may not be supported. Message: Incorrect syntax near \\'substituting\\'."

:( Thanks, Ritesh
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 marcoc200 over 9 years ago

ok last tips:
-select a custom datasource
-select the source type
-enter the ip
-enter the username and the password
-use this statement substituting [AppianHSE] with the name of your database and [HSE] with [PROJECT], remember to use [dbo]
select TOP 12 ID from (select ID from [AppianHSE].[dbo].[HSE] WHERE [AppianHSE].[dbo].[HSE].ID=ac!oldID)_result
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 riteshp over 9 years ago

Hi Marco,
:) It works. Thanks a lot. I feel stupid when i copied your last statement as it is :(. So, finally with

select TOP 12 ID from (select ID from PROJECT WHERE [appian_sandbox_new].[dbo].[PROJECT].ID=ac!oldID)_result

OR

select TOP 12 ID from (select ID from PROJECT WHERE [PROJECT].ID=ac!oldID)_result

queries it is working. I did not create custom datasource instead used the same existing datasource name.

Thanks again, Ritesh
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel