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?
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?
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.
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
ASKER
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 !!
ASKER
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'
select v.col1, v.col2, v.col3,v.Id
from view1 v where active = 'Y'
ASKER
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'
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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'
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.
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.
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')
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')