Skip to Content
0
Former Member
Feb 01, 2016 at 07:23 AM

Filtering Data in Composite Provider

5287 Views

Hi All,

I am trying to create a Composite Provider, where I want to join the data from two infoproviders. I am doing this in BW 7.4 SP11 system, which is on HANA database. I am trying to achieve something like below,

SELECT PROV1.A, PROV1.B, PROV1.C, PROV2.X, PROV2.Y, PROV2.Z

FROM INFOPROVIDER1 AS PROV1

LEFT OUTER JOIN INFOPROVIDER2 AS PROV2 ON PROV2.X = PROV1.A AND PROV2.Y = 'SAP';

I want to get all the records from provider 1 and from provider 2 I want only those records where Dimension Y = 'SAP'. For example, if Provider 1 has 100 records and Provider 2 has 20 records where X = A and Y= SAP, I am expecting output to have all 100 records from provider 1 and columns X, Y & Z are populated only for the 20 rows where the join condition is matching (as I am using LEFT OUTER JOIN).

So while creating the composite provider, provider 1 is added as a UNION and provider 2 is added as a JOIN using LEFT OUTER JOIN. Dimension A from provider 1 is mapped to dimension X from provider 2 for join condition and dimension Y is added as a separate field (not mapping to any field from provider 1) to the composite provider. When I pass filter value 'SAP' to the field Y, the output of the composite provider has only those records where Y = 'SAP', I meant to say it is fetching only those 20 records (as explained in the above example) and is behaving something like below,

SELECT * FROM ( SELECT PROV1.A, PROV1.B, PROV1.C, PROV2.X, PROV2.Y, PROV2.Z

FROM INFOPROVIDER1 AS PROV1

LEFT OUTER JOIN INFOPROVIDER2 AS PROV2 ON PROV2.X = PROV1.A )

WHERE Y = 'SAP';

Any pointer on the above issue, would be of great help!!

Thanks & Regards,

Vishwa