Home » SQL & PL/SQL » SQL & PL/SQL » Tree order, Sum of valid values (Oracle 12.2.0.)
Tree order, Sum of valid values [message #682367] 
Tue, 20 October 2020 07:40 

vasudevaviswa
Messages: 53 Registered: August 2015 Location: US

Member 


Team,
We are working on Oracle 12.2.0.1
For a ACC_BLNC_QUALIFIER,ACC_APPLIED_AMOUNT,ACT_CD,ACC_NETWORK_INDICATOR and few respective columns have 1..12 columns like ACC_BLNC_QUALIFIER1,ACC_BLNC_QUALIFIER2,...ACC_BLNC_QUALIFIER12 and ACC_APPLIED_AMOUNT have values like ACC_APPLIED_AMOUNT1,ACC_APPLIED_AMOUNT2,..ACC_APPLIED_AMOUNT12 etc..
Requirement :
If we take any falmily, they have a common FAMILY_ID. Means 1 parent have mutiple childs. So till now we have for 1 parent has 12 childrens/Qualifiers/ACT_CD_. In this, any child has some value. but have multiple Childs( in beyween 1..12) .
In future Childs may increase for the same parent. Eg: ACC_BLNC_QUALIFIER 13,ACC_BLNC_QUALIFIER14, etc.. if it value comes for ACC_BLNC_QUALIFIER13
then that value should display in next line/row for that respective parent
Current :
Parent Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12
1234 10 11
2341 12 08
earlier For 1 and 7 has two values . So we added the logic as ( as we have values for 1 and 7 have added te logic as)
LOAD_FUN (EMP.EMI_CK,
XREF1.CSTM_FIELD1_VALUE,
P_YEAR),
SUM (NVL (INSTG.ACC_APPLIED_AMOUNT1, 0))
OVER (PARTITION BY INSTG.CARDHOLDER_ID, INSTG.MCO_NAME)
+ SUM (NVL (STG.ACC_APPLIED_AMOUNT7, 0))
OVER (PARTITION BY STG.CARDHOLDER_ID, STG.MCO_NAME)
So if any new uint added like Q13,Q14... need to add the logic for below requirement..
Expected Logic
then expecting out put as and Need to add the logic for all units
Parent Q1 Q2 Q3 Q4 Q5 Q6 Q7 Q8 Q9 Q10 Q11 Q12
1234 01 07
13(Q13) 14(Q14) 16(Q15) 17(Q17)
23456 02(Q2) 12(Q12)
45641 08(Q8)
45641 13(Q13) 14(Q14)
...........
...........
If the value belongs to between 112 then that respective values goes to respective Unit. (Eg: If the value 01 then insert into Q1
If 04 then insert into Q4 etc.. like that need to follow)
Requirement 2:
UNPIVOT the data and load after conversion
Requirement3:
for each parent need to check available amount values for Units ( Q1..Q12/Q13,Q14..) for teh below example
compare ACC_APPLIED_AMOUNT values for both INSTG and STG. As the values are not same we processed the ACC_APPLIED_AMOUNT for both Q1 and Q7. If same we can ignore.
LOAD_FUN (EMP.EMI_CK,
XREF1.CSTM_FIELD1_VALUE,
P_YEAR),
SUM (NVL (INSTG.ACC_APPLIED_AMOUNTN1, 0))
OVER (PARTITION BY INSTG.CARDHOLDER_ID, INSTG.MCO_NAME)
+ SUM (NVL (STG.ACC_APPLIED_AMOUNTN7, 0))
OVER (PARTITION BY STG.CARDHOLDER_ID, STG.MCO_NAME)
Find the attached Tables Structures and Insert statements and Procedure and Function and advise the logic to add in the Procedure.
Thanks
Vasu
[Updated on: Tue, 20 October 2020 07:46] Report message to a moderator





Goto Forum:
Current Time: Fri Jan 22 11:06:08 CST 2021
