raghava_dg
asked on
using @@rowcount inside subquery
Hi ,
I am faceing a prob with subquery .
Ex:
select * from table1 where col1 in (select col1 from table2)
Here my subquery "select col1 from table2" will return more than one row .So i need to use " IN " clause .
Suppose the result of sub query is zero i,e @@rowcount=0
then i should be able to give a default value to the field col1.
I don't want to use CURSOR since the result set is very large which may affect my performance of proc.
My question is can i use @@rowcount inside a subquery .
or is there any way to solve above situation.
Any suggetions will be appreciated.
Thanks in advance
raghav
I am faceing a prob with subquery .
Ex:
select * from table1 where col1 in (select col1 from table2)
Here my subquery "select col1 from table2" will return more than one row .So i need to use " IN " clause .
Suppose the result of sub query is zero i,e @@rowcount=0
then i should be able to give a default value to the field col1.
I don't want to use CURSOR since the result set is very large which may affect my performance of proc.
My question is can i use @@rowcount inside a subquery .
or is there any way to solve above situation.
Any suggetions will be appreciated.
Thanks in advance
raghav
ASKER
Thanks alpmoon for the comment.
Yes i can use joins ..but my problem is as i said in the question ,suppose my subquery does not returns any row then i need to pass a default value to the outer query .
Actually here col1 itself is not NULL , entire subquery returns NULL ,in other words it does not return any row . At that time i need to give default value.How can it be done.
Yes i can use joins ..but my problem is as i said in the question ,suppose my subquery does not returns any row then i need to pass a default value to the outer query .
Actually here col1 itself is not NULL , entire subquery returns NULL ,in other words it does not return any row . At that time i need to give default value.How can it be done.
I am not absolutely sure, but you should try "case":
select distinct table1.col2, table1.col3, ...,
case when table2.col1 is null then "Your default value"
else table1.col1
end
from table1, table2
where table1.col1 *= table2.col1
I think table2.col1 would be treated as null when there is no corresponding row in table2. Or do you need to eliminate some of the rows in table1?
select distinct table1.col2, table1.col3, ...,
case when table2.col1 is null then "Your default value"
else table1.col1
end
from table1, table2
where table1.col1 *= table2.col1
I think table2.col1 would be treated as null when there is no corresponding row in table2. Or do you need to eliminate some of the rows in table1?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
select distinct table1.* from table1, table2
where table1.col1 = table2.col1
or with outer join if you like to include all rows in table1:
select distinct table1.* from table1, table2
where table1.col1 *= table2.col1
you would have enough flexibility to play with result set. If you need to modify the value of col1 in result set depending on table2.col1 you can use "case" like:
select ...,
case when table2.col1 is null then "XXX"
else table1.col1
end
from ....