Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 16 Oracle Analytics Lounge
- 216 Oracle Analytics News
- 43 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 79 Oracle Analytics Trainings
- 15 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
BI Publisher: how to use first and last aggregation
I have data:
and I want to show the data like this:
i try to use group level link in order to use aggregation function but it's not working as i expect.
SQL Script that i want show data:
SELECT A.ITM_ID
,B.TRNS_TYP
--,B.TRNS_DTE
,MIN(B.QTY) KEEP (DENSE_RANK FIRST ORDER BY A.ITM_ID,B.TRNS_TYP,B.TRNS_DTE) QTY
,max(b.amount) keep (dense_rank last order by A.ITM_ID,B.TRNS_TYP,B.TRNS_DTE) amt
FROM V_PRODUCT A
JOIN TMP_INV B
ON A.ITM_ID=B.ITM_ID
GROUP BY
A.ITM_ID
,B.TRNS_TYP
--,B.TRNS_DTE
order by 1,2;
Please suggest me.
Thanks in advance.
Answers
-
Something along the lines of:
QTY would be: FIRST(QTY BY PSTL_CDE,ITM_ID,TRNS_TYP)
AMOUNT would be: LAST(AMOUNT BY PSTL_CDE,ITM_ID,TRNS_TYP)
0 -
+1 to @Joel Acha -
here is effectively what you want in SQL ...
SELECT *
FROM
(
--BEG rows
SELECT B.PSTL_CDE,
A.ITM_ID,
B.TRNS_TYP,
MAX(B.QTY) KEEP (DENSE_RANK LAST ORDER BY B.TRNS_DTE DESC) OVER (PARTITION BY B.PSTL_CDE, A.ITM_ID, B.TRNS_TYP) FIRST_QTY,
MAX(B.AMOUNT) KEEP (DENSE_RANK LAST ORDER BY B.TRNS_DTE ASC) OVER (PARTITION BY B.PSTL_CDE, A.ITM_ID, B.TRNS_TYP) LAST_AMOUNT
FROM V_PRODUCT A
JOIN TMP_INV B
ON A.ITM_ID =B.ITM_ID
WHERE B.TRNS_TYP = 'BEG'
UNION
-- END rows
SELECT B.PSTL_CDE,
A.ITM_ID,
B.TRNS_TYP,
MAX(B.QTY) KEEP (DENSE_RANK LAST ORDER BY B.TRNS_DTE DESC) OVER (PARTITION BY B.PSTL_CDE, A.ITM_ID, B.TRNS_TYP) FIRST_QTY,
MAX(B.AMOUNT) KEEP (DENSE_RANK LAST ORDER BY B.TRNS_DTE ASC) OVER (PARTITION BY B.PSTL_CDE, A.ITM_ID, B.TRNS_TYP) LAST_AMOUNT
FROM V_PRODUCT A
JOIN TMP_INV B
ON A.ITM_ID =B.ITM_ID
WHERE B.TRNS_TYP = 'END'
)
ORDER BY 1,2,3
I know it seems weird to use MAX() in the FIRST rows, but notice the sort order is flipped in FIRST versus LAST (ASC in LAST is optional - just wanted to show you the difference) --- I have also added the PARTITION BY ... then I wrapped the select to put them all together in the right order ...
0
