On Apr 17, 2:26=A0pm, yf...@[EMAIL PROTECTED]
(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=
> following instead
>
> =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 =A0 =A0 =A0 =A0 5-Jan-2008 =A0 =A0 =A0SAT
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 6-Jan-2008 =A0 =A0 =A0SUN
> =A0 =A0 =A0 =A0 5 =A0 =A0 =A0 7-Jan-2008 =A0 =A0 =A0MON
> =A0 =A0 =A0 =A0 6 =A0 =A0 =A0 8-Jan-2008 =A0 =A0 =A0TUE
>
> Looks simple, but how to do this in plain SQL? =A0
>
> Feedback welcome, thanks.
>
> Malcolm
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);
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<=3D20);
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.
|
