Subquery within rpd.

mlov83

Hello guys,

Wondering if anyone can lend a hand on this one. I have been going through the exercise of trying to convert current BRIO queries to rpds in OBIEE. I have used the translation workbench in some cases to try to expedite this process. However, I have ran into and issue which has me a bit stomped. In one of my queries withing my where clause I have a bit of code that does the following.

RNL_GRNT_YR =  (select substr(max(decode(rnl_grnt_yr, '96','1996', '97','1997', '98','1998', '99','1999', '20' || rnl_grnt_yr)),3,2) from rnl s where s.rnl_fund_code = rnl.rnl_fund_code and s.rnl_orgn_code = rnl.rnl_orgn_code and s.rnl_acct_code = rnl.rnl_acct_code and s.rnl_prog_code = rnl.rnl_prog_code and s.rnl_actv_code = rnl.rnl_actv_code)

Where in my RPD can I put this sub-query. I have though about the putting it in the model under a logical column but I'm not sure how to accomplish that. So my question is, how and where do I accomplish logic of this kind in my RPD.

Thanks

and forgive me for the noob question.

[Deleted User]

That's not how OBIEE works. We're working with models. Not singular queries.

mlov83

Thanks Christian,

I completely understand that OBIEE does not work in this fashion, however, would you help me understand how I might accomplish something of this nature in an OBIEE model?

Thanks! 

[Deleted User]

The year part you mean? substr(max(decode(rnl_grnt_yr, '96','1996', '97','1997', '98','1998', '99','1999', '20' || rnl_grnt_yr)),3,2)

mlov83

YES. That's the sub query part I'm stuck with. I want to be able to get the max year. I'm basically joining two table alias but from there I'm stuck.

[Deleted User]

Best start from the base: Express your actual requirement / need in prose rather than code. Saying out loud helps us understand and also you to grasp how things are done via what we reply and how we reply. If we just give you a code X you won't get why what is done how.

mlov83

Fair enough Christian,

The  basic step here, is that I'm trying to get the max year for  every

rnl.rnl_fund_code  

rnl.rnl_orgn_code  

rnl.rnl_acct_code  

rnl.rnl_prog_code  

rnl.rnl_actv_code

as one row and than I would like to sum those rows.  In its most basic form that is all I'm trying to accomplish. My apologies with the code I was trying to show a query that basically selects the max year for me now and I though that would be helpful.

[Deleted User]

So I got the max year part. You're getting the max year. That's one. This can be done by LTS merging and creating a MAX(YEAR) measure.

The second part "I would like to sum those rows". What does that mean "those rows"?

mlov83

year is one of the column along with the other columns I mentioned previously.  I would like to identify the max year per row and than sum the amount column for that row.

Thanks again.

[Deleted User]

Ok so that (max year) is classically something which should happen before the RPD in the data sources. Reason being that then you can leverage all functionalities in a much more streamlined fashion.

As Robin said in an other thread: If push comes to shove you always have the possibility of direct database requests (of which I'm not a huge fan) or you can write an opaque view in the RPD which brings in the info.