Home » SQL & PL/SQL » SQL & PL/SQL » Update query Help
Update query Help [message #196466] Thu, 05 October 2006 08:10 Go to next message
bonker
Messages: 402
Registered: July 2005
Senior Member
Hi,

I have following two tables


SQL>   CREATE TABLE "FCT_NETTABLE_LIABILITY" 
   (	"N_CUST_SKEY" NUMBER(10,0) NOT NULL ENABLE, 
	"N_LIAB_AMOUNT" NUMBER(22,3) NOT NULL ENABLE, 
	"V_CCY_CODE" VARCHAR2(3) NOT NULL ENABLE, 
	"FIC_MIS_DATE" DATE NOT NULL ENABLE, 
	"N_RUN_SKEY" VARCHAR2(20) NOT NULL ENABLE, 
	"N_NET_LIAB_AMT" NUMBER(22,3), 
	 CONSTRAINT "PK_FCT_NETTABLE_LIABILITY" PRIMARY KEY ("N_CUST_SKEY", "V_CCY_CODE", "FIC_MIS_DATE", "N_RUN_SKEY")
/

SQL> CREATE TABLE "FCT_EXPOSURE" 
   (	"N_EXPOSURE_ID" Number(20) NOT NULL ENABLE
        "N_CUST_SKEY" NUMBER(10,0) NOT NULL ENABLE, 
	"N_LIAB_AMOUNT" NUMBER(22,3) NOT NULL ENABLE, 
	"V_CCY_CODE" VARCHAR2(3) NOT NULL ENABLE, 
	"FIC_MIS_DATE" DATE NOT NULL ENABLE, 
	"N_RUN_SKEY" VARCHAR2(20) NOT NULL ENABLE, 
	"N_NET_LIAB_AMT" NUMBER(22,3), 
	 CONSTRAINT "PK_FCT_NETTABLE_LIABILITY" PRIMARY KEY ("N_EXPOSURE_ID","N_CUST_SKEY", "V_CCY_CODE", "FIC_MIS_DATE", "N_RUN_SKEY")
/

SQL>insert into FCT_NETTABLE_LIABILITY (N_CUST_SKEY, N_LIAB_AMOUNT, V_CCY_CODE, FIC_MIS_DATE, N_RUN_SKEY, N_NET_LIAB_AMT)
values (79, 142508.21, 'HKD', to_date('30-06-2006', 'dd-mm-yyyy'), '333', null);
SQL>insert into FCT_NETTABLE_LIABILITY (N_CUST_SKEY, N_LIAB_AMOUNT, V_CCY_CODE, FIC_MIS_DATE, N_RUN_SKEY, N_NET_LIAB_AMT)
values (79, 444.517, 'USD', to_date('30-06-2006', 'dd-mm-yyyy'), '333', null);
commit;
SQL>insert into FCT_EXPOSURES (N_CUST_SKEY, N_EXPOSURE_AMT, V_CCY_CODE, FIC_MIS_DATE, N_RUN_SKEY,n_exposure_id)
values (79, 42508.21, 'HKD', to_date('30-06-2006', 'dd-mm-yyyy'), '333',1);
SQL>insert into FCT_EXPOSURES (N_CUST_SKEY, N_EXPOSURE_AMT, V_CCY_CODE, FIC_MIS_DATE, N_RUN_SKEY,n_exposure_id)
values (79, 2508.21, 'HKD', to_date('30-06-2006', 'dd-mm-yyyy'), '333',2);

SQL>insert into FCT_EXPOSURES (N_CUST_SKEY, N_EXPOSURE_AMT, V_CCY_CODE, FIC_MIS_DATE, N_RUN_SKEY,n_exposure_id)
values (79, 1508.21, 'GBP', to_date('30-06-2006', 'dd-mm-yyyy'), '333',3);







Now FCT_EXPOSUREs for customer wise,exposure wise, currency wise data and FCT_NETTABLE_LIABILITY contains customer wise and currency wise liability amount

Now I have to net exposure amount from nettable liabiliy amount till nettable liab amount for that currency becomes zero. The nettig can be done across currency but in that case only 90% of nettable amount can be set off

Can this be done as single update? Currently we have written PSLQL function and it is taking around 5 hours to complete for hundred thousand rows .

Re: Update query Help [message #196469 is a reply to message #196466] Thu, 05 October 2006 08:17 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
I'm having trouble understanding your requirement.
Can you explain the process in more detail. and ideally show us the Pl/Sql function you've created as well.

Thanks for providing Create table and Insert statements.
Re: Update query Help [message #196470 is a reply to message #196469] Thu, 05 October 2006 08:23 Go to previous message
bonker
Messages: 402
Registered: July 2005
Senior Member
Here is the code

CREATE OR REPLACE FUNCTION Fn_Nettable_Liab_HKMA_old
  (
  	p_v_batch_id VARCHAR2,
  	p_v_mis_date VARCHAR2,
  	p_v_run_id VARCHAR2,
  	p_v_process_id VARCHAR2,
  	p_v_run_execution_id VARCHAR2,
  	p_n_run_skey VARCHAR2
  )
  RETURN VARCHAR2 AS
  
  /*
  ln_netable_cust_skey fct_nettable_liability.n_cust_skey%type;
  ln_liab_amount fct_nettable_liability.n_liab_amount%type;
  lv_netable_ccy_code fct_nettable_liability.v_ccy_code%type;
  
  ln_nonsec_acct_skey fct_non_sec_exposures.n_acct_skey%type;
  lv_nonsec_ccy_code fct_non_sec_exposures.v_ccy_code%type;
  ln_nonsec_exposure_amt fct_non_sec_exposures.n_exposure_amount%type;
  
  ln_run_skey NUMBER(5) := TO_NUMBER(SUBSTR(p_n_run_skey, 8, LENGTH(p_n_run_skey)));
  ld_misdate DATE := TO_DATE(p_v_mis_date, 'YYYYMMDD');
 */
    
  str_sql VARCHAR(2000);
  str_dsn VARCHAR(2000):= substr(p_v_batch_id, 1, INSTR(p_v_batch_id,'_') - 1);
  str_run NUMBER(5) := to_number(SUBSTR(p_n_run_skey, 8, LENGTH(p_n_run_skey)));
  sqlerrm VARCHAR(2000);
  ld_misdate date := to_date(p_v_mis_date,'YYYYMMDD');
  inctr int;
  inctr1 int;
  
  ln_netable_cust_skey fct_nettable_liability.n_cust_skey%type;
  ln_liab_amount fct_nettable_liability.n_liab_amount%type;
  lv_netable_ccy_code fct_nettable_liability.v_ccy_code%type;
 
  ln_nonsec_acct_skey fct_non_sec_exposures.n_acct_skey%type;
  lv_nonsec_ccy_code fct_non_sec_exposures.v_ccy_code%type;
  ln_nonsec_exposure_amt fct_non_sec_exposures.n_exposure_amount%type;
   
 
 CURSOR cur_netable_main IS
   SELECT distinct a.n_cust_skey,nvl(n_liab_amount,0) n_liab_amount,a.v_ccy_code   
   FROM fct_nettable_liability a, fcT_non_sec_exposures b
   WHERE a.fic_mis_date = ld_misdate AND
         a.n_run_skey = str_run
         and b.n_run_skey = a.n_run_skey
         and a.fic_mis_Date = b.fic_mis_date
         and a.n_cust_skey = b.n_cust_skey
         and b.v_ccy_code is not null
         order by a.n_cust_skey,a.v_ccy_code;
         
 CURSOR cur_nonsec_main IS
   SELECT n_acct_skey,v_ccy_code,nvl(n_exposure_amount,0) n_exposure_amount  
   FROM fct_non_sec_exposures
   WHERE fic_mis_date = ld_misdate AND
         n_run_skey = str_run AND
         n_cust_skey = ln_netable_cust_skey AND
         v_ccy_code = lv_netable_ccy_code;
         
 CURSOR cur_nonsec_oth_ccy IS
   SELECT n_acct_skey,v_ccy_code,nvl(n_exposure_amount,0) n_exposure_amount  
   FROM fct_non_sec_exposures
   WHERE fic_mis_date = ld_misdate AND
         n_run_skey = str_run AND
         n_cust_skey = ln_netable_cust_skey AND
         v_ccy_code <> lv_netable_ccy_code;
         
         
  /* New cursors for handling Join case */
         
         
	  CURSOR cur_netable_main_join IS
	    SELECT n_cust_skey,nvl(n_liab_amount,0) n_liab_amount,v_ccy_code   
	    FROM fct_nettable_liability a
	    WHERE fic_mis_date = ld_misdate AND
	          n_run_skey = str_run
	          and ln_netable_cust_skey in(select distinct n_single_cust_skey  from fct_join_customers
                                        where fic_mis_date = ld_misdate);                                        
           
	          
	  CURSOR cur_nonsec_main_join IS
	    SELECT n_acct_skey,v_ccy_code,nvl(n_exposure_amount,0) n_exposure_amount  
	    FROM fct_non_sec_exposures
	    WHERE fic_mis_date = ld_misdate AND
	          n_run_skey = str_run AND
	          n_cust_skey in (  select  distinct n_cust_skey  from fct_join_customers where n_single_cust_skey =ln_netable_cust_skey 
                              union
                              select distinct n_single_cust_skey from fct_join_customers where n_single_cust_skey =ln_netable_cust_skey)AND
	          v_ccy_code = lv_netable_ccy_code;
	          
	  CURSOR cur_nonsec_oth_ccy_join IS
	    SELECT n_acct_skey,v_ccy_code,nvl(n_exposure_amount,0) n_exposure_amount  
	    FROM fct_non_sec_exposures
	    WHERE fic_mis_date = ld_misdate AND
	          n_run_skey = str_run AND
	          n_cust_skey in ( select  distinct n_cust_skey  from fct_join_customers where n_single_cust_skey =ln_netable_cust_skey 
                              union
                             select distinct n_single_cust_skey from fct_join_customers where n_single_cust_skey =ln_netable_cust_skey ) AND
         v_ccy_code <> lv_netable_ccy_code;
 
 ------**********************************************************************************----------------------
  
 
  BEGIN
  
    
    inctr := 0;
    FOR i in cur_netable_main  --loop 1
    LOOP 
      inctr := inctr + 1;
      dbms_application_info.set_module('Main Loop '||inctr,' ');
      
      ln_netable_cust_skey := i.n_cust_skey;
      lv_netable_ccy_code := i.v_ccy_code;
      ln_liab_amount := i.n_liab_amount;
      inctr1 := 0;
        FOR j in cur_nonsec_main --loop 2
        LOOP --loop 2
                   inctr1 := inctr1 + 1;
              dbms_application_info.set_action('Inner Loop '||inctr1);
          ln_nonsec_acct_skey := j.n_acct_skey;
          lv_nonsec_ccy_code := j.v_ccy_code;
          ln_nonsec_exposure_amt := j.n_exposure_amount;
          
          IF ln_nonsec_exposure_amt >= ln_liab_amount THEN
          
            UPDATE fct_non_sec_exposures T1
            SET T1.N_EXPOSURE_AMOUNT =(ln_nonsec_exposure_amt-ln_liab_amount)
            WHERE T1.n_Acct_Skey = ln_nonsec_acct_skey AND
                  T1.fic_mis_date = ld_misdate AND
                  T1.n_run_skey = str_run;
           ln_liab_amount :=0;
                exit;          
          ELSE
            
            UPDATE fct_non_sec_exposures T1
            SET T1.N_EXPOSURE_AMOUNT = 0
            WHERE T1.n_Acct_Skey = ln_nonsec_acct_skey AND
                  T1.fic_mis_date = ld_misdate AND
                  T1.n_run_skey = str_run;
            ln_liab_amount := ln_liab_amount - ln_nonsec_exposure_amt;
            
          
          END IF;
        END LOOP; --end loop 2
        
        
        
        /*Goes to this CUR only if Liability not netted with own currency exposure*/
        IF ln_liab_amount <> 0 THEN
          ln_liab_amount := ln_liab_amount *0.92;  -- Reduce 8%
          
          FOR k in cur_nonsec_oth_ccy  --loop 3
          LOOP
            
            ln_nonsec_acct_skey := k.n_acct_skey;
            lv_nonsec_ccy_code := k.v_ccy_code;
            ln_nonsec_exposure_amt := k.n_exposure_amount;
          
            IF ln_nonsec_exposure_amt >= ln_liab_amount THEN
          
              UPDATE fct_non_sec_exposures T1
              SET T1.N_EXPOSURE_AMOUNT =(ln_nonsec_exposure_amt-ln_liab_amount)
              WHERE T1.n_Acct_Skey = ln_nonsec_acct_skey AND
                    T1.fic_mis_date = ld_misdate AND
                    T1.n_run_skey = str_run;
              ln_liab_amount :=0;
              exit;
                
            ELSE
            
              UPDATE fct_non_sec_exposures T1
              SET T1.N_EXPOSURE_AMOUNT = 0
              WHERE T1.n_Acct_Skey = ln_nonsec_acct_skey AND
                  T1.fic_mis_date = ld_misdate AND
                  T1.n_run_skey = str_run;
              ln_liab_amount := ln_liab_amount - ln_nonsec_exposure_amt;
              ln_liab_amount := ln_liab_amount *0.92;
            
          
           END IF;
            
            
         END LOOP; --LOOP 3
          
          
        
        END IF;
        
         
      
      
        
    END LOOP;  --loop 1
    
    return '1';

end;
/

[Updated on: Thu, 05 October 2006 08:24]

Report message to a moderator

Previous Topic: PickList query help needed Please
Next Topic: GROUP BY AND BETWEEN
Goto Forum:
  


Current Time: Sat Dec 03 03:40:00 CST 2016

Total time taken to generate the page: 0.12589 seconds