This sounds like it should be super simple, but I can't think how to do it using plain SQL.
I am going to display some rows, and I want to number them in the display, like using rownum, except that the number only goes up when the row has some property (I don't care if it displays or not when it doesn't go up).
To explain, imagine
select rownum , the_date , to_char(dates.the_date,'DY') DAY from my_table order by the_date
select r_num, the_date, to_char(dates.the_date,'DY') DAY from (select rownum r_num, the_date from (select the_date from my_table where to_char(dates.the_date,'DY') IN ('MON','TUE','WED','THU','FRI') order by the_date) union select null r_num, the_date from my_table where to_char(dates.the_date,'DY') IN ('SAT','SUN')) order by the_date;
> This sounds like it should be super simple, but I can't think how to do > it using plain SQL.
> I am going to display some rows, and I want to number them in the display, > like using rownum, except that the number only goes up when the row has > some property (I don't care if it displays or not when it doesn't go up).
> To explain, imagine
> select rownum , the_date , to_char(dates.the_date,'DY') DAY > from my_table > order by the_date
Alternatively you can achieve it with analytics (if you like to have nls independent code, slightly more code is required)
SQL> alter session set nls_territory='AMERICA';
Session altered.
SQL> alter session set nls_date_language='AMERICAN';
Session altered.
SQL> SQL> with my_table as ( 2 select date '2008-01-01' + rownum -1 the_date 3 from dual connect by level <=10 4 ) 5 select 6 case 7 when 8 to_char(the_date,'d') not in (1,7) 9 then row_number() 10 over(partition by case when to_char(the_date,'d') not in (1,7) then 1 11 end order by the_date) 12 end row_num, 13 the_date, 14 to_char(the_date,'DY') DAY 15 from my_table 16 order by the_date;
> This sounds like it should be super simple, but I can't think how to do > it using plain SQL.
> I am going to display some rows, and I want to number them in the display, > like using rownum, except that the number only goes up when the row has > some property (I don't care if it displays or not when it doesn't go up).
> To explain, imagine
> select rownum , the_date , to_char(dates.the_date,'DY') DAY > from my_table > order by the_date
A third option, do it with the SUM analytical function and DECODE: SELECT TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE, TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY FROM DUAL CONNECT BY LEVEL<=20;
THE_DATE DAY --------- --- 01-JAN-08 TUE 02-JAN-08 WED 03-JAN-08 THU 04-JAN-08 FRI ... 19-JAN-08 SAT 20-JAN-08 SUN
Step 1: SELECT SUM(DECODE(DAY,'SAT',0,'SUN',0,1)) OVER (ORDER BY THE_DATE) MY_COUNT, THE_DATE, DAY FROM (SELECT TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE, TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY FROM DUAL CONNECT BY LEVEL<=20);
MY_COUNT THE_DATE DAY ---------- --------- --- 1 01-JAN-08 TUE 2 02-JAN-08 WED 3 03-JAN-08 THU 4 04-JAN-08 FRI 4 05-JAN-08 SAT 4 06-JAN-08 SUN 5 07-JAN-08 MON 6 08-JAN-08 TUE 7 09-JAN-08 WED 8 10-JAN-08 THU 9 11-JAN-08 FRI 9 12-JAN-08 SAT 9 13-JAN-08 SUN 10 14-JAN-08 MON 11 15-JAN-08 TUE 12 16-JAN-08 WED 13 17-JAN-08 THU 14 18-JAN-08 FRI 14 19-JAN-08 SAT 14 20-JAN-08 SUN
Now to remove the numbers that should not print, using a second DECODE: SELECT DECODE(DAY,'SAT',NULL,'SUN',NULL,SUM(DECODE(DAY,'SAT',0,'SUN',0,1)) OVER (ORDER BY THE_DATE)) MY_COUNT, THE_DATE, DAY FROM (SELECT TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE, TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY FROM DUAL CONNECT BY LEVEL<=20);
MY THE_DATE DAY -- --------- --- 1 01-JAN-08 TUE 2 02-JAN-08 WED 3 03-JAN-08 THU 4 04-JAN-08 FRI 05-JAN-08 SAT 06-JAN-08 SUN 5 07-JAN-08 MON 6 08-JAN-08 TUE 7 09-JAN-08 WED 8 10-JAN-08 THU 9 11-JAN-08 FRI 12-JAN-08 SAT 13-JAN-08 SUN 10 14-JAN-08 MON 11 15-JAN-08 TUE 12 16-JAN-08 WED 13 17-JAN-08 THU 14 18-JAN-08 FRI 19-JAN-08 SAT 20-JAN-08 SUN
Charles Hooper IT Manager/Oracle DBA K&M Machine-Fabricating, Inc.
: On Apr 17, 2:26=A0pm, yf...@vtn1.victoria.tc.ca (Malcolm Dew-Jones)
: wrote:
: > Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 : > : > This sounds like it should be super simple, but I can't think how to do : > it using plain SQL. : > : > I am going to display some rows, and I want to number them in the display,=
: > like using rownum, except that the number only goes up when the row has : > some property (I don't care if it displays or not when it doesn't go up). : > : > To explain, imagine : > : > =A0 =A0 =A0 =A0 select rownum =A0, the_date , to_char(dates.the_date,'DY')= : DAY : > =A0 =A0 =A0 =A0 from my_table : > =A0 =A0 =A0 =A0 order by the_date : > : > shows : > : > =A0 =A0 =A0 =A0 1 =A0 =A0 =A0 1-Jan-2008 =A0 =A0 =A0TUE : > =A0 =A0 =A0 =A0 2 =A0 =A0 =A0 2-Jan-2008 =A0 =A0 =A0WED : > =A0 =A0 =A0 =A0 3 =A0 =A0 =A0 3-Jan-2008 =A0 =A0 =A0THU : > =A0 =A0 =A0 =A0 4 =A0 =A0 =A0 4-Jan-2008 =A0 =A0 =A0FRI : > =A0 =A0 =A0 =A0 5 =A0 =A0 =A0 5-Jan-2008 =A0 =A0 =A0SAT : > =A0 =A0 =A0 =A0 6 =A0 =A0 =A0 6-Jan-2008 =A0 =A0 =A0SUN : > =A0 =A0 =A0 =A0 7 =A0 =A0 =A0 7-Jan-2008 =A0 =A0 =A0MON : > =A0 =A0 =A0 =A0 8 =A0 =A0 =A0 8-Jan-2008 =A0 =A0 =A0TUE : > : > but I don't want to count the weekend. =A0what I want to show would be the=
: A third option, do it with the SUM analytical function and DECODE: : SELECT : TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE, : TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY : FROM : DUAL : CONNECT BY : LEVEL<=3D20;
: THE_DATE DAY : --------- --- : 01-JAN-08 TUE : 02-JAN-08 WED : 03-JAN-08 THU : 04-JAN-08 FRI : =2E.. : 19-JAN-08 SAT : 20-JAN-08 SUN
: Step 1: : SELECT : SUM(DECODE(DAY,'SAT',0,'SUN',0,1)) OVER (ORDER BY THE_DATE) : MY_COUNT, : THE_DATE, : DAY : FROM : (SELECT : TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE, : TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY : FROM : DUAL : CONNECT BY : LEVEL<=3D20);
: Now to remove the numbers that should not print, using a second : DECODE: : SELECT : DECODE(DAY,'SAT',NULL,'SUN',NULL,SUM(DECODE(DAY,'SAT',0,'SUN',0,1)) : OVER (ORDER BY THE_DATE)) MY_COUNT, : THE_DATE, : DAY : FROM : (SELECT : TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1) THE_DATE, : TO_CHAR(TO_DATE('01/01/2008','MM/DD/YYYY')+(ROWNUM-1),'DY') DAY : FROM : DUAL : CONNECT BY : LEVEL<=3D20);
