Home » SQL & PL/SQL » SQL & PL/SQL » *WITH AS* (alternatives) other methods (2 threads merged by bb) (Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi)
*WITH AS* (alternatives) other methods (2 threads merged by bb) [message #562626] Thu, 02 August 2012 22:58 Go to next message
Chloe_19
Messages: 27
Registered: March 2012
Junior Member
I want the code i provided below to be used on the table below.
Without the
(select 'TERM1' terms, 311 count from dual union all
select 'TERM2',512 from dual union all
select 'TERM3',103 from dual union all
select 'TERM4',148 from dual union all
select 'TERM5',0 from dual
)

CREATE TABLE DAN_FOR_TEST(NAME     VARCHAR2(8),PLACE VARCHAR2 (8),
TERM1 VARCHAR2 (8),TERM2 VARCHAR2 (8),TERM3 VARCHAR2 (8),TERM4 VARCHAR2 (8),
TERM5 VARCHAR2 (8))  
INSERT INTO DAN_FOR_TEST (NAME,PLACE,TERM1,TERM2,TERM3,TERM4,TERM5) 
VALUES ('Program','ASIA','205','0','126','0','0');
INSERT INTO DAN_FOR_TEST (NAME,PLACE,TERM1,TERM2,TERM3,TERM4,TERM5) 
VALUES ('Banking','Europe','311','512','103','148','0');

Gives
NAME	PLACE	TERM1	TERM2	TERM3	TERM4	TERM5
Banking	Europe	311	512	103	148	0
Program	ASIA	205	0	126	0	0


I want to use this code (below) on the table above:
with
the_data as
(select 'TERM1' terms, 311 count from dual union all
select 'TERM2',512 from dual union all
select 'TERM3',103 from dual union all
select 'TERM4',148 from dual union all
select 'TERM5',0 from dual
)
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

gives:

R_SLOPE	R_INTERCEPT	R_COUNT	R_R2	R_AVGX	R_AVGY	R_SXX	R_SYY	R_SXY
-89.8	493	4	0.39142405	2.5	268.5	5	103009	-449

BUT WITHOUT THE

(select 'TERM1' terms, 311 count from dual union all
select 'TERM2',512 from dual union all
select 'TERM3',103 from dual union all
select 'TERM4',148 from dual union all
select 'TERM5',0 from dual
)



The reason i dont want the
(select 'TERM1' terms, 311 count from dual union all
select 'TERM2',512 from dual union all
select 'TERM3',103 from dual union all
select 'TERM4',148 from dual union all
select 'TERM5',0 from dual
)

is because i have a lot of record and i dont want to write out
select 'TERM2',512
select 'TERM3',464
select 'TERM4',312
select 'TERM5',512 for every record

Just want to used that code to be like FROM DAN_FOR_TEST


[Updated on: Tue, 14 August 2012 01:37] by Moderator

Report message to a moderator

Re: *WITH AS* (alternatives) other methods [message #562627 is a reply to message #562626] Thu, 02 August 2012 23:01 Go to previous messageGo to next message
BlackSwan
Messages: 23134
Registered: January 2009
Senior Member
https://forums.oracle.com/forums/thread.jspa?threadID=2422932&tstart=0
Re: *WITH AS* (alternatives) other methods [message #562628 is a reply to message #562627] Thu, 02 August 2012 23:07 Go to previous messageGo to next message
Chloe_19
Messages: 27
Registered: March 2012
Junior Member
???
Re: *WITH AS* (alternatives) other methods [message #562637 is a reply to message #562626] Fri, 03 August 2012 00:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59977
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Put them in a table and select from this one.

Regards
Michel
Re: *WITH AS* (alternatives) other methods [message #562645 is a reply to message #562637] Fri, 03 August 2012 01:13 Go to previous messageGo to next message
Chloe_19
Messages: 27
Registered: March 2012
Junior Member
Ummm care to eleborate?

I got this far:

SELECT NAME,TERM1,TERM2,TERM3,TERM4,TERM5,
       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(*) over() cnt,row_number() over (order by term1) rn,E.NAME,E.TERM1,E.TERM2,E.TERM3,E.TERM4,E.TERM5
          from dan_for_test E /* substitute with the real table name */
       
       )
 where cnt != 0
 GROUP BY NAME,TERM1,TERM2,TERM3,TERM4,TERM5


Gives me:
NAME	TERM1	TERM2	TERM3	TERM4	TERM5	R_SLOPE	R_INTERCEPT	R_COUNT	R_R2	R_AVGX	R_AVGY	R_SXX	R_SYY	R_SXY
Banking	311	512	103	148	0	0	0	1	0	2	2	0	0	0
Program	205	0	126	0	0	0	0	1	0	1	2	0	0	0
 


However the REGR alaculations are wrong
they should be

NAME	PLACE	TERM1	TERM2	TERM3	TERM4	TERM5	R_SLOPE	R_INTERCEPT	R_COUNT	R_R2	R_AVGX	R_AVGY	R_SXX	R_SYY	R_SXY
Banking	Europe	311	512	103	148	0	-89.8	493	4	0.39142405	2.5	268.5	5	103009	-449
 


NAME	PLACE	TERM1	TERM2	TERM3	TERM4	TERM5	R_SLOPE	R_INTERCEPT	R_COUNT	R_R2	R_AVGX	R_AVGY	R_SXX	R_SYY	R_SXY
Program	Asia	205	0	126	0	0	-39.5	244.5	2	1	2	165.5	2	3120.5	-79
Other option to PIVOT (alter code) [message #562810 is a reply to message #562626] Sun, 05 August 2012 22:59 Go to previous messageGo to next message
Chloe_19
Messages: 27
Registered: March 2012
Junior Member
I am using Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
I dont have the unpivot option in this version.
How could I alter the unpivot line of the code below (line 15) to allow me to run it in Oracle 10g 10.2.0.5.0.
Thanks.

withdata(p_name,place,term1,term2,term3,term4,term5) as
(select 'Banking','Europe',311,512,103,148,0 from dual union all
 select 'Program','ASIA',205,0,126,0,0 from dual)select p_name,       place,
       regr_slope(term_values,rn)     r_slope,
       regr_intercept(term_values,rn) r_intercept,
       regr_count(term_values,rn)     r_count,
       regr_r2(term_values,rn)        r_r2,
       regr_avgx(term_values,rn)      r_avgx,
       regr_avgy(term_values,rn)      r_avgy,
       regr_sxx(term_values,rn)       r_sxx,
       regr_syy(term_values,rn)       r_syy,
       regr_sxy(term_values,rn)       r_sxy
  from (select p_name,place,term_values,row_number() over (partition by p_name,place order by term) rn
            from (select p_name,place,term,term_values
                    from data
                  unpivot (term_values for term in (term1 as 1,term2 as 2,term3 as 3,term4 as 4,term5 as 5))
                 )           where term_values != 0       )
 group by p_name,place
Re: Other option to PIVOT (alter code) [message #562816 is a reply to message #562810] Mon, 06 August 2012 01:11 Go to previous messageGo to next message
Michel Cadot
Messages: 59977
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
With any SQL question, Post a working Test case: create table and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Regards
Michel
Re: Other option to PIVOT (alter code) [message #563442 is a reply to message #562816] Mon, 13 August 2012 20:13 Go to previous message
Barbara Boehmer
Messages: 8024
Registered: November 2002
Location: California, USA
Senior Member
SCOTT@orcl_11gR2> select * from dan_for_test
  2  /

NAME     PLACE    TERM1    TERM2    TERM3    TERM4    TERM5
-------- -------- -------- -------- -------- -------- --------
Program  ASIA     205      0        126      0        0
Banking  Europe   311      512      103      148      0

2 rows selected.

SCOTT@orcl_11gR2> select name,
  2  	    place,
  3  	    regr_slope(term_values,rn)	   r_slope,
  4  	    regr_intercept(term_values,rn) r_intercept,
  5  	    regr_count(term_values,rn)	   r_count,
  6  	    regr_r2(term_values,rn)	   r_r2,
  7  	    regr_avgx(term_values,rn)	   r_avgx,
  8  	    regr_avgy(term_values,rn)	   r_avgy,
  9  	    regr_sxx(term_values,rn)	   r_sxx,
 10  	    regr_syy(term_values,rn)	   r_syy,
 11  	    regr_sxy(term_values,rn)	   r_sxy
 12    from (select name, place, term_values,
 13  		    row_number() over (partition by name,place order by term) rn
 14  	       from (select name, place, 1 term, term1 term_values from dan_for_test
 15  		     union all
 16  		     select name, place, 2 term, term2 term_values from dan_for_test
 17  		     union all
 18  		     select name, place, 3 term, term3 term_values from dan_for_test
 19  		     union all
 20  		     select name, place, 4 term, term4 term_values from dan_for_test
 21  		     union all
 22  		     select name, place, 5 term, term5 term_values from dan_for_test)
 23  	      where term_values != 0)
 24   group by name, place
 25  /

NAME     PLACE       R_SLOPE R_INTERCEPT    R_COUNT       R_R2     R_AVGX     R_AVGY      R_SXX      R_SYY      R_SXY
-------- -------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
Banking  Europe        -89.8         493          4  .39142405        2.5      268.5          5     103009       -449
Program  ASIA            -79         284          2          1        1.5      165.5         .5     3120.5      -39.5

2 rows selected.

Previous Topic: Cursor reffering tablename
Next Topic: Oracle Date / Time Zone Conversion
Goto Forum:
  


Current Time: Thu Dec 18 05:58:53 CST 2014

Total time taken to generate the page: 0.10008 seconds