This discussion is archived
3 Replies Latest reply: Oct 30, 2013 9:32 AM by DougP RSS

Hyperion IR Derived Queries

chi_bull Newbie
Currently Being Moderated

Hello,

 

I'm working with a report in Hyperion Interactive Reporting Studio.

 

My query joins a number of derived queries and has two computed items as part of the request. See definition below:

Quantity Shipped: coalesce(RLT_SHIP_Query.Lot_Quantity, RST_SHIP_Query.Serial_Quantity, Rcv_Shipment_Lines.Quantity_Shipped )

Quantity Received: coalesce(RLT_RCV_Query.Lot_Quantity, RST_RCV_Query.Serial_Quantity, Rcv_Shipment_Lines.Quantity_Received )

 

RLT_SHIP_Query,

RST_SHIP_Query,

RLT_RCV_Query, and

RST_RCV_Query are all derived queries.

 

RST_RCV_Query sql:

SELECT AL1.SHIPMENT_LINE_ID, AL1.SERIAL_NUM, SUM ( 1 ) FROM APPS.RCV_SERIAL_TRANSACTIONS AL1, APPS.RCV_TRANSACTIONS AL2 WHERE ( AL1.TRANSACTION_ID = AL2.TRANSACTION_ID (+))  AND ((AL1.SERIAL_TRANSACTION_TYPE='TRANSACTION' AND AL2.DESTINATION_TYPE_CODE(+)='INVENTORY')) GROUP BY AL1.SHIPMENT_LINE_ID, AL1.SERIAL_NUM

 

When I try to process the query I get the error "Oracle Error -1: ORA-00904: "RST_RCV_QUERY"."SERIAL_QUANTITY": invalid identifier"

 

When I remove that column or substitute a hardcoded value the query runs fine. I don't get any error with the other compute item that is defined similarly.

 

The SQL generated by IR Studio does not include the derived query "RST_RCV_QUERY" and when I try to add it via custom SQL I get the error "Oracle Error -1: ORA-00903: invalid table name"

 

Does anyone know why this derived query isn't being included in the sql statement automatically generated?

 

SELECT AL1.SHIPMENT_LINE_ID, AL2.SHIPMENT_NUM, AL3.SEGMENT1, AL3.ORGANIZATION_ID, AL3.INVENTORY_ITEM_ID, AL4.lot_num, AL6.serial_num, AL5.expiration_date, coalesce(AL4.lot_quantity, AL6.serial_quantity, AL1.QUANTITY_SHIPPED ) , coalesce(AL5.lot_quantity, RST_RCV_Query.Serial_Quantity, AL1.QUANTITY_RECEIVED )  FROM APPS.RCV_SHIPMENT_LINES AL1, APPS.RCV_SHIPMENT_HEADERS AL2, APPS.MTL_SYSTEM_ITEMS_B AL3, (SELECT D3AL1.SHIPMENT_LINE_ID AS shipment_line_id, D3AL1.LOT_NUM AS lot_num, SUM ( nvl(D3AL1.QUANTITY,0) ) AS lot_quantity FROM APPS.RCV_LOT_TRANSACTIONS D3AL1 WHERE (D3AL1.LOT_TRANSACTION_TYPE='SHIPMENT') GROUP BY D3AL1.SHIPMENT_LINE_ID, D3AL1.LOT_NUM) AL4, (SELECT D4AL1.SHIPMENT_LINE_ID AS shipment_line_id, D4AL1.LOT_NUM AS lot_num, D4AL3.EXPIRATION_DATE AS expiration_date, SUM ( nvl(D4AL1.QUANTITY, 0) ) AS lot_quantity FROM APPS.RCV_LOT_TRANSACTIONS D4AL1, APPS.RCV_TRANSACTIONS D4AL2, APPS.MTL_LOT_NUMBERS D4AL3 WHERE (D4AL1.TRANSACTION_ID=D4AL2.TRANSACTION_ID AND D4AL1.ITEM_ID=D4AL3.INVENTORY_ITEM_ID AND D4AL3.ORGANIZATION_ID=D4AL2.ORGANIZATION_ID AND D4AL1.LOT_NUM=D4AL3.LOT_NUMBER)  AND ((D4AL1.LOT_TRANSACTION_TYPE='TRANSACTION' AND D4AL2.DESTINATION_TYPE_CODE='INVENTORY')) GROUP BY D4AL1.SHIPMENT_LINE_ID, D4AL1.LOT_NUM, D4AL3.EXPIRATION_DATE) AL5, (SELECT D5AL1.SHIPMENT_LINE_ID AS shipment_line_id, D5AL1.SERIAL_NUM AS serial_num, SUM ( 1 ) AS serial_quantity FROM APPS.RCV_SERIAL_TRANSACTIONS D5AL1 WHERE (D5AL1.SERIAL_TRANSACTION_TYPE='SHIPMENT') GROUP BY D5AL1.SHIPMENT_LINE_ID, D5AL1.SERIAL_NUM) AL6 WHERE ( AL1.SHIPMENT_LINE_ID = AL4.shipment_line_id AND  AL4.shipment_line_id = AL5.shipment_line_id AND  AL4.lot_num = AL5.lot_num AND  AL1.SHIPMENT_LINE_ID = AL6.shipment_line_id AND AL2.SHIPMENT_HEADER_ID=AL1.SHIPMENT_HEADER_ID AND AL1.ITEM_ID=AL3.INVENTORY_ITEM_ID AND AL1.FROM_ORGANIZATION_ID=AL3.ORGANIZATION_ID)  AND (AL2.SHIPMENT_NUM='278009')

  • 1. Re: Hyperion IR Derived Queries
    DougP Explorer
    Currently Being Moderated

    What version of IR?

     

    Your story sounds very familiar.  I have many derived queries that were developed using 9.3.3 or earlier.  They work fine in 11.1.2.x until I try to edit them.  11.1.2.x appears to get really confused by this concept.

  • 2. Re: Hyperion IR Derived Queries
    chi_bull Newbie
    Currently Being Moderated

    The version is 11.1.2.x

     

    I have worked around the problem now. My main query originally only requested a computed item from the derived query. In my derived query the computed item being requested was a hard coded number, no actual column involved. In my main query I added an additional request from the derived query, an actual column, and it worked. IR recognized the derived query and included it in the sql generation.

     

    It's a bit odd that it wouldn't let me just request the computed item but maybe someone else has a better explanation of it. My SQL would run fine in sql developer so I think its just an interactive reporting program nuance.


    Thanks for the reply Doug!

  • 3. Re: Hyperion IR Derived Queries
    DougP Explorer
    Currently Being Moderated

    Correct, it's a IR problem.  IR is trying to send something like Q-MyQuery.Field_Name rather than the physical field info to the SQL server.  Since there isn't a table named Q-MyQuery in the database, the query fails.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points