Home » SQL & PL/SQL » SQL & PL/SQL » How to Use DECODE as a group function with TEXT?
icon9.gif  How to Use DECODE as a group function with TEXT? [message #207840] Thu, 07 December 2006 03:23 Go to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Hi all..

This is a script for the create table:
create table loading_log(
TABLE_NAME		VARCHAR2(40),
SOURCE			VARCHAR2(10),
BILL_PERIOD		VARCHAR2(7),
TIMESTAMP		DATE,
ETL_FINISH		VARCHAR2(1),
YEAR_MONTH		VARCHAR2(6)
);


This is for the insert
insert into loading_log values ('LOCAL_CALL','NBS','01',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','NBS','04',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','NBS','07',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','NBS','10',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','NBS','13',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','NBS','16',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','NBS','19',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','NBS','22',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','NBS','25',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','NBS','28',sysdate,'Y','200609'); 

insert into loading_log values ('LOCAL_CALL','CV01','01',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV01','04',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV01','07',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV01','10',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV01','13',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV01','16',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV01','19',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV01','22',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV01','25',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV01','28',sysdate,'Y','200609'); 

insert into loading_log values ('LOCAL_CALL','CV21','01',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV21','04',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV21','07',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV21','10',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV21','13',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV21','16',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV21','19',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV21','22',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV21','25',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV21','28',sysdate,'Y','200609'); 

insert into loading_log values ('LOCAL_CALL','CV24','01',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV24','04',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV24','07',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV24','10',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV24','13',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV24','16',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV24','19',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV24','22',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV24','25',sysdate,'Y','200609'); 
insert into loading_log values ('LOCAL_CALL','CV24','28',sysdate,'Y','200609'); 

insert into loading_log values ('LOCAL_CALL','NBS','01',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','NBS','04',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','NBS','07',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','NBS','10',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','NBS','13',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','NBS','16',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','NBS','19',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','NBS','22',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','NBS','25',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','NBS','28',sysdate,'Y','200610'); 

insert into loading_log values ('LOCAL_CALL','CV01','01',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV01','04',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV01','07',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV01','10',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV01','13',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV01','16',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV01','19',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV01','22',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV01','25',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV01','28',sysdate,'Y','200610'); 

insert into loading_log values ('LOCAL_CALL','CV21','01',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV21','04',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV21','07',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV21','10',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV21','13',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV21','16',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV21','19',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV21','22',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV21','25',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV21','28',sysdate,'Y','200610'); 

insert into loading_log values ('LOCAL_CALL','CV24','01',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV24','04',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV24','07',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV24','10',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV24','13',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV24','16',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV24','19',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV24','22',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV24','25',sysdate,'Y','200610'); 
insert into loading_log values ('LOCAL_CALL','CV24','28',sysdate,'Y','200610'); 


And this is my query script:
column 01 format a10
column 04 format a10
column 07 format a10
column 10 format a10
column 13 format a10
column 16 format a10
column 19 format a10
column 22 format a10
column 25 format a10
column 28 format a10
column SOURCE format a6
break on MONTH skip 1
set linesize 150
set pagesize 63
SELECT 
to_date(SUBSTR (YEAR_MONTH, -2),'MM') "MONTH", 
source "SOURCE",
decode(sum(decode (BILL_PERIOD, 01, 1, 000000000)),1, 'LOADED', 0) "01",
decode(sum(decode (BILL_PERIOD, 04, 1, 000000000)),1, 'LOADED', 0) "04",
decode(sum(decode (BILL_PERIOD, 07, 1, 000000000)),1, 'LOADED', 0) "07",
decode(sum(decode (BILL_PERIOD, 10, 1, 000000000)),1, 'LOADED', 0) "10",
decode(sum(decode (BILL_PERIOD, 13, 1, 000000000)),1, 'LOADED', 0) "13",
decode(sum(decode (BILL_PERIOD, 16, 1, 000000000)),1, 'LOADED', 0) "16",
decode(sum(decode (BILL_PERIOD, 19, 1, 000000000)),1, 'LOADED', 0) "19",
decode(sum(decode (BILL_PERIOD, 22, 1, 000000000)),1, 'LOADED', 0) "22",
decode(sum(decode (BILL_PERIOD, 25, 1, 000000000)),1, 'LOADED', 0) "25",
decode(sum(decode (BILL_PERIOD, 28, 1, 000000000)),1, 'LOADED', 0) "28"
FROM loading_log where year_month like '2006%' and table_name ='LOCAL_CALL' and 
SUBSTR (YEAR_MONTH, -2) in ('01','02','03','04','05','06','07','08','09','10','11','12')
group by to_date(SUBSTR (YEAR_MONTH, -2),'MM'), SOURCE
order by to_date(SUBSTR (YEAR_MONTH, -2),'MM'); 

My question is..

How do I transform the text 'LOADED' into value of timestamp instead?

Thank you very much.

[Updated on: Thu, 07 December 2006 22:52]

Report message to a moderator

Re: How to Use DECODE as a group function with TEXT? [message #207841 is a reply to message #207840] Thu, 07 December 2006 03:24 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
FYI, the above query is working fine.

It's just that instead of displaying the text 'LOADED' I want it to return the value of timestamp.

TQVM.
Re: How to Use DECODE as a group function with TEXT? [message #207849 is a reply to message #207841] Thu, 07 December 2006 03:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Your problem is that you are summing up several rows, each of which may have a different timestamp value.
Which Timestamp would you like it to return - first, last, average, earliest, latest?
Re: How to Use DECODE as a group function with TEXT? [message #207897 is a reply to message #207840] Thu, 07 December 2006 04:48 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

I gave it a go, I don't know if this is what you want

column 01 format a10
column 04 format a10
column 07 format a10
column 10 format a10
column 13 format a10
column 16 format a10
column 19 format a10
column 22 format a10
column 25 format a10
column 28 format a10
column SOURCE format a6
break on MONTH skip 1
set linesize 150
set pagesize 63
SELECT 
to_date(SUBSTR (YEAR_MONTH, -2),'MM') "MONTH", 
source "SOURCE",
decode(sum(decode (BILL_PERIOD, 01, 1, 000000000)),1, to_char(loading_log.timestamp), 0) "01",
decode(sum(decode (BILL_PERIOD, 04, 1, 000000000)),1, to_char(loading_log.timestamp), 0) "04",
decode(sum(decode (BILL_PERIOD, 07, 1, 000000000)),1, to_char(loading_log.timestamp), 0) "07",
decode(sum(decode (BILL_PERIOD, 10, 1, 000000000)),1, to_char(loading_log.timestamp), 0) "10",
decode(sum(decode (BILL_PERIOD, 13, 1, 000000000)),1, to_char(loading_log.timestamp), 0) "13",
decode(sum(decode (BILL_PERIOD, 16, 1, 000000000)),1, to_char(loading_log.timestamp), 0) "16",
decode(sum(decode (BILL_PERIOD, 19, 1, 000000000)),1, to_char(loading_log.timestamp), 0) "19",
decode(sum(decode (BILL_PERIOD, 22, 1, 000000000)),1, to_char(loading_log.timestamp), 0) "22",
decode(sum(decode (BILL_PERIOD, 25, 1, 000000000)),1, to_char(loading_log.timestamp), 0) "25",
decode(sum(decode (BILL_PERIOD, 28, 1, 000000000)),1, to_char(loading_log.timestamp), 0) "28"
FROM loading_log 
where year_month like '2006%' 
and table_name ='LOCAL_CALL' 
and SUBSTR (YEAR_MONTH, -2) in ('01','02','03','04','05','06','07','08','09','10','11','12')
group by to_date(SUBSTR (YEAR_MONTH, -2),'MM'), SOURCE, timestamp
order by to_date(SUBSTR (YEAR_MONTH, -2),'MM');


I replaced the text 'LOADING' with the field timestamp but used a to_char as I was replacing text.
As a consequence I had to add timestamp to the group by.

It gave this result on the test data you supplied
SQL> 

MONTH       SOURCE 01         04         07         10         13         16         19         22         25         28
----------- ------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
01/09/2006  NBS    12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06
01/09/2006  CV01   12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06
01/09/2006  CV21   12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06
01/09/2006  CV24   12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06
01/10/2006  NBS    12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06
01/10/2006  CV01   12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06
01/10/2006  CV21   12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06
01/10/2006  CV24   12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06  12-DEC-06

8 rows selected

SQL> 


Really must get back to my work now Razz
Re: How to Use DECODE as a group function with TEXT? [message #208018 is a reply to message #207897] Thu, 07 December 2006 18:48 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
thanks a lot scorpio_biker for willing to help..

Well, that is exactly what I want.. Unfortunately, in the real case the timestamp is actually different for each bill period. I'm sorry for that. So, you can use this script to mimic the real case.

update loading_log set timestamp = sysdate + 01  where year_month='200610' and bill_period=01;
update loading_log set timestamp = sysdate + 04  where year_month='200610' and bill_period=04;
update loading_log set timestamp = sysdate + 07  where year_month='200610' and bill_period=07;
update loading_log set timestamp = sysdate + 10  where year_month='200610' and bill_period=10;
update loading_log set timestamp = sysdate + 13  where year_month='200610' and bill_period=13;
update loading_log set timestamp = sysdate + 16  where year_month='200610' and bill_period=16;
update loading_log set timestamp = sysdate + 19  where year_month='200610' and bill_period=19;
update loading_log set timestamp = sysdate + 22  where year_month='200610' and bill_period=22;
update loading_log set timestamp = sysdate + 25  where year_month='200610' and bill_period=25;
update loading_log set timestamp = sysdate + 28  where year_month='200610' and bill_period=28;

update loading_log set timestamp = sysdate + 01  where year_month='200609' and bill_period=01;
update loading_log set timestamp = sysdate + 04  where year_month='200609' and bill_period=04;
update loading_log set timestamp = sysdate + 07  where year_month='200609' and bill_period=07;
update loading_log set timestamp = sysdate + 10  where year_month='200609' and bill_period=10;
update loading_log set timestamp = sysdate + 13  where year_month='200609' and bill_period=13;
update loading_log set timestamp = sysdate + 16  where year_month='200609' and bill_period=16;
update loading_log set timestamp = sysdate + 19  where year_month='200609' and bill_period=19;
update loading_log set timestamp = sysdate + 22  where year_month='200609' and bill_period=22;
update loading_log set timestamp = sysdate + 25  where year_month='200609' and bill_period=25;
update loading_log set timestamp = sysdate + 28  where year_month='200609' and bill_period=28;


So, if you use the script you've mentioned above, you would see there would be a lot of 0s scattered everywhere since the date is different.

So, that is what actually I want to cater.

That's why I'm confused of the usage of decode in this case since normally, we would use math group function to eliminate those 0s but in this case, it is a date/text string.

By the way, I'm wondering why you don't have to specify the date format e.g. 'DD-MON-YY' when using to_char.. is it valid?

Thank you in advance.
Re: How to Use DECODE as a group function with TEXT? [message #208019 is a reply to message #207849] Thu, 07 December 2006 18:54 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
JRowbottom wrote on Thu, 07 December 2006 17:32
Your problem is that you are summing up several rows, each of which may have a different timestamp value.
Which Timestamp would you like it to return - first, last, average, earliest, latest?

I want it to return the timestamp for that particular bill_period and year_month.

For example, timestamp for source 'CV24' during year_month='200609':
TABLE_NAME                               SOURCE BILL_PE TIMESTAMP E YEAR_M
---------------------------------------- ------ ------- --------- - ------
LOCAL_CALL                               CV24   01      09-DEC-06 Y 200609
LOCAL_CALL                               CV24   04      12-DEC-06 Y 200609
LOCAL_CALL                               CV24   07      15-DEC-06 Y 200609
LOCAL_CALL                               CV24   10      18-DEC-06 Y 200609
LOCAL_CALL                               CV24   13      21-DEC-06 Y 200609
LOCAL_CALL                               CV24   16      24-DEC-06 Y 200609
LOCAL_CALL                               CV24   19      27-DEC-06 Y 200609
LOCAL_CALL                               CV24   22      30-DEC-06 Y 200609
LOCAL_CALL                               CV24   25      02-JAN-07 Y 200609
LOCAL_CALL                               CV24   28      05-JAN-07 Y 200609

So, as you might already see, I want those timestamps to be displayed horizontally as the output.
Re: How to Use DECODE as a group function with TEXT? [message #208071 is a reply to message #207840] Fri, 08 December 2006 01:10 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
Moreover, how I do I set the month as 'JAN', 'FEB', 'MAR' etc. but I want it to be sorted as month e.g. JAN would appear first rather than FEB .

Tq.
Re: How to Use DECODE as a group function with TEXT? [message #208092 is a reply to message #207840] Fri, 08 December 2006 02:28 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
Hi,

As far as I know the to_char on a date without a format will return the date in the nls format set for the database (in my case it's DD-MON-RR), so it is valid (albeit lazy Embarassed) . Probably better to include a format to ensure consistancy though.
Re: How to Use DECODE as a group function with TEXT? [message #208101 is a reply to message #208092] Fri, 08 December 2006 03:16 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Is this what you're looking for.
If not, can you take 5 minutes and describe (preferably with an example) exactly what you need the output to look like
SELECT to_date(SUBSTR (YEAR_MONTH, -2),'MM') "MONTH"
      ,source "SOURCE",
       decode(sum(decode (BILL_PERIOD, 01, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 01, timestamp, null))), 0) "01",
       decode(sum(decode (BILL_PERIOD, 04, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 04, timestamp, null))), 0) "04",
       decode(sum(decode (BILL_PERIOD, 07, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 07, timestamp, null))), 0) "07",
       decode(sum(decode (BILL_PERIOD, 10, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 10, timestamp, null))), 0) "10",
       decode(sum(decode (BILL_PERIOD, 13, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 13, timestamp, null))), 0) "13",
       decode(sum(decode (BILL_PERIOD, 16, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 16, timestamp, null))), 0) "16",
       decode(sum(decode (BILL_PERIOD, 19, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 19, timestamp, null))), 0) "19",
       decode(sum(decode (BILL_PERIOD, 22, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 22, timestamp, null))), 0) "22",
       decode(sum(decode (BILL_PERIOD, 25, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 25, timestamp, null))), 0) "25",
       decode(sum(decode (BILL_PERIOD, 28, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 28, timestamp, null))), 0) "28"
FROM   loading_log 
where  year_month like '2006%' 
and    table_name ='LOCAL_CALL' 
and    SUBSTR (YEAR_MONTH, -2) in ('01','02','03','04','05','06','07','08','09','10','11','12')
group by to_date(SUBSTR (YEAR_MONTH, -2),'MM'), SOURCE
order by to_date(SUBSTR (YEAR_MONTH, -2),'MM'); 
Re: How to Use DECODE as a group function with TEXT? [message #208113 is a reply to message #207840] Fri, 08 December 2006 04:14 Go to previous messageGo to next message
scorpio_biker
Messages: 154
Registered: November 2005
Location: Kent, England
Senior Member
This seems to work with the month name based on the example from JRowbottom

SELECT to_char(to_date(year_month,'YYYYMM'),'MON') "MONTH"
      ,source "SOURCE",
       decode(sum(decode (BILL_PERIOD, 01, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 01, timestamp, null))), 0) "01",
       decode(sum(decode (BILL_PERIOD, 04, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 04, timestamp, null))), 0) "04",
       decode(sum(decode (BILL_PERIOD, 07, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 07, timestamp, null))), 0) "07",
       decode(sum(decode (BILL_PERIOD, 10, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 10, timestamp, null))), 0) "10",
       decode(sum(decode (BILL_PERIOD, 13, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 13, timestamp, null))), 0) "13",
       decode(sum(decode (BILL_PERIOD, 16, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 16, timestamp, null))), 0) "16",
       decode(sum(decode (BILL_PERIOD, 19, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 19, timestamp, null))), 0) "19",
       decode(sum(decode (BILL_PERIOD, 22, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 22, timestamp, null))), 0) "22",
       decode(sum(decode (BILL_PERIOD, 25, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 25, timestamp, null))), 0) "25",
       decode(sum(decode (BILL_PERIOD, 28, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 28, timestamp, null))), 0) "28"
FROM   loading_log 
where  year_month like '2006%' 
and    table_name ='LOCAL_CALL' 
and    SUBSTR (YEAR_MONTH, -2) in ('01','02','03','04','05','06','07','08','09','10','11','12')
group by to_char(to_date(year_month,'YYYYMM'),'MON'), SOURCE
order by to_date(to_char(to_date(year_month,'YYYYMM'),'MON'),'MON'); 
Re: How to Use DECODE as a group function with TEXT? [message #208215 is a reply to message #208113] Fri, 08 December 2006 11:40 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
dates as dates, strings as strings - worth a read...
http://tkyte.blogspot.com/2006/11/see-you-just-cannot-make-this-stuff-up.html
Re: How to Use DECODE as a group function with TEXT? [message #208431 is a reply to message #208101] Sun, 10 December 2006 20:02 Go to previous messageGo to next message
aimy
Messages: 209
Registered: June 2006
Senior Member
JRowbottom wrote on Fri, 08 December 2006 17:16
Is this what you're looking for.
If not, can you take 5 minutes and describe (preferably with an example) exactly what you need the output to look like
SELECT to_date(SUBSTR (YEAR_MONTH, -2),'MM') "MONTH"
      ,source "SOURCE",
       decode(sum(decode (BILL_PERIOD, 01, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 01, timestamp, null))), 0) "01",
       decode(sum(decode (BILL_PERIOD, 04, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 04, timestamp, null))), 0) "04",
       decode(sum(decode (BILL_PERIOD, 07, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 07, timestamp, null))), 0) "07",
       decode(sum(decode (BILL_PERIOD, 10, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 10, timestamp, null))), 0) "10",
       decode(sum(decode (BILL_PERIOD, 13, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 13, timestamp, null))), 0) "13",
       decode(sum(decode (BILL_PERIOD, 16, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 16, timestamp, null))), 0) "16",
       decode(sum(decode (BILL_PERIOD, 19, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 19, timestamp, null))), 0) "19",
       decode(sum(decode (BILL_PERIOD, 22, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 22, timestamp, null))), 0) "22",
       decode(sum(decode (BILL_PERIOD, 25, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 25, timestamp, null))), 0) "25",
       decode(sum(decode (BILL_PERIOD, 28, 1, 0)),1, to_char(max(decode (BILL_PERIOD, 28, timestamp, null))), 0) "28"
FROM   loading_log 
where  year_month like '2006%' 
and    table_name ='LOCAL_CALL' 
and    SUBSTR (YEAR_MONTH, -2) in ('01','02','03','04','05','06','07','08','09','10','11','12')
group by to_date(SUBSTR (YEAR_MONTH, -2),'MM'), SOURCE
order by to_date(SUBSTR (YEAR_MONTH, -2),'MM'); 


WOW!!! Thanks a lot!!

That is exactly what I want!! Thank you so much!! Cool

Care to explain by the way? Embarassed
Re: How to Use DECODE as a group function with TEXT? [message #208432 is a reply to message #208113] Sun, 10 December 2006 20:04 Go to previous message
aimy
Messages: 209
Registered: June 2006
Senior Member
scorpio_biker wrote on Fri, 08 December 2006 18:14
This seems to work with the month name based on the example from JRowbottom

SELECT to_char(to_date(year_month,'YYYYMM'),'MON') "MONTH"
.
..
...
....
order by to_date(to_char(to_date(year_month,'YYYYMM'),'MON'),'MON'); 


Thanks scropio..

Never knew that I could use different function for the order by.

Coz usually I would get an error if I order by something which I've not included in the select statement.. Sad
Previous Topic: how to make use of save text.
Next Topic: Mapping column names into variables
Goto Forum:
  


Current Time: Thu Dec 08 00:34:45 CST 2016

Total time taken to generate the page: 0.05869 seconds