jdbc sender issue

[Former Member]

we have delivery_status field is mentioned in FS .The adapter picks up all data where the delivery_status is “N”. When the data is picked up the delivery_status will be set to “C” by the adapter .BUT this field is NOT there in pub_invoice verficationheader table and also this field is not MENTIONED IN MAPPING SHEET.but it is there in p_pub_invoice_verification_header table.KINDLY CONFIRM we have to write stored procedures or we can retrivve the in this way SELECT * FROM PUB_INVOICE_VERIFICATION_HEADER WHERE (SELECT * FROM P_PUB_INVOICE_VERIFICATION_HEADER WHERE DELIVERY_STATUS = 'N').

kindly give me needful solution.

[Former Member]

Hi,

the solution is clearly a stored procedure.

Inside your SP:

1. do your select and return your statement.

2. update your tables with the good flag "C" instead of "N", for all objects found in the previous SQL.

Inside your CC sender:

just execute your SP.

clearly it's the best solution for you.

Mickael

[Former Member]

hi michel

we have delivery_status field is mentioned in FS .The adapter picks up all data where the delivery_status is “N”. When the data is picked up the delivery_status will be set to “C” by the adapter .BUT this field is NOT there in pub_invoice verficationheader table and also this field is not MENTIONED IN MAPPING SHEET.but it is there in p_pub_invoice_verification_header table.KINDLY CONFIRM we have to write stored procedures or we can retrivve the in this way SELECT * FROM PUB_INVOICE_VERIFICATION_HEADER WHERE (SELECT * FROM P_PUB_INVOICE_VERIFICATION_HEADER WHERE DELIVERY_STATUS = 'N').

i am writing below statement

SELECT * FROM PUB_INVOICE_VERIFICATION_HEADER WHERE tiket_number in ( (SELECT ticket number from PPUB_INVOICE_VERIFICATION_HEADER WHERE DELIVERY_STATUS = 'N').

its working in sql database.but its not working in xi.but ccis showing fine.in sxmb_moni is not showing any message.

2) i want retive 10 fields from p_pub_invoice_verification_header table.BUT IN THAT TABLE THERE IS NO DELIVERY_STATUS FIELD.ITS THERE IN P_TABLE 1.THAT FIELD WE HAVE TO UPDATE N TO *C.*WHAT WE ARE DOING WE ARE RETRIVING DATA BASED ON PRIMARY KEY .FOR THAT WE ARE USING ABOVE STATEMENT.

3)KINDLY SUGGEST ME HOW TO WRITE STORED PROCEDURE FOR ABOVE ONE.IF AM VERY MUCH HAPPY IF GIVE SOME EXMPLE CODE FOR ABOVE ONE

THANKS

NAG

[nikhil_bose]

If you don't want to disturb current Message Type and Message Mappings you can ignore delivery_status field.

2 ways

1) specify the fields in the select statement ( bad idea if number of fields are many)

2) create stored procedure in db.

nikhiL

[Former Member]

hi nikhil

could pls send the SP code for above scenario.

[Former Member]

Hi Nagendra,

As you said i will tell you what i understood then i will try to clear your doubt.

Your are getting some data from one DB(is that DB is FS as you mentioned in the thread. Correct me if i am wrong) .

You selecting that data with a condition delivery_status = 'N' and then you update the DB by setting the same value ie, delivery_status to 'C'. (correct if i am wrong)

Now my question is from which DB table you want to select this data and update the same using some queries.

And please clarrify about this two tables like,

1.pub_invoice verficationheader

2. p_pub_invoice_verification_header

Please let me know what is the exact scenario on which you are working currently and what do you want in results.

Soorya.

[Former Member]

pub_invoice_verification_header

[Former Member]

Hi ,

You have to first ensure from where You need to fetch the data and update condition is used to restrict the further processing of the message already read.

If you are reading from multiple table throu join you can still update.

Yes you can retrieve in this way and then update the next table

REgards

Edited by: BeyondThe obvious on Apr 15, 2008 8:27 AM