locked
Querying the Database RRS feed

  • Question

  • We  receive a file from the client that we insert in to the Table A in the Database. The file we receive are like below

    RUN_GROUP SEQ_Number COMPANY OLD_COMPANY OLD_ACCT_NBR  SOURCE_CODE R_DATE REFERENCE
    ABCD01 1 1 12345 45678       AB 20161201 9876
    ABCD01 2 1 54321 87654       AB 20161201 6789
    ABCD01 4 1 12435 45687       AB 20161201 6790

    After that a process runs and it updates/inserts the data in another table B. Now we will have to select those particular records from Table B where References are 9876, 6789, 6790 (which we received from the incoming file) and create a .csv file and send back to the Client. As the Reference will be changing everytime, I am not sure how to approach. Can anyone please suggest a way to deal with this.


    • Edited by vdha Friday, December 9, 2016 2:01 PM
    Friday, December 9, 2016 1:51 PM

Answers

All replies

  • Hi,

    one you receive your file from the client, if you can use orchestration for Updating/Inserting Data into Table B.

    you can promote the Reference filed in your schema and Create a correlation Type on it. On the Send Port you can initialize the correlation set.

    then, we can create a receive port which follows the same correlation set and poll these records from the Table by passing these references and then you can create a csv file.

    I might not be exact but we can achieve in this way.

    Thanks,

    Sujith.


    Sujith

    Friday, December 9, 2016 2:08 PM
  • How I create a corelation Type on the promoted propety. The issue is the process are ran manually and we will have to send the confirmation csv the next day.
    Friday, December 9, 2016 3:15 PM
  • Promote Property as above which is REFERENCEField here.
    • In Orchestration View create Message which is going to be used in this Orchestration.
      1. Identifier -> InputMessage,  Message Type -> BizTalkCorrelation.InputMessage
    • In Orchestration View create Correlation Type and Correlation set
      1. CorrelationType -> MyCorrelationType (Correlation Properties -> BizTalkCorrelation.REFERENCE)           
      2. Correlation Set -> MyCorrelation(Correlation Type -> BizTalkCorrelation.MyCorrelationType )        
    Friday, December 9, 2016 4:24 PM
  • Is it always records 9876, 6789, 6790?

    Or is it all records that came in the last file?

    Friday, December 9, 2016 5:41 PM
    Moderator
  • Reference value keeps changing each time on the file we receive. So we need to use them in the selecting from the table B.
    Friday, December 9, 2016 6:12 PM
  • can you give more details like

    is this file which you receive from the client contains a lot of references like reference here is part of a repeatable record?

    once you receive it you will push this data into table B after that when do we pick this data the next day based on the reference fields which you received on previous day?

    Thanks,

    Sujith.


    Sujith

    Friday, December 9, 2016 7:33 PM
  • Can you store the 'reference value' list in the database as well?

    Or, even better, flag the records when saving them?
    Friday, December 9, 2016 8:11 PM
    Moderator
  • The complete process is.

    1. We receive a file from the client which has a column called REFERENCE (for example 9876, 6789, 6790 and it keeps changing in every file). We just receive file and insert them in to the Table A in the Oracle Database.

    2. After the BizTalk insert the values in to Table A. In business side they run manually a process and it takes records from Table A and updates/inserts them in to Table B and the process also does other stuffs too.

    3. What now I will have to do is, select the fields from Table B where the REFERENCE is equal to the values we received in the File we received initially (eg: 9876, 6789, 6790), make them in to .csv and send them.

    Now I am not how to start of with the step 3.

    Friday, December 9, 2016 8:38 PM
  • Johns,

    Are you asking to store the Reference Values seperately in a table??  The Reference Values are present in both Table A and Table B. The issue is intially I am inserting the received files in Table A. But for confirmation file I will have to select the records from the Table B.

    Friday, December 9, 2016 8:42 PM
  • You would store them in a separate 'pending references' table.

    Then when the business process is done, you can join the pending ref table to table B to get all the results you need.

    Friday, December 9, 2016 9:45 PM
    Moderator
  • Johns. I am just checking if there are other way of doing this. I am afraid that we will not be able to create a new table for references. I will check with the DB person too.
    Monday, December 12, 2016 2:47 PM
  • Sure, keep in mind, the DBA is usually wrong :().

    While you might not be able to create at table in an app database you don't own, you should be able to create one in a database you do own then link them.

    However, that might be a bit more complicated than you need.

    Storing the 'reference' list in a long running Orchestration is another option.  You can then use a Table Value parameter to join within the B retrieval query:  See here: http://social.technet.microsoft.com/wiki/contents/articles/24803.biztalk-server-sql-patterns-for-polling-and-batch-retreive.aspx

    • Proposed as answer by Rachit SikroriaModerator Monday, December 19, 2016 6:54 AM
    • Marked as answer by vdha Thursday, January 5, 2017 6:40 PM
    Monday, December 12, 2016 5:38 PM
    Moderator
  • I am checking if there is any possibility of us to save the references column values that we received inside BizTalk. So it can be used to in the querying the Table B. Instead of storing them in a separate table and joining them. Just checking.
    Wednesday, December 14, 2016 8:44 PM