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 Go to next message
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 1-12 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

Re: Tree order, Sum of valid values [message #682368 is a reply to message #682367] Tue, 20 October 2020 07:44 Go to previous messageGo to next message
BlackSwan
Messages: 26756
Registered: January 2009
Location: SoCal
Senior Member
Please read the OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Re: Tree order, Sum of valid values [message #682371 is a reply to message #682368] Tue, 20 October 2020 12:39 Go to previous message
vasudevaviswa
Messages: 53
Registered: August 2015
Location: US
Member
Hi Experts

Can anyone please sugegst the process/add the logic within the given Procedure to get the expected result.

Thanks
Previous Topic: How to generate a spool file without an extension name (.lst,.txt etc...)
Next Topic: To_char date with and without format
Goto Forum:
  


Current Time: Sat Nov 28 22:49:59 CST 2020