Re: I give up! Help needed on SQL

Paul,

It would appear that your problems lie in the aliases used in the subqueries -- you needn't use an alias in the subquery for a table local to that subquery, i.e., when you write this:

             (CPM_NUM - (select TOTAL_IMRESSIONS  /*problem*/
             from A_REQ_TOTAL_IM arti
             where arti.REQ_ID =
             s.REQ_ID))

you only need write this:

             (CPM_NUM - (select TOTAL_IMRESSIONS
             from A_REQ_TOTAL_IM
             where REQ_ID =
             s.REQ_ID))

The subquery is simply that, a sub-query, sort of like a code block in C or C++ where additional variables are defined, so an alias for the table in the subquery is not necessary, however, the alias for the reference value IS necessary. Make those changes to your code and it should function for you.

David Fitzjarrell

In article <371dc490_at_newsread3.dircon.co.uk>,   "Paul Davies" <cobalt_at_dircon.co.uk> 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))
> );
>
>

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Apr 21 1999 - 10:04:11 CDT