Howto embed select statements into other select statements

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

.

Relevant Pages

  • Re: 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)