| decode value by year wise [message #21719] |
Sun, 25 August 2002 21:09  |
Raju
Messages: 74
Registered: March 1999
|
Member |
|
|
Hi,
I want a report values of orders financial wise.
the output should be like
customername 99-00 00-01 01-02 02-03
xxxxxx xxx xxx xxx xxxx
I have changed deptno with orderdt
select sum(decode(deptno,10,sal)) dept10, but it will show only for a particular given date, but the sum of value should be for a period of date (i.e, apr-99 to mar-00 & apr-00 to mar-01,...). Can anybody help me.
Regards,
Raju
|
|
|
|
| Re: decode value by year wise [message #21729 is a reply to message #21719] |
Mon, 26 August 2002 12:03  |
andrew again
Messages: 2577
Registered: March 2000
|
Senior Member |
|
|
in sqlplus, you can do some tricks to change column headings.
SQL> --Grab string into a variable
SQL> column days7ago new_val days7ago noprint
SQL> select '<= '||to_char(sysdate-7, 'mm/dd/rr') days7ago from dual;
SQL> -- Set new heading for col1 to variable
SQL> column col1 heading '&days7ago'
SQL> select object_type, sum(
2 case
3 when created <= sysdate -7 then 1 end) col1,
4 sum(case
5 when created between sysdate -7 and sysdate -0 then 1 end) col2
6 from user_objects
7 group by object_type;
OBJECT_TYPE <= 08/19/02 COL2
------------------ ----------- ----------
DATABASE LINK 2 2
FUNCTION 1
INDEX 2
PACKAGE 5 1
PACKAGE BODY 4 1
PROCEDURE 1 2
SEQUENCE 1
SYNONYM 3 2
TABLE 4
VIEW 1
10 rows selected.
|
|
|
|
| Re: decode value by year wise [message #21733 is a reply to message #21729] |
Mon, 26 August 2002 20:27 |
Raju
Messages: 74
Registered: March 1999
|
Member |
|
|
Iam using oracle 7.3 on win NT. Your query is giving error like ORA-00907: Missing righ parenthesis. Pl. correct. I want the output like
customername 99-00 00-01 01-02 02-03
xxxxxxxxxx xxx xxx xxx xxx
regards.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
