group by within answers 11g

3409791

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

Looks like MAX is missing 'BY Po Ordh Order No' ... and the 'Po Ordh Release No' will affect your query grain.

Edit:

Better yet, create a a set of logical columns (PO Min Date & PO Max Date) that uses something similar to following in the physical mapping

MIN(Start date) over (partition by PO Number)

MAX(End date) over (partition by PO Number)

3409791

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

3409791

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

Thomas Dodds

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.