cancel
Showing results for 
Search instead for 
Did you mean: 

Sender JDBC adapter

Former Member
0 Kudos

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.

View Entire Topic
Former Member
0 Kudos

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
0 Kudos

Hi Mario,

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

Thanks & Regards,

Jai Shankar.

Former Member
0 Kudos

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
Active Contributor
0 Kudos

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
0 Kudos

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
0 Kudos

Hi Bhavesh,

thanks!

Former Member
0 Kudos

Hi Jai,

"event_id" is the key to your table.

In nour case change by <name>

Regards Mario

Former Member
0 Kudos

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.