Subqueries in a Select

[Karthik]

Hello everyone

I am facing a problem with using sub queries in a select statement. I am
running this on Oracle 8.0.5...

I have this table table_a and I some 4-5 fields that are foreign keys with
a table table1 and another 4-5 fields that are foreign keys with a table
table2..

am doing this select on table_a and am selecting a bunch of fields..

select a.1, a.2, a.3.......etc...now I have to translate these fk fields
to their description..

select a.1, a.2, a.3, (select tbl1.name from tbl1 where tbl1.id = a.4), (select
tbl1.name from tbl1 where tbl1.id = a.5), (select tbl1.name from tbl1 where
tbl1.id a.6)....., (select tbl2.name from tbl2 where tbl2.id = a.9), (select
tbl2.name from tbl2 where tbl2.id = a.10).....from table_a, table_x, table_y,
table_z where blah blah blah...

I have a similar query in SQL Server that works. I didnt think writing sql
statements in Oracle was quite different from SQL Server. any suggestions
on this ???

i dont want to use 4 aliases for table_1 and table_2...

Thanks for all the help in advance..

Regards
Karthik

[Simon Sellick]

"Karthik" <kemmaneni@apshealthcare.com> wrote:
>
>Hello everyone
>
>I am facing a problem with using sub queries in a select statement. I am
>running this on Oracle 8.0.5...
>
>I have this table table_a and I some 4-5 fields that are foreign keys with
>a table table1 and another 4-5 fields that are foreign keys with a table
>table2..
>
>am doing this select on table_a and am selecting a bunch of fields..
>
>select a.1, a.2, a.3.......etc...now I have to translate these fk fields
>to their description..
>
>select a.1, a.2, a.3, (select tbl1.name from tbl1 where tbl1.id = a.4),
(select
>tbl1.name from tbl1 where tbl1.id = a.5), (select tbl1.name from tbl1 where
>tbl1.id a.6)....., (select tbl2.name from tbl2 where tbl2.id = a.9), (select
>tbl2.name from tbl2 where tbl2.id = a.10).....from table_a, table_x, table_y,
>table_z where blah blah blah...
>
>I have a similar query in SQL Server that works. I didnt think writing sql
>statements in Oracle was quite different from SQL Server. any suggestions
>on this ???
>
>i dont want to use 4 aliases for table_1 and table_2...
>
>Thanks for all the help in advance..
>
>Regards
>Karthik

Karthik,
I think aliases are the ANSI way to join to multiple rows of the same table:

SELECT
t.field,
a1.name,
a2.name ...
FROM
t,
a AS a1,
a AS a2
WHERE
a1.code = t.code1
AND
a2.code = t.code2

But your suggestion that Oracle and SQL Server share the same query syntax
is incorrect - neither of them follows the ANSI standard beyond the form
of trivial queries.

[Qbert]

"Karthik" <kemmaneni@apshealthcare.com> wrote:
>
>Hello everyone
>
>I am facing a problem with using sub queries in a select statement. I am
>running this on Oracle 8.0.5...
>
>I have this table table_a and I some 4-5 fields that are foreign keys with
>a table table1 and another 4-5 fields that are foreign keys with a table
>table2..
>
>am doing this select on table_a and am selecting a bunch of fields..
>
>select a.1, a.2, a.3.......etc...now I have to translate these fk fields
>to their description..
>
>select a.1, a.2, a.3, (select tbl1.name from tbl1 where tbl1.id = a.4),
(select
>tbl1.name from tbl1 where tbl1.id = a.5), (select tbl1.name from tbl1 where
>tbl1.id a.6)....., (select tbl2.name from tbl2 where tbl2.id = a.9), (select
>tbl2.name from tbl2 where tbl2.id = a.10).....from table_a, table_x, table_y,
>table_z where blah blah blah...
>
>I have a similar query in SQL Server that works. I didnt think writing sql
>statements in Oracle was quite different from SQL Server. any suggestions
>on this ???
>
>i dont want to use 4 aliases for table_1 and table_2...
>
>Thanks for all the help in advance..
>
>Regards
>Karthik

Umm... Have you considered using a normal join?
you know.
Select a1, a2, a3, tbl1.name
From t1, t2, t3
where a.a4 = tbl1.id

Keep in mind you may have to peform some outer joins on these tables.
which you should be able to accomplish by using (+) operand. after where
like where a.a4 = tbl1.id(+)

In addition the method of doing in statements is increadibly slow when compared
to using joins.