Writing a SQL query with Parameter in WM

, ,
1

Priyatham,

You can provide a query parameter at the run time by using the DynamicSQL template of the JDBC adapter. The dynamic sql allows you to define the entire query or part of query as a run time parameter. For example, in your case, in the SQL area of your Dynamic SQl, you will need to type:
select distinct apb.bank_branch_name
from ap.ap_bank_accounts_all apa,
ap.ap_bank_branches apb
where apa.bank_branch_id = apb.bank_branch_id
and apa.bank_account_num = ${BANK_STMT_NUM}

The name that you give inside ${} becomes an input to that Dynamic SQL service which you provide at run time - ie map any variable in your pipeline to this input.

HTH, Roh

2

Hi Rohit,

We are using Oracle Apps adapter. I have created one Oracle Applications Adapter and I am able to insert into Oracle Applications Cash Management Interface tables. But my only concern is I don’t want to hardcode the values.

But I can see only the following when I create new adapter service:

Adapter Service>
Select Adapter Type>
JDBC Apps Adapter
Oracle Apps Adapter> BankStatements:BanStmt
Sample Adapter
Under BankStatements:BanStmt I have 3 template:

InsertSQL : Select records based on SQL tree
Compound Select SQL : Select records based on SQL tree
Simple SQL: Select or update records based on simple SQL.

which one do I need to pick? We are using Oracle Apps Adpater.

Can you help me out!!!

Thank you very much,
Priyatham

3

Guys,
I solved my problem. Anyways Thank you all.

Priyatham

4

Hi…
Can i include both SELECT and UPDATE in a single adapter service ?
How do i do that ?
Iam using JDBC Adapter

5

Hi,
You can only use one of them at a time in adapter service
Thanks
Jay

6

Thank you so much, I was trying in all ways…
Have a great day…
byeeee

7

You could do this by writing a stored procedure that performed both a select and an update. You would use the stored procedure adapter service to invoke hte stored procedure.

Mark

8

hii…
Thanx for the reply…
I just wanted to ask whether we can really do this or not ?
Coz i got a reply that we cannot do that …
Lemme know whether ur sure or not about we can do or not
I will try definitely if there is a way to do it.
Thanx

9

Hi,
Within webMethods adapter service you can only use one statement at a time.
As Mark said try to write a stored procedure and call it.
Thanks,
Jay

10

Tell you what. Find and read the JDBC Adapter User’s Guide and then report back to us here about the stored procedure adapter service and stored procedure with signature adapter service.

Mark

11

Hi, I tried the stored procedure, but was not able 2 do that.
I wrote 2 services for SELECT and UPDATE. I will keep trying that.

Now i have a new question,
Can i write a single adapter service for

SELECT table2.id from table1
innerjoin table3 on table1.id=table3.id and table3.id=“…”
innerjoin table4 on table1.id=table4.id and table4.id=“…”
innerjoin table5 on table5.id=table2.id and table1.no=“…”

Can i write this in a single service ? by just putting joins in which i keep the innerjoins and in WHERE i will keep the remaining equalities.
Please let me know
Thankyou