A reader, September 13, 2001 - 3:27 pm UTC
Kindly explain the below query
1 select dt, to_char( decode( to_char( dt, 'dy'), 'fri', dt, next_day(dt-7,'fri') ), 'Dy, DD-MON-YYYY' )
2* from ( select sysdate+rownum dt from all_objects where rownum < 10 )
ops$tkyte@ORA817DEV.US.ORACLE.COM> /
and in 2
why are you selecting from all_objects, what I am supposed to replace it with in my code
September 13, 2001 - 4:04 pm UTC
They asked for the last business day -- the last friday before today. I assumed that if it was friday -- they wanted to return that day.
So, the decode( to_char(dt,'dy','fri', dt, next_day(dt-7,'fri') )
is the same as:
if ( dt is friday )
then
return dt;
else
return the next friday that happens after dt-7;
end if;
As for 2, use all_objects -- you just need any table with a couple of rows.
play
play, September 13, 2001 - 4:37 pm UTC
by business day what I meant was, any weekday which is not a holiday.
September 13, 2001 - 5:04 pm UTC
Well, that is not what you said ;)
Just use the answser from #1 and expand on the answer using number two then. Use the same exact technique (eg: apply the concepts to a different problem).
ops$tkyte@ORA817DEV.US.ORACLE.COM> select max(dt)
2 from ( select decode( to_char( sysdate, 'dy'), 'fri', sysdate, next_day(sysdate-7,'fri') )-rownum+1 dt
3 from all_objects
4 where rownum <= 25
5 )
6 where to_char(dt,'dy') not in ( 'sat', 'sun' )
7 and not exists ( select NULL from holiday where h = trunc(dt) )
8 /
MAX(DT)
---------
07-SEP-01
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> insert into holiday values ( '07-sep-2001' );
1 row created.
ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select max(dt)
2 from ( select decode( to_char( sysdate, 'dy'), 'fri', sysdate, next_day(sysdate-7,'fri') )-rownum+1 dt
3 from all_objects
4 where rownum <= 25
5 )
6 where to_char(dt,'dy') not in ( 'sat', 'sun' )
7 and not exists ( select NULL from holiday where h = trunc(dt) )
8 /
MAX(DT)
---------
06-SEP-01
Here we just generate a set of dates in the inline view (candidate dates) starting with the previous friday (just like the original #1) and then back 25 days. The where clause gets rid of weekends and holidays. We keep the max date from that and its the one we want. Unless you have some 25 days of holidays -- this'll work. If you are worried about that, use more rownums...
cant we use dual
A reader, September 13, 2001 - 7:59 pm UTC
Why cant we use dual instead of all_objects.
I dont think our team lead will allow us to make a reference to all_objects like this.
Unless and untill we fully satisfy him, as to why we have to use any table there.
September 13, 2001 - 8:42 pm UTC
Well, dual has how many rows??? one.
It won't work unless the table we select from has at least a couple of rows.
I urge you -- before you use this, please understand what it does. Don't take it "as is", please take the time to understand it, comprehend it. If you do that - it will be obvious that DUAL could not be used.
The entire purpose of using all_objects where rownum <= 25 is just to make up 25 rows. We need to make a SET of dates from whence we will get the date we really want.
Create your own table, put some rows in it. Use that instead if you want.
(your team lead should get a life if they are into not letting you do things like that. why, why would they say "no, you cannot query all_objects". that is just silly. Hopefully a 'team lead' would look at it, understand what it does and say "hey, thats pretty cool", not "i'm afraid of all_objects -- don't do it").
Excellent
Jim, September 14, 2001 - 7:34 am UTC
Another good one Tom!
Your comments re understanding your reply are valid.
I get the impression many people using your site
are simply hoping you will write their code for them!
?
Je, September 18, 2001 - 2:00 am UTC
and not exists ( select NULL from holiday where h = trunc(dt)
I guess instead of null, we can also select 1 or 'X'
Iam I right.
September 18, 2001 - 9:05 am UTC
you could select *, sysdate, 1, 'x', 'hello world' -- whatever you want. It is not relevant. Whatever you select is NOT USED, ignored, not part of the answer.
select NULL in a (not) EXISTS is to me the semantically correct way to say it -- I am in fact selecting NOTHING and nothing says nothing better then NULL.
Last Business day, version 9.2.0.3
A reader, July 08, 2004 - 12:29 pm UTC
Dear Tom,
We have a table which stores every date, the second column has a Y/N indicator which tells if the date is a business day. We want a SQL query (Possibly using analytic functions) which displays the last business day against each date.
Here's how the table looks..
Day buss_day_ind
---------- ------------
07/08/2002 Y
07/09/2002 Y
07/10/2002 Y
07/11/2002 Y
07/12/2002 Y
07/13/2004 N
07/14/2004 N
07/15/2002 Y
We are expecting a resultset in this fashion..
Day Last_buss_day
---------- -------------
07/08/2002 07/05/2002
07/09/2002 07/08/2002
07/10/2002 07/09/2002
07/11/2002 07/10/2002
07/12/2002 07/11/2002
07/15/2002 07/12/2002
Can you help?
Thanks!
July 08, 2004 - 12:32 pm UTC
select day, lag(day) over (order by day)
from t
where buss_day_ind = 'Y';
Simply superb!
A reader, July 08, 2004 - 12:40 pm UTC
Database user/version/uptime
John, July 08, 2004 - 1:02 pm UTC
Tom,
I'm writing a simple sql script to see the current user,instance name and database uptime when I'm on sqlplus prompt.Here is it.
====================================================
select
'User Name is : '||user||chr(10)||
'Database Instance Name is : '||global_name||chr(10)||
'Version is : '||round(banner)||chr(50)||
'Uptime : '||floor(xx)||'days '||floor((xx-floor(xx))*24)||'hours '||round(((xx-floor(xx
)*24)-floor((xx-floor(xx)*24)))*60)||'minutes' "Database Uptime"
from (select user,global_name,banner,(sysdate-STARTUP_TIME) xx from v$instance,v$version,global_name)
/
=================================================
but the output i'm getting is
==================================================
Database Uptime
--------------------------------------------------------------------------------
User Name is : username
Database Instance Name is : orcl.oracle.COM
Version is : Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production2Up
time : 20days 17hours 44minutes
User Name is : username
Database Instance Name is : orcl.oracle.COM
Version is : PL/SQL Release 9.2.0.4.0 - Production2Uptime : 20days 17hours 44min
utes
User Name is : username
Database Uptime
--------------------------------------------------------------------------------
Database Instance Name is : orcl.oracle.COM
Version is : CORE 9.2.0.3.0 Production2Uptime : 20days 17hours 44minutes
User Name is : username
Database Instance Name is : orcl.oracle.COM
Version is : TNS for Solaris: Version 9.2.0.4.0 - Production2Uptime : 20days 17h
ours 44minutes
User Name is : username
Database Instance Name is : orcl.oracle.COM
Version is : NLSRTL Version 9.2.0.4.0 - Production2Uptime : 20days 17hours 44min
Database Uptime
--------------------------------------------------------------------------------
utes
====================================================
I would like to see,
===================================================
User Name is : username
Database Instance Name is : orcl.oracle.COM
Version is : Oracle9i Enterprise Edition Release
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
PL/SQL Release 9.2.0.4.0 - Production
CORE 9.2.0.3.0 Production
TNS for Solaris: Version 9.2.0.4.0 - Production
NLSRTL Version 9.2.0.4.0 - Production
==================
Pls help me.
July 08, 2004 - 1:27 pm UTC
ask_tom@ASKUS> column oc noprint
ask_tom@ASKUS>
ask_tom@ASKUS> select 1 oc, 'User Name is : ' || user "Database Uptime" from dual union all
2 select 2 oc, 'Database Instance Name is : '||global_name from global_name union all
3 select 3 oc, decode(rownum,1,'Version is : ') || banner from v$version union all
4 select 4 oc, 'Uptime : '||floor(xx) || ' days ' ||
5 floor((xx-floor(xx))*24)||' hours '||
6 round(((xx-floor(xx)*24)-floor((xx-floor(xx)*24)))*60)||' minutes'
7 from (select sysdate-startup_time xx from v$instance)
8 order by 1
9 /
Database Uptime
-----------------------------------------------------------------------------------------------------------------------------------
User Name is : FOOBAR
Database Instance Name is : ASKUS
Version is : Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
PL/SQL Release 10.1.0.2.0 - Production
CORE 10.1.0.2.0 Production
TNS for Linux: Version 10.1.0.2.0 - Production
NLSRTL Version 10.1.0.2.0 - Production
Uptime : 1220 days 0 hours 1 minutes
8 rows selected.
Uptime
A reader, July 08, 2004 - 2:20 pm UTC
Thanks Tom.
Why do you have to put 1 oc,2 oc etc.? any special reason ?.
July 08, 2004 - 3:39 pm UTC
the only way to get data ordered the way you want data ordered is to use order by.
hence, i needed something to order by on.
probably needed an oc2 as well -- rownum, to get the banner data ordered reliably.
converted to find the UPCOMING friday
JayMag, December 03, 2004 - 12:56 pm UTC
thanks for the helpful query.
I used it to find the UPCOMING friday for any date
(in my case sysdate was fine).
rule:
show the upcoming Friday.
If today is Friday, show the next friday
1 select dt,
2 to_char( decode(to_char( dt, 'dy'),
3 'fri', dt+7, next_day(dt,'fri') ), 'Dy, DD-MON-YYYY' )
4* from ( select sysdate+rownum dt from all_objects where rownum < 10 )
sys@qaswb> /
DT TO_CHAR(DECODE(T
----------- ----------------
04-DEC-2004 Fri, 10-DEC-2004
05-DEC-2004 Fri, 10-DEC-2004
06-DEC-2004 Fri, 10-DEC-2004
07-DEC-2004 Fri, 10-DEC-2004
08-DEC-2004 Fri, 10-DEC-2004
09-DEC-2004 Fri, 10-DEC-2004
10-DEC-2004 Fri, 17-DEC-2004
11-DEC-2004 Fri, 17-DEC-2004
12-DEC-2004 Fri, 17-DEC-2004
View
VA, February 16, 2005 - 12:18 pm UTC
How can I put this in view?
create table date_table(the_date date);
--put 10 years worth of dates in this table
create table holidays(h_date date);
-- populate this with holidays
I want a view that looks like
select the_date,prior_business_day from v;
where prior_business_day is the business day (excluding sat/sun and the holidays) prior to the_date in that row.
Help? Thanks
February 16, 2005 - 2:09 pm UTC
can we move the goal posts on this one? instead of putting 10 years worth of dates, how about we put 10 years of BUSINESS dates in there?
do you need to include sat/sun/holidays in this 10 year table?
if not, we can do this super efficient -- hugely efficient.
A reader, February 16, 2005 - 2:20 pm UTC
Well, the date table is used by many processes and is expected to have all the dates regardless of sat/sun/holiday.
Following your suggestions earlier on this page, I ended up doing something like...
Is this the best way to do it?
CREATE OR REPLACE VIEW v
AS
SELECT
d.the_date,
(SELECT MAX(the_date) FROM date_table prev
WHERE prev.the_date<d.the_date
AND to_char(prev.the_date,'dy') NOT IN ('sat','sun')
AND NOT EXISTS (SELECT NULL FROM bessemer_holiday ph
WHERE prev.the_date=ph.date_of_holiday)) prior_business_day
from date_table d
Thanks
February 16, 2005 - 2:50 pm UTC
that would be my next choice, the scalar subquery.
First choice
A reader, February 16, 2005 - 2:53 pm UTC
If we do move the goal posts on it as you suggest, what would your solution be? Thanks
February 16, 2005 - 3:08 pm UTC
I would not have a view, but rather ask people to code:
select *
from (select /*+ FIRST_ROWS */ *
from t
where dt <= :x
order by dt desc) a
where rownum <= 2)
if needed on single row:
select max(decode(r,1,dt)), max(decode(r,2,dt))
from (
select rownum r, a.*
from (select /*+ FIRST_ROWS */ *
from t
where dt <= :x
order by dt desc) a
where rownum <= 2)
)
and if needed in view for real -- pipeline it so you can:
select * from table( get_dates(:x) );
and then bind in the date of interest.
or, if you go after many many rows in this table, use lag()