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 Go to next message
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 #438322 is a reply to message #438315] Fri, 08 January 2010 01:42 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This is called a PIVOT and is explained in the link.
Re: How to get datwise data for whole month [message #438324 is a reply to message #438322] Fri, 08 January 2010 01:55 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
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 Go to previous messageGo to next message
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 #438340 is a reply to message #438337] Fri, 08 January 2010 03:22 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PIVOT does not exist in Oracle before 11g.
Apart from that you can build a dynamic query in the same way similar to the static one I mentioned for your days.

If you provide a test case (create table and insert statements) I can show you.

Regards
Michel
Re: How to get datwise data for whole month [message #438341 is a reply to message #438340] Fri, 08 January 2010 03:26 Go to previous messageGo to next message
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 #438342 is a reply to message #438341] Fri, 08 January 2010 03:43 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Since you're on 10g (according to the version information on this thread), you don't have the PIVOT clause at your disposal. We're somewhat limited. An SQL statement in Oracle needs to know what columns you are selecting from what source. This means that you will have to define a column for each possible date in your select. Before we get into this, I'd like to know whether the number of columns is fixed.

MHE
Re: How to get datwise data for whole month [message #438344 is a reply to message #438341] Fri, 08 January 2010 03:47 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You can consider below table as my sample table.

Yes but I'm too lazy to create the statements to build the test case, so I ask you to provide them.

Regards
Michel
Re: How to get datwise data for whole month [message #438345 is a reply to message #438337] Fri, 08 January 2010 03:57 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's an AskTom example of what you can do: Dynamic Report Headings

Regards
Michel
Re: How to get datwise data for whole month [message #438348 is a reply to message #438344] Fri, 08 January 2010 04:19 Go to previous messageGo to next message
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 to get datwise data for whole month [message #438349 is a reply to message #438342] Fri, 08 January 2010 04:21 Go to previous messageGo to next message
harshaprakash
Messages: 42
Registered: January 2010
Member
Columns are not fixed. That is I need 31 columns for jan, and 28/29 for Feb... etc.

Re: How make this SQL query run Under ORACLE [message #438355 is a reply to message #438315] Fri, 08 January 2010 05:26 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Bear in mind that most of us on this site don't have access to sql server systems and don't know sql server code.
If you explain what that query is trying to do you'll be far more likely to get a useful answer.
Re: How make this SQL query run Under ORACLE [message #438357 is a reply to message #438355] Fri, 08 January 2010 05:33 Go to previous messageGo to next message
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 make this SQL query run Under ORACLE [message #438358 is a reply to message #438357] Fri, 08 January 2010 05:41 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
So it's exactly the same question as this one that you just asked
Re: How make this SQL query run Under ORACLE [message #438359 is a reply to message #438358] Fri, 08 January 2010 05:46 Go to previous messageGo to next message
harshaprakash
Messages: 42
Registered: January 2010
Member
This question is sub-question of that.

There I had request for a solution. Here I have a solution which works in Sql and giving Syntax issues in Oracle.

Harsha.
Re: How to get datwise data for whole month [message #438363 is a reply to message #438349] Fri, 08 January 2010 05:50 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I was afraid you'd say that. I suggest you follow the link Michel posted. Perhaps that might offer a way out.

MHE
Re: How make this SQL query run Under ORACLE [message #438373 is a reply to message #438359] Fri, 08 January 2010 07:30 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
It is not only syntax issue, it is the whole stuff is NOT Oracle.
This is a whole script not a single SQL statement.

Regards
Michel
Re: How to get datwise data for whole month [message #438376 is a reply to message #438348] Fri, 08 January 2010 07:50 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Below are the insert statements for the table.

1/ I have no OMSIR schema
2/ Table name in insert and create table don't match
3/ Column names don't match
4/ User and date are reserved words and can't be used as column names

In short, this or nothing is the same.
Waiting for a WORKING test case.

Regards
Michel
Re: How to get datwise data for whole month [message #438378 is a reply to message #438345] Fri, 08 January 2010 08:28 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
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 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
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 Go to previous messageGo to next message
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 #438582 is a reply to message #438581] Mon, 11 January 2010 01:42 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You can either use the first method I showed here or the first one I used in AskTom topic.

Regards
Michel
Re: How to get datwise data for whole month [message #438584 is a reply to message #438582] Mon, 11 January 2010 01:48 Go to previous messageGo to next message
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 #438585 is a reply to message #438584] Mon, 11 January 2010 01:51 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
But in first method, I need to hard code all the dates right ?

Wrong, where do you see I hard-coded them?
The only thing that is hard coded is the number maximum of columns which is 31 for a month.

Quote:
That will not fetch data dynamically right ?

Wrong, it dynamically fetches the column names in the first query.

Regards
Michel
Re: How to get datwise data for whole month [message #438600 is a reply to message #438585] Mon, 11 January 2010 02:50 Go to previous messageGo to next message
harshaprakash
Messages: 42
Registered: January 2010
Member
Sorry Michel.

Thank you very much for the reply. It is working Now.

Only change is It has show all the dates for the current month.(Month will be a parameter which I supply).

If value is not there for any date it has to show zero.

Please let me know how to do that.

harsha.

[Updated on: Mon, 11 January 2010 02:55]

Report message to a moderator

Re: How to get datwise data for whole month [message #438607 is a reply to message #438600] Mon, 11 January 2010 03:09 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
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

Re: How to get datwise data for whole month [message #438609 is a reply to message #438607] Mon, 11 January 2010 03:22 Go to previous messageGo to next message
harshaprakash
Messages: 42
Registered: January 2010
Member
Michel,

Thanks a lot for reply.

As of second query is returning data like this
user 1/1 1/2 1/4
a 20 30
b 40 40 60

But I want data in below given format. Please help.
user 1/1 1/2 1/3 1/4 ....
a 20 30 0 0 ...
b 40 40 0 60 ...
Re: How to get datwise data for whole month [message #438613 is a reply to message #438609] Mon, 11 January 2010 03:37 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In the second query, outer join your table with this same table to get the result for all days.

Regards
Michel
feature not enabled error while running query [message #440112 is a reply to message #438315] Thu, 21 January 2010 02:47 Go to previous messageGo to next message
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 Go to previous messageGo to next message
ThomasG
Messages: 3184
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.
Re: feature not enabled error while running query [message #440118 is a reply to message #440113] Thu, 21 January 2010 03:08 Go to previous messageGo to next message
harshaprakash
Messages: 42
Registered: January 2010
Member
Thank you very much.

But I have another database in this server. And there this query is running fine.

Please let me know is there any way by which I can enable that feature.

Harsha.
Re: feature not enabled error while running query [message #440119 is a reply to message #440118] Thu, 21 January 2010 03:13 Go to previous messageGo to next message
Michel Cadot
Messages: 63804
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
You need to have Oracle Enterprise Edition or Personal Edition (not Standard Edition) to use these functions

Install one of these editions.

Regards
Michel
Re: feature not enabled error while running query [message #440120 is a reply to message #440118] Thu, 21 January 2010 03:13 Go to previous messageGo to next message
ThomasG
Messages: 3184
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
What does the "select * from v$version;" on this "other database" show you?
Re: feature not enabled error while running query [message #440122 is a reply to message #440118] Thu, 21 January 2010 03:16 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
harshaprakash wrote on Thu, 21 January 2010 14:38
Thank you very much.

But I have another database in this server. And there this query is running fine.

Please let me know is there any way by which I can enable that feature.
.

The only possible solution is to follow thomasg suggestion.
OR rewrite your code without using those functions.
And even its better to upgrade to the supported version of Oracle.

And if it is working on another server,is that having same version (8.1.0.7)?
And whats the option for OLAP Window Functions true?or false ?


I have to upgrade/increase my network speed Wink

sriram Smile

[Updated on: Thu, 21 January 2010 03:17]

Report message to a moderator

Re: feature not enabled error while running query [message #440123 is a reply to message #440120] Thu, 21 January 2010 03:30 Go to previous messageGo to next message
harshaprakash
Messages: 42
Registered: January 2010
Member
Thanks all of you for your valuable suggestions.
The other one is

Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production. That's why it is running there.

Is upgrading Oracle is the only solution ?

Thanks again.

Harsha
Re: feature not enabled error while running query [message #440125 is a reply to message #440123] Thu, 21 January 2010 03:33 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
Thanks for the feedback..
Upgrading is one solution..
The Other is not using that function in your Query.

Sriram Smile
Re: feature not enabled error while running query [message #440129 is a reply to message #440125] Thu, 21 January 2010 04:17 Go to previous messageGo to previous message
harshaprakash
Messages: 42
Registered: January 2010
Member
Thank you very much for the reply.

Can you please help me out to write that query without using windows functions.

Harsha.
Previous Topic: BULK UPDATE examples
Next Topic: SQL problem
Goto Forum:
  


Current Time: Mon Sep 26 11:11:48 CDT 2016

Total time taken to generate the page: 0.18965 seconds