Re: joining these two queries

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Wed, 17 Jun 2009 21:35:08 +0200
Message-ID: <4a39456e$0$194$e4fe514c_at_news.xs4all.nl>

mh_at_pixar.com schreef:
> I have these two queries:
>
> -- active, inactive sessions on instance 3
> select status,sid,serial#,inst_id
> from gv$session
> where inst_id=3 and username <> 'SYS';
>
> -- sessions with open transaction on instance 3
> select s.status,s.sid,s.serial#,s.inst_id,t.start_date
> from gv$transaction t, dba_rollback_segs r, gv$session s
> where t.xidusn=r.segment_id and
> s.saddr=t.ses_addr and
> t.inst_id=3 and
> s.inst_id=t.inst_id;
>
> which produce these two results:
>
> STATUS SID SERIAL# INST_ID
> -------- --- ------- -------
> INACTIVE 118 58510 3
> ACTIVE 123 58358 3
> INACTIVE 125 47325 3
>
> STATUS SID SERIAL# INST_ID START_DATE
> -------- --- ---------- ------- -------------------
> INACTIVE 125 47325 3 2009/06/17 00:42:25
>
> But I can't figure out how to join these into a
> single query like this... Can somebody kindly loan me
> a clue?
>
> STATUS SID SERIAL# INST_ID START_DATE
> -------- --- ---------- ------- -------------------
> INACTIVE 118 58510 3 --
> ACTIVE 123 58358 3 2009/06/17 00:42:25
> INACTIVE 125 47325 3 --
>
> Many TIA!
> Mark
>

Looks like you need an outer join on SID and a NVL on START_DATE and I think your start_date in the third query is in the wrong place and should have been in row 3.

Shakespeare Received on Wed Jun 17 2009 - 14:35:08 CDT