13 Replies Latest reply: Aug 8, 2012 1:14 AM by Chloe_19 RSS

    Combining 2 CODES (thanks)

    Chloe_19
      I want to get combine the first code (below) with the second.
      So the second code gives me the Unit and its count of people per term.
      I want to use the first code to alaculate the slope, intercep etc... from code 2 below
      Would just like to know how to join the two

      1st CODE
      SELECT REGR_SLOPE(CNT,RN)           R_SLOPE,
             REGR_INTERCEPT(CNT,RN)       R_INTERCEPT,
             REGR_COUNT(CNT,RN)           R_COUNT,
             REGR_R2(CNT,RN)              R_R2,
             regr_avgx(cnt,rn)            r_avgx,
             REGR_AVGY(CNT,RN)            R_AVGY,
             REGR_SXX(CNT,RN)             R_SXX,
             REGR_SYY(CNT,RN)             R_SYY,
             REGR_SXY(CNT,RN)             R_SXY
             
        from (select count cnt,row_number() over (order by terms) rn 
                from the_data
             )
       where cnt != 0
      R_SLOPE     R_INTERCEPT     R_COUNT     R_R2     R_AVGX     R_AVGY     R_SXX     R_SYY     R_SXY
      0.241071429    66.58035714     8      0.007238845     9     68.75     336     2697.5     81
      I want to compbine it with this code

      2nd CODE
      SELECT unit, UNIT_TITLE, acad_org,
                             term_1201,term_1202,term_1203,term_1204,term_1205,term_1206,
                             
          FROM   (SELECT unit,UNIT_TITLE,acad_org,
                             term_1201,term_1202,term_1203,term_1204,term_1205,term_1206,
                               
                        ROW_NUMBER () OVER
                          (PARTITION BY unit
                           ORDER BY 
                     term_1201 DESC,term_1202 DESC,term_1203 DESC,term_1204 DESC,term_1205 DESC,term_1206,
                                    
                     ) rn
                    FROM   (select UNIT, UNIT_TITLE, acad_org,
      
              nvl(max( decode( term, 1201, d_pct )),0) term_1201,
              nvl(max( decode( term, 1202, d_pct )),0) term_1202,
              nvl(max( decode( term, 1203, d_pct )),0) term_1203,
              nvl(max( decode( term, 1204, d_pct )),0) term_1204,
              nvl(max( decode( term, 1205, d_pct )),0) term_1205,
              nvl(max( decode( term, 1206, d_pct )),0) term_1206,
      
                    
                        from   (select E.sub|| E.cat = c.sub || c.cat unit, E.TERM,C.UNIT_TITLE, C.acad_org,
                             COUNT (E.OUA_ID) d_pct
                   from   TABLE1 E, TABLE2 C
                             WHERE  E.sub|| E.cat = c.sub || c.cat
                             and E.year = c.year
                   AND E.YEAR = '2012'
                            GROUP BY E.subject_cde || E.catalogue_no, E.TERM, C.UNIT_TITLE, C.acad_org)
                        GROUP BY UNIT, UNIT_TITLE, acad_org))
         WHERE  rn = 1
         order  by unit
      Gives me :
      UNIT     UNIT_TITLE     ACAD_ORG     TERM_0801     TERM_0802     TERM_0803     TERM_0804     TERM_0805     TERM_0806
      ABC11     Taxation     CURTN                  19                  14                  15                    0                0     12
      So when the two are combine my final answer would be something like:
      RESULT
      UNIT     UNIT_TITLE     ACAD_ORG     TERM_0801     TERM_0802     TERM_0803     TERM_0804     TERM_0805     TERM_0806
      ABC11     Taxation     CURTN                  19                  14                  15                    0                0     12
      WITH THIS.

      All in one line
      R_SLOPE     R_INTERCEPT     R_COUNT     R_R2     R_AVGX     R_AVGY     R_SXX     R_SYY     R_SXY
      0.241071429     66.58035714     8     0.007238845     9     68.75     336     2697.5     81
      Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi

      Thanks

      Edited by: Chloe_19 on 2/08/2012 00:07
        • 1. Re: Combining 2 CODES (thanks)
          riedelme
          Chloe_19 wrote:
          I want to get combine the first code (below) with the second.
          Can you add the first query to the second as another inline view, joining the results by common keys?
          • 2. Re: Combining 2 CODES (thanks)
            riedelme
            .

            Edited by: riedelme on Aug 3, 2012 5:38 AM
            • 3. Re: Combining 2 CODES (thanks)
              riedelme
              .

              Edited by: riedelme on Aug 2, 2012 7:50 AM
              • 4. Re: Combining 2 CODES (thanks)
                riedelme
                .
                • 5. Re: Combining 2 CODES (thanks)
                  Chloe_19
                  Hi riedelme
                  Sorry for the late reply i was not near a computer
                  Can you add the first query to the second as another inline view, joining the results by common keys?
                  I am realtivly new to sql i dont reall know what that means?
                  • 6. Re: Combining 2 CODES (thanks)
                    Chloe_19
                    What its ment to do is take the results from the second query
                    then the first query reads the numbers from the second querrry to determine the slopes, intercept, count etc.

                    Edited by: Chloe_19 on 2/08/2012 16:40
                    • 7. Re: Combining 2 CODES (thanks)
                      riedelme
                      Chloe_19 wrote:
                      Hi riedelme
                      Sorry for the late reply i was not near a computer
                      Can you add the first query to the second as another inline view, joining the results by common keys?
                      I am realtivly new to sql i dont reall know what that means?
                      You have 2 queries.

                      Can you do something l ike
                      query 1:
                      select *
                        from my table
                      
                      query 2:
                      select *
                        from my_other table
                      
                      combined query:
                      select *
                        from (select * from my_table) a, (select * from my_other_table)
                       where a.key = b.key
                      • 8. Re: Combining 2 CODES (thanks)
                        Etbin
                        is this related to Exclude *WITH AS* (alternatives) and your version does not support unpivot ?

                        Regards

                        Etbin

                        Edited by: Etbin on 3.8.2012 21:46
                        Maybe something like below (take it as a template NOT TESTED! no Database at hand hopefully until end of month - on my summer leave)
                        with
                        base_data as
                        (select e.subject_cde || e.catalogue_no unit,
                                c.unit_title,
                                c.acad_org,
                                e.term,
                                count(e.oua_id) d_pct
                           from table1 e,
                                table2 c
                          where e.sub || e.cat = c.sub || c.cat
                            and e.year = c.year
                            and e.year = '2012'
                          group by e.subject_cde || e.catalogue_no,e.term,c.unit_title,c.acad_org
                        ),
                        regr_calcs as
                        (select unit,unit_title,acad_org,
                                regr_slope(d_pct,rn)     r_slope,
                                regr_intercept(d_pct,rn) r_intercept,
                                regr_count(d_pct,rn)     r_count,
                                regr_r2(d_pct,rn)        r_r2,
                                regr_avgx(d_pct,rn)      r_avgx,
                                regr_avgy(d_pct,rn)      r_avgy,
                                regr_sxx(d_pct,rn)       r_sxx,
                                regr_syy(d_pct,rn)       r_syy,
                                regr_sxy(d_pct,rn)       r_sxy
                           from (select unit,
                                        unit_title,
                                        acad_org,
                                        d_pct,
                                        row_number() over (partition by unit,unit_title,acad_org order by term) rn
                                   from base_data
                                  where pct != 0
                                ) 
                          group by unit,unit_title,acad_org
                        ),
                        pivoted as
                        (select unit,unit_title,acad_org,
                                term_1201,term_1202,term_1203,term_1204,term_1205,term_1206
                           from (select unit,unit_title,acad_org,
                                        term_1201,term_1202,term_1203,term_1204,term_1205,term_1206,
                                        row_number() over (partition by unit
                                                               order by term_1201 desc,term_1202 desc,term_1203 desc,
                                                                        term_1204 desc,term_1205 desc,term_1206 desc
                                                          ) rn
                                  from (select unit,unit_title,acad_org,
                                               nvl(max(decode(term,1201,d_pct)),0) term_1201,
                                               nvl(max(decode(term,1202,d_pct)),0) term_1202,
                                               nvl(max(decode(term,1203,d_pct)),0) term_1203,
                                               nvl(max(decode(term,1204,d_pct)),0) term_1204,
                                               nvl(max(decode(term,1205,d_pct)),0) term_1205,
                                               nvl(max(decode(term,1206,d_pct)),0) term_1206,
                                          from (select unit,unit_title,acad_org,term,d_pct
                                                  from base_data
                                               )
                                         group by unit,unit_title,acad_org
                                       )
                                )
                          where rn = 1
                        )
                        select p.unit,p.unit_title,p.acad_org,
                               p.term_1201,p.term_1202,p.term_1203,p.term_1204,p.term_1205,p.term_1206,
                               r.r_slope,r.r_intercept,r.r_count,r.r_r2,r.r_avgx,r.r_avgy,r.r_sxx,r.r_syy,r.r_sxy
                          from pivoted p,
                               regr_calcs r
                         where p.unit = r.unit
                               p.unit_title = r.unit_title
                               p.acad_org = r.acad_org
                        • 9. Re: Combining 2 CODES (thanks)
                          Chloe_19
                          Hi etbin
                          the code works perfectly
                          thank you so much:

                          This is what it gives:
                          UNIT     UNIT_TITLE     ACAD_ORG     TERM_1201     TERM_1202     TERM_1203     TERM_1204     TERM_1205     TERM_1206      R_SLOPE           R_INTERCEPT
                          ABS17     SNOW                ACU                  19                   0                29                    0            0              0           10                      9
                          ABS28     RICE                ACU                  13                   0                14                    0            0              0            1                  12
                          ABT11     RAIN               UNISA                   0                  16                 0                    0            0              0          null                  null
                          I am trying to add a statement to the code which would use this formula y=mx+b
                          I want to take the INTERCEPT column MINUS it from the Y which are the term_1201, term_1202, term_1203 etc columns and divide it by the SLOP
                          So for 1201 it would be 9 - 19 which is -10 / 10 which is -1
                          So new column added would NEW_TERM_1201 is -1

                          I would like to repeat this process for all the TERMS.
                          Hope you undertand and can help

                          Thanks
                          • 10. Re: Combining 2 CODES (thanks)
                            Etbin
                            Hope you undertand and can help
                            Not very sure (looks too much like a typing exercise)
                            maybe NOT TESTED!
                            with
                            data(unit,unit_title,acad_org,
                                 term_1201,term_1202,term_1203,term_1204,term_1205,term_1206,r_slope,r_intercept) as
                            (select 'ABS17','SNOW','ACU     ',19,0,29,0,0,0,10,9 from dual union all
                             select 'ABS28','RICE     ','ACU',13,0,14,0,0,0,1,12 from dual union all
                             select 'ABT11','RAIN','UNISA',0,16,0,0,0,0,null,null from dual
                            )
                            select unit,unit_title,acad_org,
                                   term_1201,term_1202,term_1203,term_1204,term_1205,term_1206,r_slope,r_intercept,
                                   (r_intercept - term_1201) / r_slope new_term_1201,
                                   (r_intercept - term_1202) / r_slope new_term_1202,
                                   (r_intercept - term_1203) / r_slope new_term_1203,
                                   (r_intercept - term_1204) / r_slope new_term_1204,
                                   (r_intercept - term_1205) / r_slope new_term_1205,
                                   (r_intercept - term_1206) / r_slope new_term_1206
                              from data 
                            Regards

                            Etbin
                            • 11. Re: Combining 2 CODES (thanks)
                              Chloe_19
                              Thanks Etbin.
                              That seems right to me.

                              Just in which area should i pate the code. (ruffly) i know you cant test it.
                              Once more its combining codes this one
                              select unit,unit_title,acad_org,
                                     term_1201,term_1202,term_1203,term_1204,term_1205,term_1206,r_slope,r_intercept,
                                     (r_intercept - term_1201) / r_slope new_term_1201,
                                     (r_intercept - term_1202) / r_slope new_term_1202,
                                     (r_intercept - term_1203) / r_slope new_term_1203,
                                     (r_intercept - term_1204) / r_slope new_term_1204,
                                     (r_intercept - term_1205) / r_slope new_term_1205,
                                     (r_intercept - term_1206) / r_slope new_term_1206
                              with
                              with
                              base_data as
                              (select e.subject_cde || e.catalogue_no unit,
                                      c.unit_title,
                                      c.acad_org,
                                      e.term,
                                      count(e.oua_id) d_pct
                                 from table1 e,
                                      table2 c
                                where e.sub || e.cat = c.sub || c.cat
                                  and e.year = c.year
                                  and e.year = '2012'
                                group by e.subject_cde || e.catalogue_no,e.term,c.unit_title,c.acad_org
                              ),
                              regr_calcs as
                              (select unit,unit_title,acad_org,
                                      regr_slope(d_pct,rn)     r_slope,
                                      regr_intercept(d_pct,rn) r_intercept,
                                      regr_count(d_pct,rn)     r_count,
                                      regr_r2(d_pct,rn)        r_r2,
                                      regr_avgx(d_pct,rn)      r_avgx,
                                      regr_avgy(d_pct,rn)      r_avgy,
                                      regr_sxx(d_pct,rn)       r_sxx,
                                      regr_syy(d_pct,rn)       r_syy,
                                      regr_sxy(d_pct,rn)       r_sxy
                                 from (select unit,
                                              unit_title,
                                              acad_org,
                                              d_pct,
                                              row_number() over (partition by unit,unit_title,acad_org order by term) rn
                                         from base_data
                                        where pct != 0
                                      ) 
                                group by unit,unit_title,acad_org
                              ),
                              pivoted as
                              (select unit,unit_title,acad_org,
                                      term_1201,term_1202,term_1203,term_1204,term_1205,term_1206
                                 from (select unit,unit_title,acad_org,
                                              term_1201,term_1202,term_1203,term_1204,term_1205,term_1206,
                                              row_number() over (partition by unit
                                                                     order by term_1201 desc,term_1202 desc,term_1203 desc,
                                                                              term_1204 desc,term_1205 desc,term_1206 desc
                                                                ) rn
                                        from (select unit,unit_title,acad_org,
                                                     nvl(max(decode(term,1201,d_pct)),0) term_1201,
                                                     nvl(max(decode(term,1202,d_pct)),0) term_1202,
                                                     nvl(max(decode(term,1203,d_pct)),0) term_1203,
                                                     nvl(max(decode(term,1204,d_pct)),0) term_1204,
                                                     nvl(max(decode(term,1205,d_pct)),0) term_1205,
                                                     nvl(max(decode(term,1206,d_pct)),0) term_1206,
                                                from (select unit,unit_title,acad_org,term,d_pct
                                                        from base_data
                                                     )
                                               group by unit,unit_title,acad_org
                                             )
                                      )
                                where rn = 1
                              )
                              select p.unit,p.unit_title,p.acad_org,
                                     p.term_1201,p.term_1202,p.term_1203,p.term_1204,p.term_1205,p.term_1206,
                                     r.r_slope,r.r_intercept,r.r_count,r.r_r2,r.r_avgx,r.r_avgy,r.r_sxx,r.r_syy,r.r_sxy
                                from pivoted p,
                                     regr_calcs r
                               where p.unit = r.unit
                                     p.unit_title = r.unit_title
                                     p.acad_org = r.acad_org
                              Edited by: Chloe_19 on 7/08/2012 20:34
                              • 12. Re: Combining 2 CODES (thanks)
                                Etbin
                                Maybe (just extending the other final step)
                                with
                                base_data as
                                (select e.subject_cde || e.catalogue_no unit,
                                        c.unit_title,
                                        c.acad_org,
                                        e.term,
                                        count(e.oua_id) d_pct
                                   from table1 e,
                                        table2 c
                                  where e.sub || e.cat = c.sub || c.cat
                                    and e.year = c.year
                                    and e.year = '2012'
                                  group by e.subject_cde || e.catalogue_no,e.term,c.unit_title,c.acad_org
                                ),
                                regr_calcs as
                                (select unit,unit_title,acad_org,
                                        regr_slope(d_pct,rn)     r_slope,
                                        regr_intercept(d_pct,rn) r_intercept,
                                        regr_count(d_pct,rn)     r_count,
                                        regr_r2(d_pct,rn)        r_r2,
                                        regr_avgx(d_pct,rn)      r_avgx,
                                        regr_avgy(d_pct,rn)      r_avgy,
                                        regr_sxx(d_pct,rn)       r_sxx,
                                        regr_syy(d_pct,rn)       r_syy,
                                        regr_sxy(d_pct,rn)       r_sxy
                                   from (select unit,
                                                unit_title,
                                                acad_org,
                                                d_pct,
                                                row_number() over (partition by unit,unit_title,acad_org order by term) rn
                                           from base_data
                                          where pct != 0
                                        ) 
                                  group by unit,unit_title,acad_org
                                ),
                                pivoted as
                                (select unit,unit_title,acad_org,
                                        term_1201,term_1202,term_1203,term_1204,term_1205,term_1206
                                   from (select unit,unit_title,acad_org,
                                                term_1201,term_1202,term_1203,term_1204,term_1205,term_1206,
                                                row_number() over (partition by unit
                                                                       order by term_1201 desc,term_1202 desc,term_1203 desc,
                                                                                term_1204 desc,term_1205 desc,term_1206 desc
                                                                  ) rn
                                          from (select unit,unit_title,acad_org,
                                                       nvl(max(decode(term,1201,d_pct)),0) term_1201,
                                                       nvl(max(decode(term,1202,d_pct)),0) term_1202,
                                                       nvl(max(decode(term,1203,d_pct)),0) term_1203,
                                                       nvl(max(decode(term,1204,d_pct)),0) term_1204,
                                                       nvl(max(decode(term,1205,d_pct)),0) term_1205,
                                                       nvl(max(decode(term,1206,d_pct)),0) term_1206,
                                                  from (select unit,unit_title,acad_org,term,d_pct
                                                          from base_data
                                                       )
                                                 group by unit,unit_title,acad_org
                                               )
                                        )
                                  where rn = 1
                                )
                                select p.unit,p.unit_title,p.acad_org,
                                       p.term_1201,p.term_1202,p.term_1203,p.term_1204,p.term_1205,p.term_1206,
                                       r.r_slope,r.r_intercept,r.r_count,r.r_r2,r.r_avgx,r.r_avgy,r.r_sxx,r.r_syy,r.r_sxy,
                                       (r.r_intercept - p.term_1201) / r.r_slope new_term_1201,
                                       (r.r_intercept - p.term_1202) / r.r_slope new_term_1202,
                                       (r.r_intercept - p.term_1203) / r.r_slope new_term_1203,
                                       (r.r_intercept - p.term_1204) / r.r_slope new_term_1204,
                                       (r.r_intercept - p.term_1205) / r.r_slope new_term_1205,
                                       (r.r_intercept - p.term_1206) / r.r_slope new_term_1206
                                  from pivoted p,
                                       regr_calcs r
                                 where p.unit = r.unit
                                       p.unit_title = r.unit_title
                                       p.acad_org = r.acad_org
                                Regards

                                Etbin
                                • 13. Re: Combining 2 CODES (thanks)
                                  Chloe_19
                                  Once more you save me Etbin
                                  Thank you so much