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@xxxxxxxxxx ---------------------------(end of broadcast)--------------------------- TIP 7: don't forget to increase your free space map settings
