Re: I give up! Help needed on SQL

From: Stephane Faroult <sfaroult_at_oriolecorp.com>
Date: Wed, 21 Apr 1999 19:55:37 -0700
Message-ID: <371E8FA9.4CFC@oriolecorp.com>

Paul Davies 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
>

Paul,

Try to replace

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

and 0 > (select datediff('ss', datenow, c.END_DATE) /

                   ((CM_NUM - arti.TOTAL_IMRESSIONS) + ((CLICK_NUM *
clickyield)
                                - arti.TOTAL_IMRESSIONS))
                    - datediff('ss',arti.TIMESTAMP,datenow)
            from A_REQ_TOTAL_IM arti
            where arti.REQ_ID = s.REQ_ID)

it should do the trick (give or take a few missing parentheses) - and by the way be slightly faster than the original code, since three queries on A_REQ_TOTAL_IM are replace by a single one. I know it takes longer, but, as far as deadlines allow it to you, try to get into the Oracle logic rather than trying to keep close to Sybase. On reading your code, I have felt more than once my eye-brows raising and I would not be surprised by performance problems. Try to convince your management (or client) to get more time to do it and to optimize the queries, it will not be a waste of money.

--
Regards,

Stéphane Faroult
Oriole Corporation

http://www.oriolecorp.com, designed by Oracle DBAs for Oracle DBAs

Received on Wed Apr 21 1999 - 21:55:37 CDT