Home » SQL & PL/SQL » SQL & PL/SQL » need help in writing proc or getting the logic.
need help in writing proc or getting the logic. [message #327806] Tue, 17 June 2008 13:46 Go to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
SELECT   TRACK_NUM, MIN(sub_account) AS START_KEY, MAX(sub_account) AS END_KEY  FROM (              SELECT to_number(sub_account) as sub_account,              RANK() OVER (PARTITION BY sub_account              ORDER BY ROWNUM) AS ROW_INSTANCE,              NTILE(  20  ) OVER (ORDER BY sub_account) AS TRACK_NUM              FROM  STG_SUB_MASTER_MONTH_HISTORY)              WHERE ROW_INSTANCE = 1              GROUP BY TRACK_NUM ;


The above sql gives result set like

TRACK_NUM	START_KEY	END_KEY1	200000028	20959319542	2095932501	50670042143	5067004346	1.1092E+104	1.1092E+10	2.1028E+105	2.1028E+10	2.3078E+106	2.3078E+10	2.9010E+107	2.9010E+10	3.1089E+108	3.1089E+10	3.3051E+109	3.3051E+10	3.4081E+1010	3.4081E+10	3.7014E+1011	3.7014E+10	3.7067E+1012	3.7067E+10	4.1006E+1013	4.1006E+10	4.5040E+1014	4.5040E+10	4.9047E+1015	4.9047E+10	5.2030E+1016	5.2030E+10	6.4578E+1017	6.4578E+10	7.1001E+1018	7.1001E+10	7.1035E+1019	7.1035E+10	7.2048E+1020	7.2048E+10	7.9553E+10


Now i have to pass these start key and end key to my below code


Insert into /*+ Parallel (A,8) append */ Sub_phone_Rateplan_fss ASELECT /* Index(Sub, IDX_Sub_hist)*/                                sub.MARKET_CODE        ,                        sub.SUB_CTYPE ,                        phn.PH_BASIC_SRV        ,                        sub.MONTH_ID  ,                         sub.SUB_ACCOUNT        ,                        sub.SUB_LAST_NAME     ,                       sub.SUB_FIRST_NAME   ,                        SUB.WS_ENTRY_DATE AS SUB_WS_ENTRY_DATE   ,                        sub.WS_DISC_DATE       ,                        sub.SUB_DISC_REASON ,                        sub.SUB_BILL_CYCLE    ,                        sub.SUB_MUN_CODE     ,                        sub.SUB_30DAY_BAL     ,                        sub.SUB_60DAY_BAL     ,                        sub.SUB_90DAY_BAL     ,                        sub.SUB_OVR90_BAL     ,                        sub.SUB_WRTOFF_AMT ,                        sub.SUB_BALANCE_DUE ,                        sub.SUB_CURRENT_BAL ,                        sub.SUB_STATUS,                        phn.PH_MOBIL_ID         ,                        phn.WS_ESN_UN           ,                        phn.WS_START_DATE    ,                        phn.WS_STOP_DATE      ,                        PHN.WS_ENTRY_DATE AS PHN_WS_ENTRY_DATE   ,                        phn.PH_STATUS            ,                        phn.PH_FEAT_CODES     ,                        phn.PH_MODEL  ,                        phn.CRICKET_LOCATION_ID_KEY           ,                        phn.FEATURE_SET_ID                       -- NTILE(10) OVER(PARTITION BY SUB.SUB_ACCOUNT ORDER BY SUB.SUB_ACCOUNT) AS NT                    FROM STG_SUB_MASTER_MONTH_HISTORY SUB, STG_PHN_MASTER_MONTH_HISTORY PHN                     WHERE                     --SUB.MONTH_ID = PHN.MONTH_ID                     SUB.SUB_ACCOUNT = PHN.PH_ACCOUNT                       And to_number(SUB.Sub_Account)  BETWEEN   @startkey and  @endkey;                                      Commit;


can anyone help me getting the logic or the procedure since i have to use the result set provided above by the first query.
Re: need help in writing proc or getting the logic. [message #327807 is a reply to message #327806] Tue, 17 June 2008 13:49 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Please start by actually FORMATTING your post so it is readable on a single screen.

Post DDL & DML to create table & test data.
Re: need help in writing proc or getting the logic. [message #327810 is a reply to message #327806] Tue, 17 June 2008 14:17 Go to previous messageGo to next message
victory_nag
Messages: 36
Registered: June 2008
Location: CA
Member
SELECT  TRACK_NUM, MIN(sub_account) AS START_KEY, 
                     MAX(sub_account) AS END_KEY
             FROM (
                  SELECT to_number(sub_account) as sub_account,
                  RANK() OVER (PARTITION BY sub_account
                  ORDER BY ROWNUM) AS ROW_INSTANCE,
                  NTILE(  20  ) OVER (ORDER BY sub_account) AS TRACK_NUM
                  FROM  STG_SUB_MASTER_MONTH_HISTORY)
              WHERE ROW_INSTANCE = 1
              GROUP BY TRACK_NUM ;

The result set for above code is
 TRACK_NUM  START_KEY    END_KEY
---------- ---------- ----------
         1  200000028 2095931954
         2 2095932501 5067004214
         3 5067004346 1.1092E+10
         4 1.1092E+10 2.1028E+10
         5 2.1028E+10 2.3078E+10
         6 2.3078E+10 2.9010E+10
         7 2.9010E+10 3.1089E+10
         8 3.1089E+10 3.3051E+10
         9 3.3051E+10 3.4081E+10
        10 3.4081E+10 3.7014E+10
        11 3.7014E+10 3.7067E+10
        12 3.7067E+10 4.1006E+10
        13 4.1006E+10 4.5040E+10
        14 4.5040E+10 4.9047E+10
        15 4.9047E+10 5.2030E+10
        16 5.2030E+10 6.4578E+10
        17 6.4578E+10 7.1001E+10
        18 7.1001E+10 7.1035E+10
        19 7.1035E+10 7.2048E+10
        20 7.2048E+10 7.9553E+10


20 rows selected.


now i have to use these start and end keys generated in the result set for the following query.

Insert into /*+ Parallel (A,8) append */ Sub_phone_Rateplan_fss A
SELECT /* Index(Sub, IDX_Sub_hist)*/ 
               
                sub.MARKET_CODE        ,

                        sub.SUB_CTYPE ,
                        phn.PH_BASIC_SRV        ,
                        sub.MONTH_ID  ,
                         sub.SUB_ACCOUNT        ,
                        sub.SUB_LAST_NAME     ,
                       sub.SUB_FIRST_NAME   ,
                        SUB.WS_ENTRY_DATE AS SUB_WS_ENTRY_DATE   ,
                        sub.WS_DISC_DATE       ,
                        sub.SUB_DISC_REASON ,
                        sub.SUB_BILL_CYCLE    ,
                        sub.SUB_MUN_CODE     ,
                        sub.SUB_30DAY_BAL     ,
                        sub.SUB_60DAY_BAL     ,
                        sub.SUB_90DAY_BAL     ,
                        sub.SUB_OVR90_BAL     ,
                        sub.SUB_WRTOFF_AMT ,
                        sub.SUB_BALANCE_DUE ,
                        sub.SUB_CURRENT_BAL ,
                        sub.SUB_STATUS,
                        phn.PH_MOBIL_ID         ,
                        phn.WS_ESN_UN           ,
                        phn.WS_START_DATE    ,
                        phn.WS_STOP_DATE      ,
                        PHN.WS_ENTRY_DATE AS PHN_WS_ENTRY_DATE   ,
                        phn.PH_STATUS            ,
                        phn.PH_FEAT_CODES     ,
                        phn.PH_MODEL  ,
                        phn.CRICKET_LOCATION_ID_KEY           ,
                        phn.FEATURE_SET_ID  
              FROM STG_SUB_MASTER_MONTH_HISTORY SUB, STG_PHN_MASTER_MONTH_HISTORY PHN
 
                    WHERE 
                    
                     SUB.SUB_ACCOUNT = PHN.PH_ACCOUNT  
                     And to_number(SUB.Sub_Account)  BETWEEN   startkey and  endkey;

Re: need help in writing proc or getting the logic. [message #327813 is a reply to message #327806] Tue, 17 June 2008 15:08 Go to previous message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
Just string them altogether using "OR"
Previous Topic: ORA-22905: cannot access rows from a non-nested table item
Next Topic: Insert Row Data as columns into Another Table
Goto Forum:
  


Current Time: Fri Dec 02 16:54:36 CST 2016

Total time taken to generate the page: 0.25972 seconds