Question
Inovar
SA
Last activity: 12 Feb 2020 6:30 EST
Fetch Data from External DB Multiple tables using Joins
Hi Team,
I have an requirement to connect to Oracle DB and fetch data from multiple tables using Join Query. I have created an Data base and database table as well.But my query is " How to map single pega class to mutiple tables of external DB?
Below is Query to be used:
select be.design_id,us.full_name, be.status,dname.attribute_value as DESIGN_NAME from bam_dba.O2B_Business_Entity be left join bam_dba.O2B_Business_Entity_Details bed on bed.entity_id = be.entity_id and bed.attribute_key ='CREATED_BY_EID' left join bam_dba.O2B_Business_Entity_Details dname on dname.entity_id = be.entity_id and dname.attribute_key ='DESIGN_NAME' left join BAM_DBA.O2B_REF_USER us on bed.attribute_value = us.EID where be.source_system = 'VISE' and be.design_id in ('543997');
Regards
Gowtham.
Adept view
IN
Hi Gowtham,
Please follow the below steps to map columns to Pega class.
1. Create class based on your class structure
2. Create connect SQL rule in the same class with the above query
3. In activity use RDB-List method and specify the connect SQL rule created in step 2 with step page pointing to class that has been created in step 1
4. Have alias name for the columns like dname.attribute_value as DESIGN_NAME in the above query
be.design_id as DesignID,us.full_name as Fullname, be.status as Status
5. create properties in the class created in step 1 For Example: DESIGN_NAME as this was already used in the query
you can create DesignID, Fullname, Status properties with respect to be.design_id,us.full_name, be.status columns
Once above steps are done, execute the activity created in step 3. you can find the properties mapped on the step page.