Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Problem with date query - simple?

Re: Problem with date query - simple?

From: DA Morgan <damorgan_at_exesolutions.com>
Date: Sat, 08 Feb 2003 22:32:55 -0800
Message-ID: <3E45F617.E1C45EC2@exesolutions.com>


"Don A." wrote:

> Bertram,
>
> We currently have several local and remote databases that are being used by
> internal and external clients / customers and I am recording (among other
> things) on-going connection information (what programs, machines. userids, etc)
> in a central database.
>
> I already use these tables for current and historic reporting and they've worked
> well until now. Management now wants the ability to query the data directly and
> get information on clients connected between 6 PM and 6 AM. They felt they
> needed at least 4 hours of current data for their validity checking (hence the 6
> PM to 10 PM requirement below), or else they wanted to see the previous nights
> information (which I was already reporting to them). So if management queries
> the database anytime after 10 PM they want to see current data. All I can say is
> GOD I hate management.
>
> Anyway, due to other internal and external politics, the best way seemed to be
> creating a view and have it return the appropriate data. Because I fully expect
> them to change or expand these requirements, I didn't want to start creating new
> tables just to provide this information (I also thought it wouldn't be difficult
> to create a select statement to do this ;-)
>
> I created a decode(sign(to_char(trunc... statement and it seems to work (I
> haven't done any further verification after it ran the first time), but boy is
> it ugly. I may just look at populating another table and getting the data that
> way, not really sure at this point.
>
> Time to go have another pint or two, then I'll come up with something do to
> management . Oops, I meant for...
>
> Cheers,
>
> Don.
>
> On Sat, 08 Feb 2003 21:27:05 GMT, "Bert Bear" <bertbear_at_NOSPAMbertbear.net>
> wrote:
>
> >Don,
> >
> >So, what you are saying is:
> >
> >Until today's data is completely collected (e.g. after 10 PM) show me
> >yesterday's data (between times X and Y). Once today's data is complete,
> >start collecting for tomorrow and show me data collected today.
> >
> >My first thought is you could look at using two tables:
> >
> >1) Today's data -- set up for quick collection (e.g. OLTAP like).
> >
> >2) Previous data -- set up for data mining / warehousing.
> >
> >During day-end close, you would merge the today's data into the previous
> >data and then truncate today's data. (If you are running a 24x7 OLTAP
> >envrionment, it isn't this simple. For some reason I get the feeling you're
> >doing a POS or at least where business stops and you can do end-of-day
> >processing in a quiet mode.)
> >
> >This "design" would give you several performance options and might make your
> >SQL select / report easier. Using this method, you only need to look in
> >previous data. The decision on "nearest current time/otherwise grab the
> >data from tonight" is NOT in your select, but in when the merge process
> >occurs.
> >
> >Are these thought going down the same path you are thinking?
> >
> >Bertram Moshier
> >Oracle Certified Professional 8i and 9i DBA
> >
> >http://www.bmoshier.net/bertram
> >
> >
> >
> >"Don A." <DBA_at_direct.ca> wrote in message
> >news:ie8a4vgrucuoa8babcs8jrkqqc9177t8vk_at_4ax.com...
> >> Greetings,
> >>
> >> I'm trying to select records from a table based on a date field and can't
> >seem
> >> to figure out how to set up variable date checking. This select statement
> >is in
> >> a view so it needs to be able to determine the current time and then
> >request
> >> either today's data or yesterday's. (All of this is on 8.1.7.2.5 under
> >W2K)
> >>
> >> Basically I want all records between a certain time (say 6 PM and 10 PM)
> >from
> >> the nearest current time.
> >>
> >> That is, if it's currently before 10 PM I want the data from last night,
> >> otherwise grab the data from tonight.
> >>
> >> I can put in a set date and time manually :
> >>
> >> i.e. ... where RDATE between ('08-FEB-03 18:00:00','DD-MM-YY hh24:mi:ss')
> >> and ('08-FEB-03 22:00:00','DD-MM-YY hh24:mi:ss')
> >>
> >> But obviously it means the select statement is static. How can I check the
> >> current time and then pass this to the where clause so it requests the
> >> appropriate days data?
> >>
> >> Any help, pointers, suggestions appreciated.
> >>
> >> Thanks,
> >>
> >> Don
> >
> >

Presumably you are using an after logon trigger. Why all the convoluted SQL? All of this information is readily available to the trigger using SYS_CONTEXT.

Daniel Morgan Received on Sun Feb 09 2003 - 00:32:55 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US