Link to home
Start Free TrialLog in
Avatar of AbhiJeet
AbhiJeet

asked on

oracle query

I have a view eg view1 and two tables table1, table2

I need an query which will fetch records from view1 based on a join condition between table1 and table2

The query will something like below,

select v.col1, v.col2, v.col3 , ( select t2.is_active from t1, t2 where t1.id = t2.id and t1.id = v.id) active
from view1 v where active = 'Y'

But this gives invalid identifier error as it doesn't find active column which is an alias.

So what is the other way of writing the query?
Avatar of David VanZandt
David VanZandt
Flag of United States of America image

It looks like you're trying to select column is active of table t2 from both tables T1 and T2. In other words you're missing a select criteria from T2. i
Secondly, if you're trying to write a sub-query to get the value of  is active, your syntax is wrong and you should re check your source.
Avatar of AbhiJeet
AbhiJeet

ASKER

Yes it is wrong and that is why it is giving me the error, i will need to know how i can rewrite the same
Try this. Enjoy !!

SELECT is_active, f.col1, f.col2, f.col3,f.Id
FROM
(
	SELECT t2.is_active , t1.Id FROM t1 INNER JOIN t2 ON t1.id = t2.id 
)e
INNER JOIN
(
	select v.col1, v.col2, v.col3,v.Id
	from view1 v where active = 'Y'
)f
ON e.Id = v.Id

Open in new window

there is not active column in the view actually, i need to take that from the table t2.is_active based on the join condition of t1 and t2.
Yes the above considers your point. try that it will work. Otherwise please let me know !!
i am not sure how below will work when there is not active column in view, kindly help me to get this clear

select v.col1, v.col2, v.col3,v.Id
      from view1 v where active = 'Y'
is the below appropriate:

select
  v.col1, v.col2, v.col3
from
  view1 v, ( select t2.is_active active from t1, t2 where t1.id = t2.id ) v1
where v.id = v1.id
   and active  = 'Y'
Ohh okie , Updated code


SELECT is_active, f.col1, f.col2, f.col3,f.Id
FROM
(
	SELECT t2.is_active , t1.Id FROM t1 INNER JOIN t2 ON t1.id = t2.id 
)e
INNER JOIN
(
	select v.col1, v.col2, v.col3,v.Id
	from view1 v where e.is_active = 'Y'
)f
ON e.Id = v.Id

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Qlemo
Qlemo
Flag of Germany image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Why using t1 when ID column is available in t2


Another approach is

select v.col1, v.col2, v.col3is
from view1 v where id in
(  select t2.is_active from t1, t2 where t1.id = t2.id and t1.id = v.id and t2.is_active='Y')

Or

Select * from (
select v.col1, v.col2, v.col3 , ( select t2.is_active from t1, t2 where t1.id = t2.id and t1.id = v.id) active
from view1 v
 ) where active = 'Y'
The first one will not work, because ID is compared with is_active.
Further you use correlated subqueries only if no other choice is available. Same applies for a subselect in the select columns.
How about this simple query with a sub-query:
select t1.col1, t1.col2, t1.col3 , 'Y'
from t1
where exists (select 1 from t2 where t2.id = t1.id
      and t2.is_active = 'Y');

Or this join:
select t1.col1, t1.col2, t1.col3 , t2.is_active Active
from t1, t2
where t2.id = t1.id
 and t2.is_active = 'Y';

I recommend trying both of these with your data, your Oracle version and your hardware.  One of these versions may work faster than the other.
markgeer, you lost the view, which I suppose to have important data ...
Qlemo you are right on both your points

The best option will be

select v.col1, v.col2, v.col3is
from view1 v join t2 on v.id=t2.I'd and t2.is_active='Y'

Or


select v.col1, v.col2, v.col3is
from view1 v jon t2 on t2.id=v.Id join t2 on t1.id=t2.I'd and t2.is_active='Y'

In case  you want to avoid cases where I'd is missing in table t1


I just wanted to share the approaches as in some cases using sub queries executes faster


Below is the corrected query I shared before
select v.col1, v.col2, v.col3is
from view1 v where id in
(  select t2.id from t1, t2 where t1.id = t2.id and t1.id = v.id and t2.is_active='Y')