Home » SQL & PL/SQL » SQL & PL/SQL » "How to get datwise data for whole month" (merged 3) and "feature not enabled error&q (8i, windows xp)
| "How to get datwise data for whole month" (merged 3) and "feature not enabled error&q [message #438315] |
Fri, 08 January 2010 01:24  |
harshaprakash
Messages: 42
Registered: January 2010
|
Member |
|
|
Hi,
I am storing transaction data for users vertically as I have shown below in table1.
And now I need to get the data for whole month horizontally as I have shown below in table2.
Please let me know how to write query for same ?
Table1:-
user Date trans
a 1/1/2010 10
a 1/1/2010 10
b 1/1/2010 20
b 1/1/2010 20
a 2/1/2010 30
b 2/1/2010 40
a 4/1/2010 60
Table2:-
user 1/1/2010 2/1/2010 3/1/2010 4/1/2010 5/1/2010 ........................
a 20 30 0 60 0 .....
b 40 40 0 0 0 .......
I want data for whole month (till last day of the month) as shown above.
Regards,
harsha.
|
|
|
|
|
|
| Re: How to get datwise data for whole month [message #438324 is a reply to message #438322] |
Fri, 08 January 2010 01:55  |
harshaprakash
Messages: 42
Registered: January 2010
|
Member |
|
|
Thank you for the reply.
I can't user PIVOT as given there. Because data for the month has to come dynamically. I will pass only month to the query.
I discussed about same thing in another forum. I am attaching that file. You may get some hint if you read that.
Waiting for your help..
Harsha.
|
|
|
|
| Re: How to get datwise data for whole month [message #438325 is a reply to message #438315] |
Fri, 08 January 2010 01:55 |
 |
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
SQL> select deptno,
2 sum(decode(extract(year from hiredate),1980,1,0)) "1980",
3 sum(decode(extract(year from hiredate),1981,1,0)) "1981",
4 sum(decode(extract(year from hiredate),1982,1,0)) "1982",
5 sum(decode(extract(year from hiredate),1980,0,1981,0,1982,0,1)) "Other Year"
6 from emp
7 group by deptno
8 order by deptno
9 /
DEPTNO 1980 1981 1982 Other Year
---------- ---------- ---------- ---------- ----------
10 0 2 1 0
20 1 2 0 2
30 0 6 0 0
Regards
Michel
|
|
|
|
| Re: How to get datwise data for whole month [message #438337 is a reply to message #438325] |
Fri, 08 January 2010 02:54 |
harshaprakash
Messages: 42
Registered: January 2010
|
Member |
|
|
Hi,
I don't want to hard code any values. dynamically It has to get the values. I have attached one file in my above thread. In that there is a solution which works in Sql and in Oracle it is giving some errors.
Below I am pasting that solution. Please let me know how to change this so that it will work in Oracle also.
Create Table #Table1
(
users varchar(10),
Date datetime,
trans int
)
insert into #Table1
Select 'a','1/1/2010',10
Union All
Select 'a','1/1/2010',10
Union All
Select 'b','1/1/2010',20
Union All
Select 'b','1/1/2010',20
Union All
Select 'a','1/2/2010',30
Union All
Select 'b','1/2/2010',40
Union All
Select 'b','1/4/2010',60
DECLARE @SqlQuery nVARCHAR(MAX)
DECLARE @Date VARCHAR(MAX)
set @Date=''
SET @SqlQuery = ''
Select @Date=@Date+','+Date
From
(
Select Distinct '['+Convert(Char(10),Date,101)+']' As Date
From #Table1
) As AA
Set @Date=substring(@Date,2,len(@Date))
SET @SqlQuery ='SELECT *
FROM
(
SELECT
Distinct
users
, (
Convert(Char(10),Date,101)
) AS date1
, trans
FROM #Table1
) AS ItemDetail
PIVOT ( SUM(trans) FOR date1 IN ( ' + @Date + ' ) ) AS pvt '
print @SqlQuery
EXECUTE ( @SqlQuery )
--Output
--users 01/01/2010 01/02/2010 01/04/2010
----------------------------------------
--a 10 30 NULL
--b 20 40 60
drop table #Table1
Harsha.
|
|
|
|
|
|
| Re: How to get datwise data for whole month [message #438341 is a reply to message #438340] |
Fri, 08 January 2010 03:26 |
harshaprakash
Messages: 42
Registered: January 2010
|
Member |
|
|
Hi,
thanks for the reply.
You can consider below table as my sample table.
column - datatype
user varchar
date datetime
trans int
Table1:-
user Date trans
a 1/1/2010 10
a 1/1/2010 10
b 1/1/2010 20
b 1/1/2010 20
a 2/1/2010 30
b 2/1/2010 40
a 4/1/2010 60
Harsha.
|
|
|
|
|
|
|
|
|
|
| Re: How to get datwise data for whole month [message #438348 is a reply to message #438344] |
Fri, 08 January 2010 04:19 |
harshaprakash
Messages: 42
Registered: January 2010
|
Member |
|
|
Hi,
Below are the insert statements for the table.
CREATE TABLE OMSIR.test
(
User VARCHAR2(1000),
Date DATE,
Trans INTEGER
)
LOGGING
NOCACHE
NOPARALLEL;
INSERT INTO "OMSIR"."TESTDATE" (
"USERNAME", "EDATE", "TRANS"
) VALUES (
'a',
TO_DATE('01-SEP-1921', 'DD-MON-RRRR'), 10
);
INSERT INTO "OMSIR"."TESTDATE" (
"USERNAME", "EDATE", "TRANS"
) VALUES (
'a',
TO_DATE('02-SEP-1921', 'DD-MON-RRRR'), 10
);
INSERT INTO "OMSIR"."TESTDATE" (
"USERNAME", "EDATE", "TRANS"
) VALUES (
'b',
TO_DATE('02-SEP-1921', 'DD-MON-RRRR'), 60
);
INSERT INTO "OMSIR"."TESTDATE" (
"USERNAME", "EDATE", "TRANS"
) VALUES (
'b',
TO_DATE('01-SEP-1921', 'DD-MON-RRRR'), 10
);
INSERT INTO "OMSIR"."TESTDATE" (
"USERNAME", "EDATE", "TRANS"
) VALUES (
'a',
TO_DATE('02-SEP-1921', 'DD-MON-RRRR'), 10
);
INSERT INTO "OMSIR"."TESTDATE" (
"USERNAME", "EDATE", "TRANS"
) VALUES (
'b',
TO_DATE('01-SEP-1921', 'DD-MON-RRRR'), 10
); |
|
|
|
|
|
|
|
| Re: How make this SQL query run Under ORACLE [message #438357 is a reply to message #438355] |
Fri, 08 January 2010 05:33 |
harshaprakash
Messages: 42
Registered: January 2010
|
Member |
|
|
Sorry, I didn't knew that.
The above sql code will fetch data from table and show in the below format.
Table is like this.
user Date trans
a 1/1/2010 10
a 1/1/2010 10
b 1/1/2010 20
b 1/1/2010 20
a 2/1/2010 30
b 2/1/2010 40
b 4/1/2010 60
This table will be shown in below format. And this is an example. So I want same query to work in Oracle also.
--Output
--users 01/01/2010 01/02/2010 01/04/2010
----------------------------------------
a 10 30 NULL
b 20 40 60
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to get datwise data for whole month [message #438378 is a reply to message #438345] |
Fri, 08 January 2010 08:28 |
|
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Here's another way than the one I mentioned in the link:
alter session set nls_date_format='MM/DD/YYYY';
Create Table Table1
(
users varchar(10),
Dates date,
trans int
)
/
insert into Table1
Select 'a','1/1/2010',10 from dual
Union All
Select 'a','1/1/2010',10 from dual
Union All
Select 'b','1/1/2010',20 from dual
Union All
Select 'b','1/1/2010',20 from dual
Union All
Select 'a','1/2/2010',30 from dual
Union All
Select 'b','1/2/2010',40 from dual
Union All
Select 'b','1/4/2010',60 from dual
/
commit;
SQL> select * from table1 order by 1, 2;
USERS DATES TRANS
---------- ---------- ----------
a 01/01/2010 10
a 01/01/2010 10
a 01/02/2010 30
b 01/01/2010 20
b 01/01/2010 20
b 01/02/2010 40
b 01/04/2010 60
7 rows selected.
SQL> col c01 new_value c01
SQL> col c02 new_value c02
SQL> col c03 new_value c03
SQL> col c04 new_value c04
SQL> col c05 new_value c05
SQL> col c06 new_value c06
SQL> col c07 new_value c07
SQL> col c08 new_value c08
SQL> col c09 new_value c09
SQL> col c10 new_value c10
SQL> col c11 new_value c11
SQL> col c12 new_value c12
SQL> col c13 new_value c13
SQL> col c14 new_value c14
SQL> col c15 new_value c15
SQL> col c16 new_value c16
SQL> col c17 new_value c17
SQL> col c18 new_value c18
SQL> col c19 new_value c19
SQL> col c20 new_value c20
SQL> col c21 new_value c21
SQL> col c22 new_value c22
SQL> col c23 new_value c23
SQL> col c24 new_value c24
SQL> col c25 new_value c25
SQL> col c26 new_value c26
SQL> col c27 new_value c27
SQL> col c28 new_value c28
SQL> col c29 new_value c29
SQL> col c30 new_value c30
SQL> col c31 new_value c31
SQL> set termout off
SQL> select max(decode(rk, 1,dates)) c01,
2 max(decode(rk, 2,dates)) c02,
3 max(decode(rk, 3,dates)) c03,
4 max(decode(rk, 4,dates)) c04,
5 max(decode(rk, 5,dates)) c05,
6 max(decode(rk, 6,dates)) c06,
7 max(decode(rk, 7,dates)) c07,
8 max(decode(rk, 8,dates)) c08,
9 max(decode(rk, 9,dates)) c09,
10 max(decode(rk,10,dates)) c10,
11 max(decode(rk,11,dates)) c11,
12 max(decode(rk,12,dates)) c12,
13 max(decode(rk,13,dates)) c13,
14 max(decode(rk,14,dates)) c14,
15 max(decode(rk,15,dates)) c15,
16 max(decode(rk,16,dates)) c16,
17 max(decode(rk,17,dates)) c17,
18 max(decode(rk,18,dates)) c18,
19 max(decode(rk,19,dates)) c19,
20 max(decode(rk,20,dates)) c20,
21 max(decode(rk,21,dates)) c21,
22 max(decode(rk,22,dates)) c22,
23 max(decode(rk,23,dates)) c23,
24 max(decode(rk,24,dates)) c24,
25 max(decode(rk,25,dates)) c25,
26 max(decode(rk,26,dates)) c26,
27 max(decode(rk,27,dates)) c27,
28 max(decode(rk,28,dates)) c28,
29 max(decode(rk,29,dates)) c29,
30 max(decode(rk,30,dates)) c30,
31 max(decode(rk,31,dates)) c31
32 from (select distinct dates, dense_rank() over(order by dates) rk from table1)
33 /
C01 C02 C03 C04 C05 C06 C07 C08 C09 C10 C11
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
C12 C13 C14 C15 C16 C17 C18 C19 C20 C21 C22
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
C23 C24 C25 C26 C27 C28 C29 C30 C31
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
01/01/2010 01/02/2010 01/04/2010
1 row selected.
SQL> set termout on
SQL> col c01 heading "&c01"
SQL> col c02 heading "&c02"
SQL> col c03 heading "&c03"
SQL> col c04 heading "&c04"
SQL> col c05 heading "&c05"
SQL> col c06 heading "&c06"
SQL> col c07 heading "&c07"
SQL> col c08 heading "&c08"
SQL> col c09 heading "&c09"
SQL> col c10 heading "&c10"
SQL> col c11 heading "&c11"
SQL> col c12 heading "&c12"
SQL> col c13 heading "&c13"
SQL> col c14 heading "&c14"
SQL> col c15 heading "&c15"
SQL> col c16 heading "&c16"
SQL> col c17 heading "&c17"
SQL> col c18 heading "&c18"
SQL> col c19 heading "&c19"
SQL> col c20 heading "&c20"
SQL> col c21 heading "&c21"
SQL> col c22 heading "&c22"
SQL> col c23 heading "&c23"
SQL> col c24 heading "&c24"
SQL> col c25 heading "&c25"
SQL> col c26 heading "&c26"
SQL> col c27 heading "&c27"
SQL> col c28 heading "&c28"
SQL> col c29 heading "&c29"
SQL> col c30 heading "&c30"
SQL> col c31 heading "&c31"
SQL> set linesize 1000
SQL> set trimout on
SQL> set trimspool on
SQL> set underline off
SQL> select users,
2 sum(decode(rk, 1,trans)) c01,
3 sum(decode(rk, 2,trans)) c02,
4 sum(decode(rk, 3,trans)) c03,
5 sum(decode(rk, 4,trans)) c04,
6 sum(decode(rk, 5,trans)) c05,
7 sum(decode(rk, 6,trans)) c06,
8 sum(decode(rk, 7,trans)) c07,
9 sum(decode(rk, 8,trans)) c08,
10 sum(decode(rk, 9,trans)) c09,
11 sum(decode(rk,10,trans)) c10,
12 sum(decode(rk,11,trans)) c11,
13 sum(decode(rk,12,trans)) c12,
14 sum(decode(rk,13,trans)) c13,
15 sum(decode(rk,14,trans)) c14,
16 sum(decode(rk,15,trans)) c15,
17 sum(decode(rk,16,trans)) c16,
18 sum(decode(rk,17,trans)) c17,
19 sum(decode(rk,18,trans)) c18,
20 sum(decode(rk,19,trans)) c19,
21 sum(decode(rk,20,trans)) c20,
22 sum(decode(rk,21,trans)) c21,
23 sum(decode(rk,22,trans)) c22,
24 sum(decode(rk,23,trans)) c23,
25 sum(decode(rk,24,trans)) c24,
26 sum(decode(rk,25,trans)) c25,
27 sum(decode(rk,26,trans)) c26,
28 sum(decode(rk,27,trans)) c27,
29 sum(decode(rk,28,trans)) c28,
30 sum(decode(rk,29,trans)) c29,
31 sum(decode(rk,30,trans)) c30,
32 sum(decode(rk,31,trans)) c31
33 from (select users, dates, trans, dense_rank() over (order by dates) rk from table1)
34 group by users
35 order by users
36 /
USERS 01/01/2010 01/02/2010 01/04/2010
a 20 30
b 40 40 60
2 rows selected.
If you put this in a script what is between "set termout off" and "set termout on" does not appear on the screen as well as all SQL*Plus commands (set, col...), I let them here to show it works.
Regards
Michel
|
|
|
|
| Re: How to get datwise data for whole month [message #438382 is a reply to message #438378] |
Fri, 08 January 2010 08:51 |
|
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Another way to do it, shorter but maybe harder to uderstand is to execute a query that generates the final query.
Once again what's between "set termout off" and "set termout on" does not appear if you put it in a script:
SQL> set termout off
SQL> col sql new_value sql
SQL> select max('users'||
2 replace(sys_connect_by_path('sum(decode(rk,'||rk||',trans)) "'||dates||'"','#'),'#',', ')||
3 ' from (select users, dates, trans, dense_rank() over (order by dates) rk from table1)'||
4 ' group by users order by users') sql
5 from (select distinct dates, dense_rank() over(order by dates) rk from table1)
6 connect by prior rk = rk-1
7 start with rk = 1
8 /
SQL
------------------------------------------------------------------------------------------------------------------------
users, sum(decode(rk,1,trans)) "01/01/2010", sum(decode(rk,2,trans)) "01/02/2010", sum(decode(rk,3,trans)) "01/04/2010"
from (select users, dates, trans, dense_rank() over (order by dates) rk from table1) group by users order by users
1 row selected.
SQL> set termout on
SQL> select &sql
2 /
USERS 01/01/2010 01/02/2010 01/04/2010
---------- ---------- ---------- ----------
a 20 30
b 40 40 60
2 rows selected.
Regards
Michel
|
|
|
|
| Re: How to get datwise data for whole month [message #438560 is a reply to message #438382] |
Sun, 10 January 2010 22:30 |
harshaprakash
Messages: 42
Registered: January 2010
|
Member |
|
|
Hi Michel,
Thank you very much for the help.
I created a table by below script.
Create Table Table1
(
users varchar(10),
Dates varchar(50),
trans int
)
I inserted data into that table using below code.
insert into Table1
Select 'a','1/1/2010',10 from dual
Union All
Select 'a','1/1/2010',10 from dual
Union All
Select 'b','1/1/2010',20 from dual
Union All
Select 'b','1/1/2010',20 from dual
Union All
Select 'a','1/2/2010',30 from dual
Union All
Select 'b','1/2/2010',40 from dual
Union All
Select 'b','1/4/2010',60 from dual
Now I am trying to run the script which you have given and it is saying ORA-00904: invalid column name at "sys_connect_by_path". Script is as below:-
select max('users'||
replace(sys_connect_by_path('sum(decode(rk,'||rk||',trans)) "'||dates||'"','#'),'#',', ')||
' from (select users, dates, trans, dense_rank() over (order by dates) rk from table1)'||
' group by users order by users') sql
from (select distinct dates, dense_rank() over(order by dates) rk from table1)
connect by prior rk = rk-1
start with rk = 1
Please guide me. What changes I need to do in this script ?
[Updated on: Sun, 10 January 2010 22:31] Report message to a moderator |
|
|
|
| Re: How to get datwise data for whole month [message #438577 is a reply to message #438560] |
Mon, 11 January 2010 01:19 |
|
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
Use SQL*PLus and copy and paste your session as I did.
If sys_connect_by_path is not known then you are not in 10g as you said.
I copied and pasted your statement in my SQL*Plus session and it works:
SQL> select max('users'||
2 replace(sys_connect_by_path('sum(decode(rk,'||rk||',trans)) "'||dates||'"','#'),'#',', ')||
3 ' from (select users, dates, trans, dense_rank() over (order by dates) rk from table1)'||
4 ' group by users order by users') sql
5 from (select distinct dates, dense_rank() over(order by dates) rk from table1)
6 connect by prior rk = rk-1
7 start with rk = 1
8 /
SQL
------------------------------------------------------------------------------------------------------------------------
users, sum(decode(rk,1,trans)) "01/01/2010", sum(decode(rk,2,trans)) "01/02/2010", sum(decode(rk,3,trans)) "01/04/2010"
from (select users, dates, trans, dense_rank() over (order by dates) rk from table1) group by users order by users
1 row selected.
Regards
Michel
[Updated on: Mon, 11 January 2010 01:20] Report message to a moderator |
|
|
|
| Re: How to get datwise data for whole month [message #438581 is a reply to message #438577] |
Mon, 11 January 2010 01:32 |
harshaprakash
Messages: 42
Registered: January 2010
|
Member |
|
|
Thanks Michel,
I was trying to run query through TOAD. I am really sorry My Oracle version is 8i. My TOAD version was 10. I confused both.
After creating table I tried to ran below script in SQL Plus. But It is not working.
Sorry for the inconvenience caused. Please suggest me what to do now?
|
|
|
|
|
|
| Re: How to get datwise data for whole month [message #438584 is a reply to message #438582] |
Mon, 11 January 2010 01:48 |
harshaprakash
Messages: 42
Registered: January 2010
|
Member |
|
|
Michel,
Thanks for the reply.
But in first method, I need to hard code all the dates right ?
That will not fetch data dynamically right ?
If I pass month and year, Query has to return data for whole month.
I think you were referring to below code. Please help me.
SQL> col c01 new_value c01
SQL> col c02 new_value c02
SQL> col c03 new_value c03
SQL> col c04 new_value c04
SQL> col c05 new_value c05
SQL> col c06 new_value c06
SQL> col c07 new_value c07
SQL> col c08 new_value c08
SQL> col c09 new_value c09
SQL> col c10 new_value c10
SQL> col c11 new_value c11
SQL> col c12 new_value c12
SQL> col c13 new_value c13
SQL> col c14 new_value c14
SQL> col c15 new_value c15
SQL> col c16 new_value c16
SQL> col c17 new_value c17
SQL> col c18 new_value c18
SQL> col c19 new_value c19
SQL> col c20 new_value c20
SQL> col c21 new_value c21
SQL> col c22 new_value c22
SQL> col c23 new_value c23
SQL> col c24 new_value c24
SQL> col c25 new_value c25
SQL> col c26 new_value c26
SQL> col c27 new_value c27
SQL> col c28 new_value c28
SQL> col c29 new_value c29
SQL> col c30 new_value c30
SQL> col c31 new_value c31
SQL> set termout off
SQL> select max(decode(rk, 1,dates)) c01,
2 max(decode(rk, 2,dates)) c02,
3 max(decode(rk, 3,dates)) c03,
4 max(decode(rk, 4,dates)) c04,
5 max(decode(rk, 5,dates)) c05,
6 max(decode(rk, 6,dates)) c06,
7 max(decode(rk, 7,dates)) c07,
8 max(decode(rk, 8,dates)) c08,
9 max(decode(rk, 9,dates)) c09,
10 max(decode(rk,10,dates)) c10,
11 max(decode(rk,11,dates)) c11,
12 max(decode(rk,12,dates)) c12,
13 max(decode(rk,13,dates)) c13,
14 max(decode(rk,14,dates)) c14,
15 max(decode(rk,15,dates)) c15,
16 max(decode(rk,16,dates)) c16,
17 max(decode(rk,17,dates)) c17,
18 max(decode(rk,18,dates)) c18,
19 max(decode(rk,19,dates)) c19,
20 max(decode(rk,20,dates)) c20,
21 max(decode(rk,21,dates)) c21,
22 max(decode(rk,22,dates)) c22,
23 max(decode(rk,23,dates)) c23,
24 max(decode(rk,24,dates)) c24,
25 max(decode(rk,25,dates)) c25,
26 max(decode(rk,26,dates)) c26,
27 max(decode(rk,27,dates)) c27,
28 max(decode(rk,28,dates)) c28,
29 max(decode(rk,29,dates)) c29,
30 max(decode(rk,30,dates)) c30,
31 max(decode(rk,31,dates)) c31
32 from (select distinct dates, dense_rank() over(order by dates) rk from table1)
33 /
C01 C02 C03 C04 C05 C06 C07 C08 C09 C10 C11
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
C12 C13 C14 C15 C16 C17 C18 C19 C20 C21 C22
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
C23 C24 C25 C26 C27 C28 C29 C30 C31
---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
01/01/2010 01/02/2010 01/04/2010
1 row selected.
SQL> set termout on
SQL> col c01 heading "&c01"
SQL> col c02 heading "&c02"
SQL> col c03 heading "&c03"
SQL> col c04 heading "&c04"
SQL> col c05 heading "&c05"
SQL> col c06 heading "&c06"
SQL> col c07 heading "&c07"
SQL> col c08 heading "&c08"
SQL> col c09 heading "&c09"
SQL> col c10 heading "&c10"
SQL> col c11 heading "&c11"
SQL> col c12 heading "&c12"
SQL> col c13 heading "&c13"
SQL> col c14 heading "&c14"
SQL> col c15 heading "&c15"
SQL> col c16 heading "&c16"
SQL> col c17 heading "&c17"
SQL> col c18 heading "&c18"
SQL> col c19 heading "&c19"
SQL> col c20 heading "&c20"
SQL> col c21 heading "&c21"
SQL> col c22 heading "&c22"
SQL> col c23 heading "&c23"
SQL> col c24 heading "&c24"
SQL> col c25 heading "&c25"
SQL> col c26 heading "&c26"
SQL> col c27 heading "&c27"
SQL> col c28 heading "&c28"
SQL> col c29 heading "&c29"
SQL> col c30 heading "&c30"
SQL> col c31 heading "&c31"
SQL> set linesize 1000
SQL> set trimout on
SQL> set trimspool on
SQL> set underline off
SQL> select users,
2 sum(decode(rk, 1,trans)) c01,
3 sum(decode(rk, 2,trans)) c02,
4 sum(decode(rk, 3,trans)) c03,
5 sum(decode(rk, 4,trans)) c04,
6 sum(decode(rk, 5,trans)) c05,
7 sum(decode(rk, 6,trans)) c06,
8 sum(decode(rk, 7,trans)) c07,
9 sum(decode(rk, 8,trans)) c08,
10 sum(decode(rk, 9,trans)) c09,
11 sum(decode(rk,10,trans)) c10,
12 sum(decode(rk,11,trans)) c11,
13 sum(decode(rk,12,trans)) c12,
14 sum(decode(rk,13,trans)) c13,
15 sum(decode(rk,14,trans)) c14,
16 sum(decode(rk,15,trans)) c15,
17 sum(decode(rk,16,trans)) c16,
18 sum(decode(rk,17,trans)) c17,
19 sum(decode(rk,18,trans)) c18,
20 sum(decode(rk,19,trans)) c19,
21 sum(decode(rk,20,trans)) c20,
22 sum(decode(rk,21,trans)) c21,
23 sum(decode(rk,22,trans)) c22,
24 sum(decode(rk,23,trans)) c23,
25 sum(decode(rk,24,trans)) c24,
26 sum(decode(rk,25,trans)) c25,
27 sum(decode(rk,26,trans)) c26,
28 sum(decode(rk,27,trans)) c27,
29 sum(decode(rk,28,trans)) c28,
30 sum(decode(rk,29,trans)) c29,
31 sum(decode(rk,30,trans)) c30,
32 sum(decode(rk,31,trans)) c31
33 from (select users, dates, trans, dense_rank() over (order by dates) rk from table1)
34 group by users
35 order by users
36 /
USERS 01/01/2010 01/02/2010 01/04/2010
a 20 30
b 40 40 60
2 rows selected.
|
|
|
|
|
|
|
|
| Re: How to get datwise data for whole month [message #438607 is a reply to message #438600] |
Mon, 11 January 2010 03:09 |
|
Michel Cadot
Messages: 68644
Registered: March 2007
Location: Nanterre, France, http://...
|
Senior Member
Account Moderator |
|
|
In the first query, instead of searching all dates in the table use a row generator to select all days in a month.
For instance, the next query generates all days of the current month, modify it as you needs (note: it is a 8i query newer version allows queries much simpler):
select trunc(sysdate,'MONTH')+rn-1
from (select rownum rn
from (select 1 from dual group by cube(1,2,3,4,5))
where rownum <= last_day(sysdate) - trunc(sysdate,'MONTH') + 1
)
/
In the second query, outer join your table with this same table to get the result for all days.
Regards
Michel
[Updated on: Mon, 11 January 2010 03:11] Report message to a moderator |
|
|
|
|
|
|
|
| feature not enabled error while running query [message #440112 is a reply to message #438315] |
Thu, 21 January 2010 02:47 |
harshaprakash
Messages: 42
Registered: January 2010
|
Member |
|
|
Hi,
I have written an sql query. I am trying to run that in Oracle. I am using TOAD for Oracle. My Oracle version is 8i.
In Oracle I have two databases. In one db it is running fine and in other db it is throwing error. Below I am giving query.
select username,userid,sum(decode(rk, 1,trans)) c01,sum(decode(rk, 2,trans)) c02,sum(decode(rk, 3,trans)) c03,sum(decode(rk, 4,trans)) c04,sum(decode(rk, 5,trans)) c05,sum(decode(rk, 6,trans)) c06,sum(decode(rk, 7,trans)) c07,sum(decode(rk, 8,trans)) c08,sum(decode(rk, 9,trans)) c09,sum(decode(rk,10,trans)) c10,sum(decode(rk,11,trans)) c11,sum(decode(rk,12,trans)) c12,sum(decode(rk,13,trans)) c13,sum(decode(rk,14,trans)) c14,sum(decode(rk,15,trans)) c15,sum(decode(rk,16,trans)) c16,sum(decode(rk,17,trans)) c17,sum(decode(rk,18,trans)) c18,sum(decode(rk,19,trans)) c19,sum(decode(rk,20,trans)) c20,sum(decode(rk,21,trans)) c21,sum(decode(rk,22,trans)) c22,sum(decode(rk,23,trans)) c23,sum(decode(rk,24,trans)) c24,sum(decode(rk,25,trans)) c25,sum(decode(rk,26,trans)) c26,sum(decode(rk,27,trans)) c27,sum(decode(rk,28,trans)) c28,sum(decode(rk,29,trans)) c29,sum(decode(rk,30,trans)) c30,sum(decode(rk,31,trans)) c31 from (select username, tblutilization.userID, UTIDATE, cast(substr(supporthrs,1,2)as int)*60 + cast(substr(supporthrs,4,2) as int) + cast(substr(travelhrs,1,2)as int)*60 + cast(substr(travelhrs,4,2) as int) as trans, dense_rank() over (order by UTIDATE) rk from tblutilization,fogsuser where tblutilization.userid = fogsuser.userid ) WHERE utidate > to_date('12/01/2009','MM.DD.YYYY') and utidate < to_date('1/31/2010','MM.DD.YYYY') group by username,userid order by userid
If I run this query it shows below error :
ORA-00439: feature not enabled: OLAP Window Functions
Also below are the details of the DB which may help you to give solution.
1) select * from v$version;
Oracle8i Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
CORE 8.1.7.0.0 Production
TNS for 32-bit Windows: Version 8.1.7.0.0 - Production
NLSRTL Version 3.4.1.0.0 - Production
2)select * from v$option
Partitioning FALSE
Objects TRUE
Parallel Server FALSE
Advanced replication FALSE
Bit-mapped indexes FALSE
Connection multiplexing TRUE
Connection pooling TRUE
Database queuing FALSE
Incremental backup and recovery FALSE
Instead-of triggers TRUE
Parallel backup and recovery FALSE
Parallel execution FALSE
Parallel load TRUE
Point-in-time tablespace recovery FALSE
Fine-grained access control FALSE
N-Tier authentication/authorization TRUE
Function-based indexes FALSE
Plan Stability FALSE
Online Index Build FALSE
Coalesce Index FALSE
Managed Standby FALSE
Materialized view rewrite FALSE
Materialized view warehouse refresh FALSE
Database resource manager FALSE
Spatial FALSE
Visual Information Retrieval FALSE
Export transportable tablespaces FALSE
Transparent Application Failover FALSE
Fast-Start Fault Recovery FALSE
Sample Scan FALSE
Duplexed backups FALSE
Java FALSE
OLAP Window Functions FALSE
3) SELECT owner, COUNT(*) FROM dba_objects
WHERE status = 'INVALID' group by owner
BNGOMS 2
CTXSYS 32
NARODASCM 1
OMSIR 6
OMSWADCO 3
ORDSYS 9
ROADOMS 2
SCM 1
SERVICEPORTAL 1
SYS 4
SYSTEM 1
TKOMS 5
VBG 2
Harsha.
[Updated on: Thu, 21 January 2010 03:01] by Moderator Report message to a moderator |
|
|
|
| Re: feature not enabled error while running query [message #440113 is a reply to message #440112] |
Thu, 21 January 2010 02:52 |
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
|
Senior Member |
|
|
Quote:
ORA-00439: feature not enabled: OLAP Window Functions
Quote:
OLAP Window Functions FALSE
Well. The feature is not enabled, like the error message says.
You need to have Oracle Enterprise Edition or Personal Edition (not Standard Edition) to use these functions.
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Goto Forum:
Current Time: Wed Apr 24 02:54:14 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
