Sender JDBC adapter

[Former Member]

Hi All,

I have 2 tables. First table

Header

-

---

Name EmpId Status

Jai 5601 0

Karthik 5579 0

Item

-

---

Name Contactnumber

Jai 9894268913

Jai 04312432431

Karthik 98984110335

Karthik 04222643993

Now I need to select a single record from Header and all its corresponding records from Item then I need to update the status field to prevent processing the same record and move to next header and all its corresponding Item records. How can I achieve this in a scenario with out BPM? How should I write my SQL and Update SQL in sender commn channel?

Appreciate your help in this.

Thanks & Regards,

Jai Shankar.

[Former Member · Accepted solution]

Hi Jai,

I guess I got it.

You have to select a single document from the database. The problem is to qualify your select statement.

a) The solution is to select the MIN record (MIN means you select only ONE)!

b) On the other hand you want wo select corresponding from a second table.

So you have to combine a) with b)

a) select * from table_1 where event_id = (select min(event_id) from table_1 where status = 0)

b) select * from table_2 t1, table_1 t2 where t1.name = t2.name

Unfortunately I am not a SQL specialist.

Somebody else should be able to combine the statements a) and b)

Regards Mario

[Former Member · Accepted solution]

Hi Mario,

Seems to be a good approach. I l try this and get back to you.

Thanks & Regards,

Jai Shankar.

[Former Member · Accepted solution]

Hi Mario,

<i>select * from table_1 where event_id = (select min(event_id) from table_1 where status = 0)</i>

Can you explain some thing about this SQL. What does the event_id signify here? I am getting an error while I try this SQL in Querry Analyser.

Thanks & Regards,

Jai Shankar.

[bhavesh_kantilal · Accepted solution]

Jai,

If the query mentioned by Mario is possible then it is really a good way to go about it.

Try catchin a PL / SQL expert to help you on this query..!!

Regards,

Bhavesh

[Former Member · Accepted solution]

Hi Bhavesh,

Trying the same. If I am able to solve it as Mario suggested, will defenitely update you.

Thanks & Regards,

Jai Shankar.

[Former Member · Accepted solution]

Hi Bhavesh,

thanks!

[Former Member · Accepted solution]

Hi Jai,

"event_id" is the key to your table.

In nour case change by <name>

Regards Mario

[Former Member · Accepted solution]

Hi All,

This querry works.

select Header.Name, Header.EmpId, Item.Contactnumber from Header,Item where Header.Name = (select min(Header.Name) from Header where Header.Status = 0) and Header.Name = Item.Name

Update SQL :

update Header set status = 1 where name = (select min(name) from Header where status = 0)

Thanks for the idea Mario.

Thanks & Regards,

Jai Shankar.

[Former Member]

Hi Jai,

this one should also very helpful for your problem:

Replication of Nodes Using the Graphical Mapping:

/people/claus.wallacher/blog/2006/04/17/replication-of-nodes-using-the-graphical-mapping-tool

Regards Mario

[Former Member]

Hi ,

you can achieve this one with BPM only.

Sekhar

[bhavesh_kantilal]

Jai,

Dont think this is possible without a BPM.

In thesense, you want the SENDER JDBC adapter 's query to be Dynamic and yet process only one record at a time.. Dont think this can be done without a BPM.

Regards,

Bhavesh

[Former Member]

Hi Bhavesh,

Thanks. If I should use BPM how can I achieve the same?What are the steps in the BPM? I need to send the data to a R/3 using ABAP proxy(asynchronous).

Thanks & Regards,

Jai Shankar.

[bhavesh_kantilal]

Jai,

Did you take a look at my blog,

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

Go through it and let me know if you have any further queries.

Regards,

bhavesh