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 )
RST_RCV_Query are all derived queries.
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')
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!