Family OWB Mapping

This post is in response to a question raised by a OWB Developer on OTN Forum, follow the link for full history - http://forums.oracle.com/forums/thread.jspa?threadID=509336&tstart=0.

The issue is -

Hi Mahesh,

Hope you have a nice weekend :)

That is what i don't know how to achive this through OWB mapping.

I will repeat again. I have data in this format.

ID | FamilyNum | RelationType
-------------------------------------------
100 1000 Head
850 1000 Wife
1100 1000 Son

and i need records in connection table as below:

ID | FamilyNum | RelationType
-------------------------------------------
100 850 Husband
850 100 Wife

100 110 Father
1100 100 Son

If you observe this is kind of opposite entry for each record.

This is very simple in PL/SQL coding. I thought use Cursor Loop in OWB, but don't find in help. If this is not possible then i implement this in SP and call it from OWB. No other option.

Thanks,
Danish


The Solution is -

OWB Map
-----------------



1. sources

HEAD FAMILY Table for Head of the Family
REST FAMILY Tale for rest of the family

2. The Join Condition is

HEAD.RELATION_TYPE IS NULL
AND HEAD.FAMILY_KEY = REST.FAMILY_KEY
AND REST.RELATION_TYPE IS NOT NULL

3. From the Output of Join To SET Operator (UNION ALL)

4. SET Operator has 2 Ingroups REST & REST_HEAD
The Ingroup REST takes input straight from OUTGRP of JOIN operator
The Ingroup REST_HEAD takes input from the OUTGRP of EXPRESSION.

5. The Expression ( this is to generate relation record for each family member ie Husband / Father ) has 3 input attributes

REST.RELATION_TYPE
REST.MEMBER_ID
HEAD.MEMBER_ID

From OUTGRP of JOIN.

6. The Output attributes and the business rules of Expression
FAMILY_NUM
(For this I am assuming that you are taking first 3 digits of the family member_id to get the Family Num of Father / Husband )

TransformationRule - TO_NUMBER(SUBSTR(TO_CHAR(INGRP1.R_MEMBER_ID,1,3 )))

MEMBER_ID

TransformationRule - INGRP1.H_MEMBER_ID

RELATION_TYPE

Transformation Rule -
CASE INGRP1.R_RELATION_TYPE
WHEN 'Wife' THEN 'Husband'
WHEN 'Son' THEN 'Father'
WHEN 'Daughter' THEN 'Father'
WHEN 'Husband' THEN 'Wife'
ELSE 'Unknown'
END

7. Map straight from the SET operator to Target Table.



Test Case

-----------------------
-- Source Table
-----------------------

desc family


MEMBER_ID NUMBER
FAMILY_KEY NUMBER
RELATION_TYPE VARCHAR2(30)

SELECT * FROM FAMILY ORDER BY FAMILY_KEY

100 1000 (null)
850 1000 Wife
1100 1000 Son

1500 2000 Son
500 2000 (null)
9000 2000 Husband
200 2000 Daughter

Two Set of records ( Family Key 1000, 2000) . One Family has 3 records and the other has 4 records including Head of Family.

----------------------------------------
--Before Running the OWB Map
-----------------------------------------

--Target Table structure

DESC TGT_FAM
FAMILY_NUM NUMBER
MEMBER_ID NUMBER
RELATION_TYPE VARCHAR2(30)
FAMILY_KEY NUMBER

4 rows selected

SELECT * FROM TGT_FAM


FAMILY_NUM MEMBER_ID RELATION_TYPE FAMILY_KEY
---------------------- ---------------------- ------------------------------ ----------------------

0 rows selected


--------------------------
--Run the OWB Map
-- Check the results
-----------------------------

SELECT * FROM TGT_FAM ORDER BY FAMILY_KEY

FAMILY_NUM MEMBER_ID RELATION_TYPE FAMILY_KEY
---------------------- ---------------------- ------------------------------ ----------------------
100 1100 Son 1000
100 850 Wife 1000
850 100 Husband 1000
110 100 Father 1000

150 500 Father 2000
200 500 Father 2000
900 500 Wife 2000
500 1500 Son 2000
500 200 Daughter 2000
500 9000 Husband 2000

10 rows selected