how to write multiple queries in sender jdbc adapter

[Bhargavakrishna]

Hi experts,

i have a requirement where i need to fetch the data, from sql server and should placed it in sap directories.

while performing validations, if any record not satisfy the condition, that particular records should store in separate folder.

the immediate next day, we have to fetch the previous day records, which are failed in validation.

for ex:   input                                                                        out put

            empid, date, login, logout                                     empid, date, login, logout

if suppose employee forgot to log out, that record should be moved to un-successful folder.

next day we should fetch the today's data, along with yesterdays records which don't have the out time.

my question is how pi will know, what records to be fetched from the database.

can we write multiple queries in sender jdbc adapter?

how to achieve this..

[Ryan-Crosby]

Hi Bhargarva,

We have done this a couple of times in some scenarios in our landscape.  I have included screenshots of the request/response message types where you could run multiple JDBC queries synchronously and pass those responses back.  Also, I wasn't sure what the key would be in your "today" query so I just guessed and put login but that would be based on your requirement.

Request:

Response:

Regards,

Ryan Crosby

Message was edited by: Ryan Crosby

[Former Member]

Hello,

Why don't to create a SP in DB itself and let DB handle this kind of logic?

Thanks

Amit Srivastava

[Former Member]

Hi,

All you need to do is write joins ..if the data is stored in multiple tables...

whatever query you write in sql editor can be provided in PI...

HTH

Rajesh

[Bhargavakrishna]

Hi all, thanks for your reply.

Hi Gagandeep Batra,

can you explain how to specify condition in receiver determination. i have not worked on this.

Hi rajesh,

data should be fetched from single table. as per my requirement, we should fetch the previous day records of emp, who don't have the out time. along with today's records. so i have to write a query in such a way that it should fetch both the records.

can you explain, how to do this, with joins..

[baskar_gopalakrishnan2]

You might want to check this link for specifying condition using xpath in Receiver determination

http://wiki.sdn.sap.com/wiki/display/XI/Xpath+Condition+in+Receiver+Determination

[gagandeep_batra]

Hi Bharava,

You can refer the link provided by bhaskar,

what you have to do is create two Receiver CC ,2 Receiver Agreement, and 2 ID and one RD

define condition using X-path..

Regards

Gagan

[gagandeep_batra]

Hi Bhargava ,

you can handle this in single query,

select * from emp where (indate = todaydate and outdate= todaydate) or (indate = yesDate and outdate = NULL) or (indate = todaydate and outdate = NULL).

and then create condition in reciver determination about todaydate field  which is not successfull ie. outtime of today is not there. put it on unsuccessful folder rest all will go to the successful folder...

Hope this help..:)

Regards

Gagan

[smavachee]

Hello Bhargava,

Can you check at

Hope it helps.!

Regards,

Sunil