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  |
 |
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 #562645 is a reply to message #562637] |
Fri, 03 August 2012 01:13  |
|
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 |
|
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 #563442 is a reply to message #562816] |
Mon, 13 August 2012 20:13 |
 |
Barbara Boehmer
Messages: 7668
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.
|
|
|
|
Goto Forum:
Current Time: Wed May 22 16:38:42 CDT 2013
Total time taken to generate the page: 0.62592 seconds
|
] 
Members
Search
Help
Register
Login
Home
