Re: Subselects - recursion problem

Martijn,

On Mon, 2005-05-30 at 21:08, Martijn van Oosterhout wrote:
> On Mon, May 30, 2005 at 08:32:15PM +1000, Philip Rhoades wrote:
> > Martijn,
> >
> >
> > On Mon, 2005-05-30 at 19:50, Martijn van Oosterhout wrote:
> > > "recursion problem" ? It's called a correlated subquery. SQL is
> > > declarative, you state what you want and the database figures out how
> > > to get the answer for you. I think you need to go and read up on the
> > > basics of SQL.
> >
> > Declarative or not, it looks strange having the output of the first
> > select dependent on a second select, which is dependent on the output of
> > the first select . .
>
> Not really, if you think about it. SQL is a form of relational algebra
> and like in normal algebra it's not unusual to have various variables
> relating to eachother in various ways. Some expressions may look
> recursive, but that's just another relationship.

OK.

> As you can see from the query plan, it scans through each record in c1
> filtering out rows based on the easy conditions. It then works out the
> subquery for each row and compares the result with policy. If it works
> the row is returned otherwise it keeps going. There's no recursion.

OK, makes sense in English . .

> Perhaps the easiest way to think about it is having the subquery as a
> function, and write it like:
>
> SELECT c1.loc, c1.lob, c1.policy
> FROM crec AS c1
> WHERE c1.t_type = '1'
> AND c1.t_diss = '2'
> AND c1.recon = 'Y'
> AND c1.policy = Subquery( c1 )
> ORDER BY c1.loc, c1.lob, c1.policy ;
>
> That doesn't look recursive to me, yet it's the same thing...
>
> Hope this helps,

No, that looks just as recursive to me but I get the picture from the
English explanation so thanks again.

Regards,

Phil.
--
Philip Rhoades

Pricom Pty Limited (ACN 003 252 275 ABN 91 003 252 275)
GPO Box 3411
Sydney NSW 2001
Australia
Mobile: +61:0411-185-652
Fax: +61:2:8923-5363
E-mail: phil(at)chu(dot)com(dot)au