✍ Kashif M
Trends

Sunday, 26 January 2014


Retrieve the latest record (scd) in Informatica


By on January 26, 2014




If we are using emp table as target in scd then the query is



Select * from emp_t a

where a.version = (Select max (b.version) from emp_t b where a.empno=b.empno)

Kashif
mkashu: Retrieve the latest record (scd) in Informatica
Review : Kashif | Kashif
Update: January 26, 2014 | Rating: 4.5

Comment for "Retrieve the latest record (scd) in Informatica"

9 comments:

  1. I believe this query is faster than yours....
    select * from
    (select *,rank over(partition by empno order by version desc) rnk from emp_t a) z
    where z.rnk=1

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. I am new to Informatica.
    Can you please tell me a and b in the query.
    Is a the target lookup table?

    ReplyDelete
    Replies
    1. a and b are alternative names(alias names) for emp_t

      Delete
  4. Hi, Can you plz tell me what is SCD?

    ReplyDelete
  5. @ adesh if they are asking that questions from SCD related,that is scd 2 ,where old and new data are identified by version then,giving answer in interview in using rank query will be wrong,they will get to know that you dont know SCD,which is important in informatica.
    as your technical query is correct.

    ReplyDelete
  6. if version is not maintained,then how can we get latest record.

    ReplyDelete

Blog Archive