Re: query trouble

>Hi all!
> I've got a table that maintains a list of logins (I'm with an ISP), and
>the relevant data. what I'm trying to do is search that table for people
>with dual simultaneous connections. I can't seem to get it to work, and I'm
>at a loss as to why. any input would be greatly appreciated. Please email
>replies to wyma69_at_nidlink.com .
>
>What I've got is this:
>PROCEDURE GET_MULTS IS
>
>cursor names is
>select UNAME, LONG_DATE, OUT_DATE
>from MAIN.RADIUS_LOGS
>where (TO_DATE(LONG_DATE, 'dy mon dd HH24:MI:SS YYYY')) between (SYSDATE-1)
>and SYSDATE
>order by (TO_DATE(LONG_DATE, 'dy mon dd HH24:MI:SS YYYY'));
>
>BEGIN
>
>for dual_log in names loop
>
>SELECT UNAME, LONG_DATE, OUT_DATE, SESS_LENGTH, CALLED_FROM
>into :UNAME, :LONG_DATE, :OUT_DATE, :SESS_LENGTH, :CALLED_FROM
>from MAIN.RADIUS_LOGS
>where UNAME=dual_log.UNAME
>and (TO_DATE(LONG_DATE, 'dy mon dd HH24:MI:SS YYYY')) between (SYSDATE-1)
>and SYSDATE
>and (TO_DATE(LONG_DATE, 'dy mon dd HH24:MI:SS YYYY'))
>between (TO_DATE(dual_log.LONG_DATE, 'dy mon dd HH24:MI:SS YYYY')) and
>(TO_DATE(dual_log.OUT_DATE, 'dy mon dd HH24:MI:SS YYYY'));
>
>
>end loop;
>
>END;
>

Presumably, session #1 somehow creates an entry in your log file. If it does not do a commit, the 2nd session will not see the entry. Could this be the problem?

Also, if they are still logged in, wouldn't the OUT_DATE be null? What about if the connection was dropped without the OUT_DATE begin filled? To get around this you might be able to try nvl(dual_log.out_date, sysdate)

Incidently, Oracle does date comparions properly; you do not need to use the TO_DATE functions unless the dates are stored in the DB as character strings.

HTH
Dan Hekimian-Williams Received on Fri May 07 1999 - 06:30:48 CEST