Re: Oracle-Advanced SQL-question

From: Andreas Mosmann <keineemails_at_gmx.de>
Date: Mon, 06 Jun 2005 20:14:09 +0200
Message-ID: <1118081649.58@user.newsoffice.de>

Andreas Mosmann schrieb am 06.06.2005 in
<1118062535.89_at_user.newsoffice.de>:

Thanks to both of you.

I now use the Query that way AK developed. (I understand it but I still m problems to build queries where single columns are result of a select statement that refers a table from the outer query). The complete query (names adapted to the exact situation) at the end of the text.

The performance doesn't matter because it is only a little tool for developement of the application. If it would take 10 Minutes to get the answer it would be enough. Exactly now there are given back 6 rows in 0,04 secs - very fast.

Andreas

<SQL>

select
  E3.CIDUSER,
  E3.CERRORTIME LOGIN_TIME,
  ( select

      min(CERRORTIME)
    from
      BAUMPRG.TBERRORLOG E1
    where

      E1.CDEVELOPERTEXT ='Abmeldung erfolgt' and
      E1.CIDUSER = E3.CIDUSER and
      E1.CERRORTIME > E3.CERRORTIME and
      not exists
      ( select
          *
        from
          BAUMPRG.TBERRORLOG E2
        where
          E1.CIDUSER = E2.CIDUSER and
          E3.CERRORTIME < E2.CERRORTIME and
          E2.CERRORTIME < E1.CERRORTIME
      )

  ) logout_time,
  round((( select

      min(CERRORTIME)
    from
      BAUMPRG.TBERRORLOG E1
    where

      E1.CDEVELOPERTEXT ='Abmeldung erfolgt' and
      E1.CIDUSER = E3.CIDUSER and
      E1.CERRORTIME > E3.CERRORTIME and
      not exists
      ( select
          *
        from
          BAUMPRG.TBERRORLOG E2
        where
          E1.CIDUSER = E2.CIDUSER and
          E3.CERRORTIME < e2.CERRORTIME and
          E2.CERRORTIME < E1.CERRORTIME
      )

  )-E3.CERRORTIME)*24*60) MINUTES_BETWEEN from
  BAUMPRG.TBERRORLOG E3
where
  E3.CDEVELOPERTEXT='Anmeldung erfolgt'
</SQL>

-- 
wenn email, dann AndreasMosmann <bei> web <punkt> de

Received on Mon Jun 06 2005 - 13:14:09 CDT