Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 15 Oracle Analytics Lounge
- 208 Oracle Analytics News
- 41 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 76 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Subquery within rpd.
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.
Answers
-
That's not how OBIEE works. We're working with models. Not singular queries.
0 -
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!
0 -
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)
0 -
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.
0 -
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.
0 -
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.
0 -
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"?
0 -
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.
0 -
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.
0