Re: Correlated subquery?

From: Susan Zachgo <szachgo_at_austin.rr.com>
Date: Tue, 15 Jun 1999 03:30:22 GMT
Message-ID: <iNj93.2513$x3.415402_at_typhoon.austin.rr.com>

How about something like:

select

    o.logical_name,
    d.vendor,
    d.model,
    d.serial_no,
    o.purch_actual_cost,
    d.location,
    d.type,
    ...

from

    device d,
    ownership o
where

    d.logical_name = o.logical_name
and o.ownership_begin_date = (

        select min(ownership_begin_date)
        from ownership o2
        where o2.logical_name = o.logical_name)
and    o.purch_actual_cost >= 5000

and o.ownership_begin_date <= to_date('06/30/1999', 'dd/mm/yyyy') and o.ownership_end_date is null
order by

   o.logical_name

Jim Wagner wrote in message <7k4f7i$ige$1_at_news.doit.wisc.edu>...
>I have successfully executed a SQL query that has correlated subquery
>in the FROM clase of the statement in MS Access but am having no luck
>doing the same in Oracle 8...is this allowed? Below is the SQL
>statement as executed through Access. If possible, could someone give
>the the Oracle synatx translation? Thanks
>
>SELECT O.LOGICAL_NAME, D.VENDOR, D.MODEL, D.SERIAL_NO_, (SELECT
>O2.PURCH_ACTUAL_COST FROM OWNERSHIP AS O2 WHERE O2.LOGICAL_NAME =
>O.LOGICAL_NAME AND O2.OWNERSHIP_BEGIN_DATE = (SELECT MIN
>(O3.OWNERSHIP_BEGIN_DATE) FROM OWNERSHIP AS O3 WHERE O3.LOGICAL_NAME =
>O2.LOGICAL_NAME)) AS PURCH_ACTUAL_COST, D.LOCATION, D.TYPE, D.ROOM,
>D.MANUFACTURER, D.U_CONTACT_NAME, O.OWNERSHIP_BEGIN_DATE,
>O.CAP_INV_REC_CODE, O.CUST_BILLING_NB, O.RETIRED_DATE,
>O.OWNERSHIP_TRANSFER_PRICE, O.OWNERSHIP_END_DATE
>FROM DEVICE AS D INNER JOIN OWNERSHIP AS O ON D.LOGICAL_NAME =
>O.LOGICAL_NAME
>WHERE PURCH_ACTUAL_COST >= 5000 AND O.OWNERSHIP_BEGIN_DATE <=
>#06/30/1999# AND O.OWNERSHIP_END_DATE IS NULL
>ORDER BY O.LOGICAL_NAME;
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.
>
>
Received on Tue Jun 15 1999 - 05:30:22 CEST