Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

group by within answers 11g

Received Response
21
Views
4
Comments
3409791
3409791 Rank 2 - Community Beginner
edited Aug 13, 2024 7:26PM in Oracle Analytics Cloud and Server

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

test.png

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

Rj

Answers

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    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
    3409791 Rank 2 - Community Beginner

    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)

    Test2.png

    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

    Test3.png

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

  • 3409791
    3409791 Rank 2 - Community Beginner

    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

    Test5.png

    This is from BMM Layer

    Test4.png

    Thanks a lot for your time,

    Rj

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    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.