need asst.

[ProDev]

Hi,

Here is what, I want to do ......

Source : view in MS-SQL Server
Target : Oracle table.


Source Data
===========

First_Name Last_Name City

Do Wyane CA
Donev Steve NJ
Do Wyane NY
Herz Michael CA




After the load, target needs to have following data.


===========

First_Name Last_Name Use_Name

Do Wyane WDo_CA
Donev Steve SDonev
Do Wyane WDo_NY
Herz Michael MHerz




Note :- User_Name is unique key in the target.


Business rule is such that,

User_Name should be "1st letter of Last_Name + First_Name"
Since there may be 2 persons with same name ("Do Wyane" in this case) the User_Name should be

"1st letter of Last_Name + First_Name"+"_"+City

in such special case.


This is to avoid unique key constraint error at the target for the User_Name column.



Every thing is fine for me. Big problem is, to comapre with previous row in order to determine User_Name for the persons
with same name staying in different cities.


Thanx

 

[blom0344]

You may want to change some of your business rules. Primary keyes should NOT be composed this way.........

Suppose someone moves to another city! If you want to keep this policy, you can just as well add a sequence generator to keep things unique.

Do you use the primary key in enforcing ref. integrity? If not, then use a surrogate key....

T. Blom
Information analyst
tbl@shimano-eu.com

 

[ProDev]

Well, the business rule is such that, there will never be entries in source table for 2 persons having same first name & last name, staying in same city.

 

[blom0344]

Sure that may be the rule in your case, however puzzling such a rule appears to be, but why create a primary key on such an akward set?

T. Blom
Information analyst
tbl@shimano-eu.com

 

[Sridharan]

Blom's concern is a good one from DW perspective and you shud look at that one... Maybe not now but in future when the B Rules changes(which I'm sure will) it will be bottleneck... Anyway to work with your situation you shud pass the rows in a sorted order from the Source and check for the Previous Row value with the Current Row Value to see if there are two First Name or Last Name as your case maybe and then use a Expr to concatenate if the City with a _ Prefix if not concantenate First Letter of First Name and Last Name and send to the target...

Sri