SQL Help

From: Paul Davies <cobalt_at_dircon.co.uk>
Date: 1999/04/21
Message-ID: <371dc0db@newsread3.dircon.co.uk>#1/1

I give up! I'm trying to translate the Transact SQL below to ORACLE SQL. I've indicated the bits which are causing problems with the /*problem*/ flag.

The datediff functions are functions I created (with the help of Thomas Kyte) to mimic the Sybase datediff functions.

As you'll see from the flag, the problems lie with the embedded correlated subqueries.

Any help in rewriting this bit of code would be greatly appreciated.

Paul

INSERT INTO TEMP_SMOOTHREQOK
(REQ_ID,RAN)

select s.REQ_ID,s.RAN from TEMP_SMOOTHREQ s ,CAM where
s.CAM_ID = c.CAM_ID
and
c.VERSION = (select max(VERSION) from CAM i where
i.CAM_ID = c.CAM_ID)
and
(

    (

        (exists (select * from A_REQ_TOTAL_IM
        arti
        where
        arti.REQ_ID = s.REQ_ID)

    )
and
(

    (

        datediff('ss',datenow,c.END_DATE) /
        (
            (CPM_NUM - (select TOTAL_IMRESSIONS  /*problem*/
            from A_REQ_TOTAL_IM arti
            where arti.REQ_ID =
            s.REQ_ID))
        +
        ((CLICK_NUM * clickyield) - (select            /*problem*/
            TOTAL_IMRESSIONS from A_REQ_TOTAL_IM arti
            where arti.REQ_ID =
            s.REQ_ID))

    )
)
<
datediff('ss',(select TIMESTAMP from /*problem*/

        A_REQ_TOTAL_IM arti where
        arti.REQ_ID = s.REQ_ID),datenow)

    )
)
or
(not exists (select * from A_REQ_TOTAL_IM arti

    where

        arti.REQ_ID = s.REQ_ID))
        or ((CONTINUEAFTER = 1) and
        (datediff('dd',END_DATE,datenow) > 0))

); Received on Wed Apr 21 1999 - 00:00:00 CDT