Re: SQL Help

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1999/04/21
Message-ID: <371fe84e.14979529@192.86.155.100>

A copy of this was sent to "Paul Davies" <cobalt_at_dircon.co.uk> (if that email address didn't require changing) On Wed, 21 Apr 1999 13:23:35 +0100, you wrote:

>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))
>);
>
>
>

Haven't tested it 100% but I believe the following is equivalent. (made the assumption datenow is Sybase's equivalent of our SYSDATE). I had to punt on the schemas too...
drop table temp_smoothreq;
drop table cam;
drop table a_req_total_id;

create table temp_smoothreq ( cam_id number,

                              req_id number,
                              ran    number );

create table cam ( cam_id number,
                   version number,
                   end_date date,
                   cpm_num number,
                   click_num number,
                   clickyield number,
                   continueafter number );

create table a_req_total_id ( req_id number, total_impressions number, timestamp date );

select s.req_id, s.ran
  from temp_smoothreq s, cam c, a_req_total_id arti

where s.CAM_ID = c.CAM_ID
  and c.VERSION = (select max(VERSION) from CAM i where i.CAM_ID = c.CAM_ID)
  and s.req_id = arti.req_id (+)

  and
(

    (

        ( arti.req_id is not null )
        and
        (
           (
              datediff('ss',sysdate,c.END_DATE) /
              ((CPM_NUM - arti.total_impressions)
              +
              ((CLICK_NUM * clickyield) -  arti.total_impressions)
              )
           )
           <
           datediff('ss', arti.timestamp, sysdate)
        )

    )
    or (arti.req_id is NULL )
    or ((CONTINUEAFTER = 1) and (datediff('dd',c.END_DATE,sysdate) > 0)) );

We outer join to a_req_total_id by arti_req_id (since you always used the same row in your subqueries, this will work). Then, piece by piece we rewrite your where clause:

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

this becomes

   ( arti.req_id is not null )

the outer join did the exists for us already -- if it exists, req_id will not be null, if it does not exist, req_id is null...

> )
>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)
>
> )
>)

the 3 problems become simply: arti.total_impressions/timestamp so that simplifies to:

           (
              datediff('ss',sysdate,c.END_DATE) /
              ((CPM_NUM - arti.total_impressions)
              +
              ((CLICK_NUM * clickyield) -  arti.total_impressions)
              )
           )
           <
           datediff('ss', arti.timestamp, sysdate)
        )

for performance, i would go a step further and code:

           (
              ((c.end_date-sysdate)*24*60*60) /
              ((CPM_NUM - arti.total_impressions)
              +
              ((CLICK_NUM * clickyield) -  arti.total_impressions)
              )
           )
           <
           ((sysdate-arti.timestamp) * 24*60*60)
        )

that is, inline the datediff function call.

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

simplifies to

  ( arti.req_id is null )

> or ((CONTINUEAFTER = 1) and
> (datediff('dd',END_DATE,datenow) > 0))
>);

can stay the same but I would code for performance:

  or ((CONTINUEAFTER = 1) and ( sysdate-c.end_date > 0 ))

to remove the overhead of calling plsql from sql....

>
>
 

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
http://govt.us.oracle.com/    -- downloadable utilities
 
----------------------------------------------------------------------------
Opinions are mine and do not necessarily reflect those of Oracle Corporation

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