Error in Passing I/P values to Dynamic SQL

, ,
1

Hi all,
I am getting the following error while invoking the dynamic sql service:

I have tried to execute in following ways:
1- I wrote an sql statement in SQL field line as:
select * from tablename where date = ‘12/JUL/2006’
I got the required output.

2- In real time I will get the where condition to this Adapter serive.
I wrote an sql statement in following way for this:
select * from tablename ${str}
I created one Input field as str (where date = ‘12/JUL/2006’ ) and selected output fields, while executing this serivce its showing the following error:

com.wm.app.b2b.server.ServiceException: [ART.117.4002] Adapter Runtime (Adapter Service): Unable to invoke adapter service TEST.adapterService:TESTDYN.
[ADA.1.316] Cannot execute the SQL statement ““SELECT * FROM tablename WHERE date = ‘12/JUL/2006’””. "
(17003) Invalid column index"
Invalid column index

Could some body please help me.

thanks
ravi.

2

Let your query be
SELECT * from TableName where date = ${str}
and pass only date as input to str.

3

anumolurk,

          Did you have this issue resolved ??  If so please let me know.
4

My problem was with my SQL query and it works now.

select * from tablename where date = ‘${str}’

5

Hi All,

Below is the query which i need to implement in webMethods,since this is related to outer join im unable implement in normal select adapter service so i m choosing Dynamic sql.

select a.X_SALES_REGION,b.name ALIAS_NAME, c.name Primary_Industry_Name
from tbsie.mv_s_org_ext a, tbsie.mv_s_org_syn b, tbsie.mv_S_INDUST c
where b.OU_ID(+) = a.row_id
and a.X_PR_INDUST_ID = c.row_id(+)

Apart from these , I should be able to pass a DATE field as input so that the select query should fetch only those records which are greater than the given date.

Please advice on this.