5 Replies Latest reply: Aug 27, 2014 3:50 PM by 2739608 RSS

    Need Hlp(26aug)

    2739608

      I need help with a SQL query. My data is like this.

       

         

      year_1to10_limitbob_codeprod_idcount
      below 1 yearptbarm40
      below 2 yearibcfix30
      below 3 yearcortpo40
      below 4 yearibctpo10
      below 5 yearptbarm20
      below 1 yearcorfix40
      below 2 yearcoroth50
      below 4 yearcoroth50
      below 3 yearibcarm70
      below 2 yearcorfix40
      above 5 yearcorarm50
      above 7 yearptbarm30
      above 9 yearibcfix40
      below 5 yearptbfix80
      below 3 yearcoroth100
      below 2 yearptbfix300
      above 10 yearptbarm10
      above 10 yearibcfix20

       

      I want to show like below.

         

      bob_ptbbob_ibcbob_corprod_armprod_fixprod_tpo
      below 1 year
      below 2 year90
      below 3 year
      below 4 year
      below 5 year
      above 5 year
      above 7 year
      above 9 year
      above 10 year

       

      Group by with 'year_1to10_limit' and the types of bob_code's such as ptb,ib, cor has to be summed and given another names, such as bob_ptb,bob_ibc,bob_cor

      the same with prod_id.Also, if you take the 'below 2 year' and 'cor', the bob_cor has to be equal to 50+40, which is 90.

       

      I'm new to this site. Little confusing. Already posted onetime, don't know where it is gone. Sorry if you find me twice with the same request. Thank You ALL.

        • 1. Re: Need Hlp(26aug)
          Karthick_Arp

          Like this?

           

          SQL> select year_1to10_limit
            2       , sum(decode(bob_code, 'cor', count)) bob_cor
            3       , sum(decode(bob_code, 'ibc', count)) bob_ibc
            4       , sum(decode(bob_code, 'ptb', count)) bob_ptb
            5       , sum(decode(prod_id, 'arm', count)) prod_arm
            6       , sum(decode(prod_id, 'fix', count)) prod_fix
            7       , sum(decode(prod_id, 'oth', count)) prod_oth
            8       , sum(decode(prod_id, 'tpo', count)) prod_tpo
            9    from t
           10   group
           11      by year_1to10_limit
           12   order
           13      by to_number(replace(replace(replace(year_1to10_limit, 'above'), 'below'), 'year'));
          
          YEAR_1TO10_LI    BOB_COR    BOB_IBC    BOB_PTB   PROD_ARM   PROD_FIX   PROD_OTH   PROD_TPO
          ------------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
          below 1 year          40                    40         40         40
          below 2 year          90         30        300                   370         50
          below 3 year         140         70                    70                   100         40
          below 4 year          50         10                                          50         10
          above 5 year          50                               50
          below 5 year                               100         20         80
          above 7 year                                30         30
          above 9 year                     40                               40
          above 10 year                    20         10         10         20
          
          9 rows selected.
          
          • 2. Re: Need Hlp(26aug)
            2739608


            Karthik,

             

            Thanks for taking your precious time on me. However, my actual distinct 'year_1to10_limit' values are, I'm using Oracle Sql developer.

             

            <2 YEARS LEFT

            <4 YEARS LEFT

            <1 YEAR LEFT

            Above 10 Years

            <5 YEARS LEFT

            <3 YEARS LEFT

            <10 YEARS LEFT.

             

            So I made little modification, but it is giving error. Please find the query and error, below.

            select break_term_1to10yrs_period 

                 , sum(decode(grp_cd_ret_bob_new, 'COR', ln_nr)) bob_cor 

                 , sum(decode(grp_cd_ret_bob_new, 'IBC', ln_nr)) bob_ibc 

                 , sum(decode(grp_cd_ret_bob_new, 'PTB', ln_nr)) bob_ptb 

                 , sum(decode(product_type_ind, 'ARM', ln_nr)) prod_arm 

                 , sum(decode(product_type_ind, 'FIX', ln_nr)) prod_fix 

                 , sum(decode(product_type_ind, 'OTH', ln_nr)) prod_oth 

                 , sum(decode(product_type_ind, 'BAL', ln_nr)) prod_bal 

                from rkandu.au26

                group  by break_term_1to10yrs_period 

                order  by to_number(replace(replace(replace(replace(break_term_1to10yrs_period, 'YEARS'), 'YEAR'), 'Above'),'LEFT'),'Years');

             

            ORA-01481: invalid number format model

            01481. 00000 -  "invalid number format model"

            *Cause:    The user is attempting to either convert a number to a string

                       via TO_CHAR or a string to a number via TO_NUMBER and has

                       supplied an invalid number format model parameter.

            *Action:   Consult your manual.

            • 3. Re: Need Hlp(26aug)
              2739608

              Karthik,

               

              I just took out the order by. It works now. Thank You so much. You save my day.

              • 4. Re: Need Hlp(26aug)
                2739608

                Karthick,

                I need your help again, as I'm getting double count than I suppose to get. Just tell me where I went wrong with the code.


                (select break_term_1to10yrs_period,grp_cd_ret_bob_new,product_type_ind,count(1) AS TOT_LN   
                from hm_prod.p8202014_sas where DML_ctrl_hldout_cd in ('Y','N')
                group by break_term_1to10yrs_period,grp_cd_ret_bob_new,product_type_ind)

                UNION
                (select deliquent_flg,grp_cd_ret_bob_new,product_type_ind,count(1) AS TOT_LN
                from hm_prod.p8202014_sas where DML_ctrl_hldout_cd in ('Y','N')
                group by deliquent_flg,grp_cd_ret_bob_new,product_type_ind)

                UNION
                (select mod_flg,grp_cd_ret_bob_new,product_type_ind,count(1) AS TOT_LN
                from hm_prod.p8202014_sas where DML_ctrl_hldout_cd in ('Y','N')
                group by mod_flg,grp_cd_ret_bob_new,product_type_ind)

                UNION
                (select arm_reset,grp_cd_ret_bob_new,product_type_ind,count(1) AS TOT_LN
                from hm_prod.p8202014_sas where DML_ctrl_hldout_cd in ('Y','N')
                group by arm_reset,grp_cd_ret_bob_new,product_type_ind);

                 

                result (part of result)

                Total No. of Loans are 29,951,104
                Break term/Deliquent/Modified/Arm Bob_Code ARM_Reset Total Loans
                <10 YEARS LEFT COR ARM 350
                <10 YEARS LEFT COR FIX 4691
                <10 YEARS LEFT COR OTH 68
                <10 YEARS LEFT IBC ARM 5
                <10 YEARS LEFT IBC FIX 1782
                <10 YEARS LEFT PTB ARM 106
                <10 YEARS LEFT PTB FIX 3794
                <10 YEARS LEFT PTB OTH 4
                ARM Reset <12Mos COR ARM 59571
                ARM Reset <12Mos COR FIX 1

                 

                To get our result, I modified your code. But I'm getting almost double the count, which I suppose to get.

                 

                select term_reset_del_mod 

                     ,SUM(decode(grp_cd_ret_bob_new, 'COR', tot_ln)) bob_cor 

                     ,SUM(decode(grp_cd_ret_bob_new, 'IBC', tot_ln)) bob_ibc 

                     ,SUM(decode(grp_cd_ret_bob_new, 'PTB', tot_ln)) bob_ptb 

                     ,SUM(decode(product_type_ind, 'ARM', tot_ln)) prod_arm 

                     ,SUM(decode(product_type_ind, 'FIX', tot_ln)) prod_fix 

                     ,SUM(decode(product_type_ind, 'OTH', tot_ln)) prod_oth 

                     ,SUM(decode(product_type_ind, 'BAL', tot_ln)) prod_bal 

                    from rkandu.au27_v2_1

                    group  by term_reset_del_mod;

                 

                Total No. of Loans are 59,902,200
                term_reset_del_mod BOB_COR BOB_IBC BOB_PTB PROD_ARM PROD_FIX PROD_OTH PROD_BAL
                <10 YEARS LEFT 5109 1787 3904 461 10267 72
                Above 10 Years 4394759 774809 2307406 434586 7003745 38644 1
                MOD_N 4307129 772292 2287879 433432 6898928 34941 1
                MOD_Y 92739 4304 23431 1615 115084 3775
                ARM Reset <6 Mos 4276629 748493 2129316 101721 7014002 38716 1
                ARM Reset <12Mos 59572 6862 51927 118360 1
                ARM Reset >12Mos 48588 19044 113466 181089 9
                ARM Reset <18Mos 15079 2197 16601 33877
                DEL_N 4399868 776596 2311310 435047 7014012 38716 1

                 

                Pls help. Thank You.

                • 5. Re: Need Hlp(26aug)
                  2739608

                  Forgot to add one step, to rename a field name 'break_term_1to10yrs_period' as 'term_reset_del_mod'

                   

                  select * from au27_v2;

                  create table au27_v2_1 as

                  select break_term_1to10yrs_period as term_reset_del_mod,grp_cd_ret_bob_new,product_type_ind,tot_ln from au27_v2;