Dimension with subquery

building semantic layer
1

Hi,

I have a Table MGR is having 2 columns. one is employee ID and other is his manager’s emp id
Table MGR is:
EMP_ID , MGR_EMP_ID
201 101
202 101
203 101
301 102
302 102
303 102
101 90
102 90
90 51

I have created a dimension MGR_Level_1 for the subquery:select MGR.EMP_ID from MGR where MGR.MGR_EMP_ID = 51

I have tried to create a dimension “MGR_Level_2” for this query:
select MGR.EMP_ID from MGR where MGR.MGR_EMP_ID in
(select MGR.EMP_ID from MGR where MGR.MGR_EMP_ID = 51) – should return 101,102

I put in SELECT clause: MGR.EMP_ID
WHERE clause: MGR.MGR_EMP_ID in (@Select(Classname\MGR_Level_1))

Unfortunatly dimension “MGR_Level_2” is not returning any data.

Please suggest where i went wrong?

Thanks

mithranclt (BOB member since 2008-10-14)

2

Hi,

What are you trying to achieve?

Which database do you use?

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

Hi,

above code is not working because Where condition used in the object “MGR_Level_1” does not get applied in the object “MGR_Level_2” if you use it in where condition MGR.MGR_EMP_ID in (@Select(Classname\MGR_Level_1)).

The resulting query of “MGR_Level_2” would be-

select MGR.EMP_ID from MGR where MGR.MGR_EMP_ID in
MGR.EMP_ID

which will not display any data since MGR.MGR_EMP_ID will not be equal to MGR.EMP_ID.

To solve this problem, use either case or decode in “MGR_Level_1” object like - case when MGR.MGR_EMP_ID = 51 then MGR.EMP_ID end

Hope this helps.

soni_ak :india: (BOB member since 2008-09-15)

4

Thanks! it helps!

mithranclt (BOB member since 2008-10-14)