group by within answers 11g

Question

Hi All,

Thanks for your time - I have this simple query in OBIEE answers 11g - in the RPD there is an OPV for this data

I am trying to get a MIN and MAX for the given PO Order NO but for some reason when I use the MIN(start date BY ORDER_PO_NO) it works fine

but when I add the MAX(end date BY ORDER_PO_NO) it is again showing the min values instead of giving me the min of those dates

I only want the MIN of State_Date BY PO_ORDER_NO and MAX of END_DATE BY PO_ORDER_NO at the same time I need to show the PO_RELEASE_NO in the report

From the Below screenshot - MIN is right but when I add the MAX(MAX_END_DT BY PO_ORDR_ORDER_NO) it is failing - instead of 01 Oct 14 it is listing all the dates

when I looked athe SQL generated it is very confusing - any suggestions really appreciated

Rj

Thomas Dodds · Answer

Why are you using opaque views?  These are performance killers (no predicate filtering).

This should be easily done in the BMM with physically mapped logical columns - the OPV is standing in your way.

3409791 · Answer

Hi Thomas,

In the RPD I have a very complex query - do you think I can specify the MIN() OVER (PARTITION BY PO_ORDER_NO) in the query directly

or

do you think it is good to specify in the BMM layer

This is from Physical layer - SQL in OPV

This is from BMM Layer

Thanks a lot for your time,

Rj

3409791 · Answer

Hi Thomas,

thank you so much for your time - it is giving me the correct result when I use either MIN or MAX - so what I have done is in the answers I added one more column PO_ORDER_NO with a function of LENGH(PO_ORDER_NO)

to see if there is any extra space  - then in the "Advanced" tab I specify the group by to use the second column which is LENGTH(PO_ORDER_NO) first column is just PO_ORDER_NO

in the criteria tab I specified MIN(START_DATE BY PO_ORDER_NO) MAX(END_DATE BY PO_ORDER_NO)

below is from advanced tab - I could not figure out why it is not giving me the same result as above when I specify the group by with saw_0 which is PO_ORDER_NO

I really appreciate your time - this is so confusing or I am missing something