Re: A little complicated SQL

akizub_at_yahoo.com (Alex Kizub) wrote in message news:<9d4ba33b.0207180439.3efe1f1c_at_posting.google.com>...

Can't use PL/SQL, and you don't say what DB version. I'll assume Oracle 8.x and that you need something that returns a result set. (This happened to me recently)

Maybe nested subqueries, along the lines of this:

select
distinct
q1.a, q1.b, q2.c
from
(select a,b from t1) q1,
(select x1.a, x1.b, x1.C from X1,X2
 where x1.a||x2.b in (select a||b from t1)  and X1.D=X2.D) q2
where q2.a (+) = q1.a

> I want to improve perfomance of my application but database structure
> is not good.
>
> Here is problemm:
>
> One SQL is:
> select A,B from T1;
>
> for each row I have to create another query
> select C from X1,X2 where X1.A=A and X2.B=B and X1.D=X2.D;
>
> X1.A can be absent, X2.B can be absent and relation X1.D=X2.D can be
> absent.
>
> So I can have something or nothing.
>
> So right now I have rowset like this
>
> A B Query C
> -------------------------------------------------------------------------------------------------
> A1 B1 select C from X1,X2 where X1.A='A1' and X2.B='B1' and
> X1.D=X2.D C1
> A2 B2 select C from X1,X2 where X1.A='A2' and X2.B='B2' and
> X1.D=X2.D nothing
> A3 B3 select C from X1,X2 where X1.A='A3' and X2.B='B3' and
> X1.D=X2.D C3
>
> Can I create query which returns for me
>
> A B C
> ------------------------------
> A1 B1 C1
> A2 B2 null
> A3 B3 C3
>
> in one select statement?
> And I can't use PL/SQL in our database :(
>
> Thanks is advance.
>
> Alex Kizub.
Received on Thu Jul 18 2002 - 16:36:33 CDT