Dynamic SQL Error

, ,
1

HI everyone,

Could not run ‘selectXXXXXXXX’.

com.wm.app.b2b.server.ServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service PACKAGE.adapters.FOLDER:ADAPTERSERVICE.
[ADA.1.316] Cannot execute the SQL statement

"select A1,
A2,
TO_CHAR(A3,‘YYYY-MM-DD’),
A4,
A5,
A6,
A7
from Employee where A8 in ( ${runtimeValueforA8)})

(42000/907) ORA-00907: missing right parenthesis
"
ORA-00907: missing right parenthesis

when iam testing the above adapter service iam passing the value for runtimeValueforA8? it is throwing above exception.

Can some one tell me where iam making mistake.

I appreciate your help.

thanks

2

This part of the query looks suspicious:

( ${runtimeValueforA8)})

3

Thanks For the help, You are right that was a typo.

4

I am trying to build a dynamic sql and I get the error message below The same query when I code as a custom SQL it works as expected.

My Dynamic SQL is as follows

SELECT T1.ColA, T1.ColB
FROM ${ENV}.Table1 T1
WHERE T1.ColA = ‘${INPUT}’

I want pass in ENV and INPUT dynamically

My error message is as follows:

com.wm.app.b2b.server.ServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service
(17003) Invalid column index"
Invalid column index

When i create a Custom SQL similar to this it returns results as expected.
My custom SQL:

SELECT T1.COLA,
T1.COLB
FROM CRPDTA.TABLE1 T1
WHERE T1.COLA = ?

I would like to pas in ENV = CRPDTA dynamically.

Please let me know.

Thanks
Sai

5

I assume CRPDTA is the schema name…and i dont think dynamicSQL can accept as param in this case for schema.tablename.

HTH,
RMG

6

Not defining InputJDBCType, InputFieldType and InputField in
DynamicSql Tab returned the results as expected.

Regards
Sai

7

Good to know…

But is it with out specifying input param you are able to pass the $ENV param??

HTH,
RMG

8

Yes… it works.
No need to define input params for dynamic sql queries. it will be taken from query itself.