How To: Refresh only rows having specific column value from a source table to target

Viewing 1 post (of 1 total)
  • Author
    Posts
  • #15238
    Pallavik
    Keymaster

    How To:  Refresh only rows having specific column value from a source table to target

    Scenario:

    Imagine you have a business requirement to filter only rows with specific column value from a source table into the target. For example you have an Employee table in the source database with columns ‘ID’,‘Name’, ‘Address’ and Status. Now you want to load only employees who are valid. In other words rows which have Status column with value ‘valid’. Below we will see how this can be achieved using HVR’s Restrict/RefreshCondition on source and Target groups.

    Overview:

    In our case the /RefreshCondition will be {status}=’valid’ which means that we want to refresh only the rows which have column value as ‘valid’.

    Notes:

    1.The column name is always surrounded by curly braces as a part of syntax and it is case sensitive.

    2.Correct column name can be found by right clicking on the table name in HVR Console and checking the Properties for the table. For example, in our case, column name ‘status’ is in UPPERCASE in Oracle database but in HVR GUI it is in lowercase so we will use lowercase.

     

    Pre-requisites:

    1) A channel chn1 is already present with Oracle source and SQL server Target

    2) Table Employee is already added to the channel chn1 which looks like below and we want only rows which are valid. In this case rows with ID 2, 3 and 5

    3) Action Capture and Integrate are already defined for the channel chn1

    Steps:

    Step A] Add action Restrict/ RefreshConditon on the source group

    1.Right click on the channel select Restrict under New Action

    2.In the pop up window check box for /RefreshCondition for source group and table employee

     

    3.In the space provided next to option /RefreshCondition type {status}=’valid’

     

    4.Click on OK to add action to the channel

    Step B] Add action Restrict /RefreshCondition on Target group

    1.Right click on the channel select Restrict under New Action

    2.In the pop up window check box for /RefreshCondition for target group and table employee

    3.In the space provided next to /RefreshCondition enter {status}=’valid’

    4.Click on OK to add action to the channel

    5.The actions on this channel should look like in below image

     

     

    Step C] Perform Refresh by right clicking on the channel chn1 and checking box for Refresh data.

    1.Right click on channel and Click on HVR Refresh

    2.In the pop up window select the table(s) that need to be refreshed

    3.In the options tab check box for Refresh Data. Select Bulk Granularity or Row by Row Granularity depending on the user requirement.

    4.Next step would be to check box for Create Absent Tables if you know that tables are not present on the target database. When this option is checked automatically the next option for Alter or Recreate Existing Tables is checked. And depending upon users need they can select radio button for ‘if mismatched’ or ‘Always Recreate’

    5.In our case I am selecting Refresh Data and Create Absent Tables with option Always Recreate

    6.Click on Refresh to start Refresh

    7.As we expected we see only 3 rows refreshed to the target

    8.Below is data on Target after refresh is performed

     

Viewing 1 post (of 1 total)
  • You must be logged in to reply to this topic.

© 2020 HVR

Test drive Contact us