Re: Howto embed select statements into other select statements

On May 7, 6:40 am, Niels Dybdahl <niels.dybd...@xxxxxxxxx> wrote:
Hi,

I want to calculate the fraction of two counts and I have created the
following select statement:

select
field1,
field2,
(select table1.co/table2.co
from (select count(1) co, id from t_table1 where seal>=1000 group
by id) table1,
(select count(1) co, id from t_table2 group by id) table2
where table1.id=j.id and table2.id=j.id),
field3
from t_table3 j

This works as long as there are corresponding entries in t_table1 and
t_table2, otherwise the fraction becomes null, which might make sense
when there is nothing in t_table2 but not when t_table1 has no
matching entries.

Instead I have tried:

select
field1,
field2,
(select table1.co/table2.co
from (select count(1) co, id from t_table1 where seal>=1000 and
id=j.id) table1,
(select count(1) co, id from t_table2 where id=j.id) table2),
field3
from t_table3 j

But then the database (Oracle 10) complains that it can not find j.id.
How can I write this select correctly?

Best regards
Niels Dybdahl


Why make your statement so complicated?
Make an inline view. (basically move the subquery to the FROM clause.)
select
field1,
field2,
ratio,
field3
from t_table3 j,
(select table1.co/table2.co ratio
from (select count(1) co, id from t_table1 where seal>=1000 group
by id) table1,
(select count(1) co, id from t_table2 group by id) table2
where table1.id=table2.id) ratioview
where ratioview.id=j.id ;

For dealing with the NULL conditions, consider the NVL() function and
possibly an outer join between table1 and table2.

HTH,
Ed

PS ( for ORACLE specific questions like this, you should consider
posting to one of the oracle newsgroups. For SQL questions like this,
use comp.databases.oracle.server)

.

Relevant Pages

  • Howto embed select statements into other select statements
    ... I want to calculate the fraction of two counts and I have created the ... by id) table1, ...
    (comp.databases)
  • Re: update query
    ... will match any string which starts with a and ends with c. ... Probably because you have an extra blank between table1. ... Is there in fact a field named Field2 in your query? ... SET Field1 = Field2 ...
    (microsoft.public.access.queries)
  • Data type of a tuple id in r3?
    ... delete from table1 ... The data in field1 and field2 are ... How I finally figured out I needed to cast the tid was when I did a ...
    (comp.databases.ingres)
  • RE: combine data from 2 tables in access
    ... Source Table: Table1 ... Fields: Fax, Field1, Field2 ... Source Table: Table2 ...
    (microsoft.public.access.modulesdaovba)
  • Re: How to find Minimum of four fields
    ... SELECT Id, Field1, Field2, "PhoneCost" AS CostType, PhoneCost AS CostAmount ...
    (microsoft.public.access.gettingstarted)