Link to home
Start Free TrialLog in
Avatar of raghava_dg
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
Avatar of alpmoon
alpmoon
Flag of Australia image

I am not sure whether I understood your problem, but I think you should consider the problem in a set oriented manner rather than row oriented. If you write the same query as

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 ....
Avatar of raghava_dg
raghava_dg

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.
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?
ASKER CERTIFIED SOLUTION
Avatar of amitpagarwal
amitpagarwal
Flag of India 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