Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Substitutes for subqueries (subselects)?
Thanks, Jan,
But I meant to ask another thing... The question is not if there's a tool that converts my type 1 queries into type 2. I'd like to know (if someone can help me) if there's another way to achieve the desired output. That requires minos adjustments of the type 1 queries only. I read somewhere that there's always many ways to achieve the same thing in SQL :))
Cheers,
Kurta
"Jan Gelbrich" <j_gelbrich_at_westfalen-blatt.de> wrote in message news:<c24vad$1ogcsv$1_at_ID-93924.news.uni-berlin.de>...
> "Kurta" <submit_at_galleus.com> schrieb im Newsbeitrag
> news:efcb1994.0403020208.33ed38f_at_posting.google.com...
> > I have a SELECT query that uses subselects:
> >
> > SELECT
> > SAV_ID
> > FROM
> > T_SVGS
> > WHERE
> > (SAV_MEMB = 66470
> > OR
> > SAV_CM_BASE IN (SELECT CMBS_ID FROM T_CM_BASEBASE AA WHERE CMBS_MEMB
> > = 66470)
> > OR
> > SAV_UM_BASE IN (SELECT UMBS_ID FROM T_UM_BASE BB WHERE UMBS_MEMB =
> > 66470))
> >
> > I'd have to return additional columns from the tables of the
> > subqueries. I can achieve a desired result with joins and unions like
> > this:
> >
> > SELECT
> > SAV_ID, SAV_MEMB, NULL CLASSTYPE
> > FROM
> > T_SVGS
> > WHERE
> > SAV_MEMB = 66470
> >
> > UNION
> >
> > SELECT
> > SAV_ID, SAV_CM_BASE, CMBS.CLASSTYPE
> > FROM
> > T_SVGS, T_CM_BASEBASE CMBS
> > WHERE CMBS_ID = SAV_CM_BASE AND CMBS_MEMB = 66470
> >
> > UNION
> >
> > SELECT
> > SAV_ID, SAV_UM_BASE, UMBS.CLASSTYPE
> > FROM
> > T_SVGS, T_UM_BASE UMBS
> > WHERE UMBS_ID = SAV_UM_BASE AND UMBS_MEMB = 66470
> >
> > Is there a less harmful way to get the same results without having to
> > rewrite all the queries?
> >
> > Thanks for your help.
> >
> > Kurta
>
>
> Kurta, I fear the answer is no, there is no nice tool for this.
>
> That would be a really nice tool, that could rewrite all concerning selects
> correctly
> with *zero* knowledge of the business rules behind them
> (and in the real world many lack to use fk constraints and so on ...)
>
> On the other hand, it could
> actually make some SQL "artists" loosing their jobs ... maybe it will come
> with programming languages of the 10th generation in 2020, who knows ...
>
> So far, good SQL remains an art. Few are real artists, many arenīt.
>
> Not much help, sorry, but this is how I see it.
> Greetings, Jan
Received on Thu Mar 04 2004 - 03:50:48 CST