Hitachi Vantara Pentaho Community Forums
Results 1 to 6 of 6

Thread: Database lookup step in spoon

  1. #1
    Join Date
    Apr 2008
    Posts
    9

    Default Database lookup step in spoon

    Hi,

    I have one table in Database.
    I uesd this table in Database lookup step.
    And i added some condition in like source_field1=lookup_field1
    This step returning some of fields from lookup step.
    Then i have added filter rows step.Here data are passing into two targets.


    when do i run first time that data are loaded into target table.
    I have changed some value on this table.

    When do i run next time, if,some of the condition not matched in filer row step ,then
    i want to insert new row which values are changed in source field.

    my problem is:

    if i run second time , the new row is inserted.
    here actual data row is inserted as last row.
    if i run next time, the same row inserted one more time.

    how to solve the problem.

    for example:

    fisrt time loaded data

    col1,col2,col3
    111,aaa,aaaa
    222,bbb,bbbb

    here i have changed the value for first row is like:
    111,xxx,xxxx

    if i run a transformation,
    the new row is inserted like :
    111,xxx,xxxx

    if i run next time the same record inserting every time.

  2. #2
    Join Date
    May 2006
    Posts
    4,882

    Default

    Your post is a bit confusing, so if you can make it more clear .... attach the transformation e.g.

    Seems like you need to use update/insert with a proper key set on the output side. Of course that way if you would also make changes on the target table they would be overwritten by new changes coming from the source.

    Regards,
    Sven

  3. #3
    Join Date
    Apr 2008
    Posts
    9

    Default Database lkp

    Hi,

    My concern is not like that.

    Actually i tried to implement SCD TYPE 2.

    i have many rows in table for same key column.
    i want to check the source value into last row of lookup table value.
    how to do?

    for example.


    source table
    col1,col2
    1,d

    lkp table
    1,a
    1,b
    1,c
    1,d

    in this example i have 4 rows for same key.
    if i run the transformation then
    it's inserting one new row with same value like.

    1,d

    because this lkp compareing first value(1,a) with value(1,d)
    based on condition (src_col1=lkp_col1 and src_col2<>lkp_col2).

    My requirement is need to check the source value to last row in lkp table.

    thanks and regards,

    Shekar

  4. #4
    Join Date
    May 2006
    Posts
    4,882

    Default

    In your current setup this wouldn't work... and it's not a kettle specific problem.

    How does any application know it has to select the last inserted row for a same set of keys? (guessing the data inserted will not be nicely alphabetically ascending)

    What you could do e.g. is add a timestamp to the row which is generated when a row is inserted and then select the row for a certain primary key with the newest timestamp.

    Regards,
    Sven

  5. #5
    Join Date
    Apr 2008
    Posts
    9

    Default Database lkp

    Hi,

    In informatica, we have option like

    1.ues first value or
    2.use last value or
    3.use any value or
    4.report error.

    If we select option 1, then lkp will check first row from set of rows from same key value.
    like we can check last row also.


    is there any default option like that?


    thanks & Regards
    Shekar

  6. #6
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    You can set the "order by" clause in DB lookup, yes.
    And there are options to make it fail too.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.