Skip navigation
Contact Us
6036 Views 5 Replies Latest reply: Apr 17, 2013 11:15 PM by Maheshkumar Panati RSS
Dream weaver Novice 59 posts since
Sep 6, 2010
Currently Being Moderated

Oct 19, 2010 9:24 PM

how to update a flat file target? if not possible how can i implement this logic

hi,

  in my mapping i am  having mutliple  files and only one  target output Flat  file,and i need  to implement  below logic.Can any one  suggests me an idea ,how to do  it?

input

------

file1:

field1  field2    field3

1        A           B

2        C           D

3        E           F

 

file2:

4       G           H

1        I            J

5        K           L

 

file3:

4      M           N

6      O           P

here   i am reading three different files in the order File3,file2,file1 .The   logic i needed is , for  example if the record corresponding to '1' is   present in multiple files  ,then i need to write the record which is   present in the first file and  discard the records corresponding to 1  in  the rest rest of the files.My target is a flat file  and i tried  with update strategy but i had later found that "update concept" wont  work with flat files. So please suggest another way to get this logic

 

output

--------

6    O    P

4    G    H

5    K    L

1    A    B

2   C     D

3    E    F

 

regards

angel

  • Sumit Shegokar Newbie 9 posts since
    Sep 5, 2009

    It would be two step design:

     

    First step:

    Create a one to one mapping loading data from files and loading into a stage table  (RDBMS). While loading data into stage, be sure  that the records Integration service is loading should be loaded with their corresponding file name (file name of file in which those records were present).

     

    In INFA 8.5/8.6 we have an option that helps to extract the file name of a file of which integration service is reading records.  It is there in source definition of imported file structure. Extract port gets created and we can map that port with a port (Src_FileName) that we have created in the stage table.

    Structure of Stage table:

    (

    Field1,

    Field2,

    Field3,

    Src_fileName varchar2(50 char)       -- New attribute need to add in the stage table.

    )

     

    So the stage data should be like below after load:

     

    Field1    Field2    Field3    Src_FileName

    1              A             B            File1

    2              C             D            File1

    3              E             F             File1

    4              G            H             File2

    1              I              J              File2

    5              K            L               File2

    4             M           N              File3

    6              O            P             File3

     

    Consideration for STEP 1:

    -        -  You have rights to create & truncate the stage table. 

    -        -  You have INFA PC 8.5.x onwards version.

     

    Note:

    - Load strategy –Truncate load.

     

    Once the stage is created:

    Step 2:

    Create a mapping with RDBMS (Stage table previously created) as the source and flat file as the target:

    SQ Override:

     

    SELECT

                    SRC.FIELD1,

                    SRC.FIELD2,

                    SRC.FIELD3

    FROM

                    STAGE TABLE AS SRC,

    (

    SELECT

                    FIELD1,

                    MAX (SRC_FILENAME) RECENT_FILE_NAME

      FROM

                    STAGE TABLE

    ) RECENT_VALUE

    WHERE

    SRC.FIELD1 = RECENT_VALUE.FIELD1

    AND

    SRC. SRC_FILENAME = RECENT_VALUE. SRC_FILENAME;

     

     

    Consideration for STEP 2:

    -       -   We are reading data in descending order as said in your example (file3, file2, file1). So if found in file3 then consider an instance of file3.

    -       -   Source files are named properly, so that max/min will pick the proper (Recent file) file.

     

    HoHope this will help....

  • Maheshkumar Panati Newbie 11 posts since
    Nov 16, 2012

    We can implement this using dynamic lookup.. I have tried this, Working fine

More Like This

  • Retrieving data ...

Bookmarked By (0)