Forum Stats

  • 3,734,446 Users
  • 2,246,973 Discussions
  • 7,857,293 Comments

Discussions

ODI create the run time CSV/XLS file based on distinct value in the source Data.

User_HG7TT
User_HG7TT Member Posts: 1 Green Ribbon
edited Jun 11, 2021 10:51AM in Data Integrator

Source is department table, and department number get populated by some other system.

ODI read the department table, find the distinct department number from column on run time.

Create the separate files based on how many department no distinct values in the table.

If the table has 3 distinct value, it will create the 3 separate file.

If the table has 4 distinct value, it will create the 4 separate file.

Output file name we can add the distinct value in the name of the file.

For example if I have 10, 20, 30 department table, I have to create the 3 output files from the source table using variable. In future department number might be added and file should get created automatically. Could you please advise which module I need to look to address this requirement? Or if you can send me the documentation steps that would be really useful.

Thanks a lot.

Comments

  • Christyxo
    Christyxo Member Posts: 98 Bronze Badge
    edited Jun 11, 2021 11:18AM

    You will need to build a package that loops through the distinct values.

    First you want to create a variable v_DEPT_RANK against any logical schema. This is used to increment through the loop. The query here is redundant as the package will control what the value actually is, so just write

    SELECT 
      1 
    FROM
      DUAL 
    

    Then create a variable v_DEPT_ID that points to your table and uses the above variable

    SELECT
      DEPT_ID
    FROM
      (
        SELECT DISTINCT
          DEPT_ID
        , DENSE_RANK ( ) OVER ( ORDER BY DEPT_ID ) RNK
        FROM 
          TABLE
      )
    WHERE RNK = #v_DEPT_RANK
    

    Open the file in the design model and change the resource field to #v_DEPT_ID

    Create a mapping and filter the department id using v_DEPT_ID to populate your file. When your file gets created it should be created with the file name matching the department ID.

    Then you bring everything into a package.

    • First step would be SET VARIABLE v_DEPT_RANK = 1
    • Second step would be REFRESH VARIABLE v_DEPT_ID
    • Third step would be your mapping
    • Fourth step would be INCREMENT VARIABLE v_DEPT_RANK +1

    Then the 4th step would point back to the second step thus completing your loop.

    This will fail when you have completed all distinct values in your loop so you also want to include a step that handles STEP 2 failing. Just point it to a beep, or a sleep component.

    Once you are familiar with this approach there are multiple approaches that you can adopt to clean up the logic but this will be the basic start point. It will look something like this:


Sign In or Register to comment.