Case in a join

[munchen]

Is there a way to something like this in a join?

case
when a.refno is null then b.refno > tmp.refno
else
a.refno > b.refno

I need this to be in the join. Can this be done?

Thanks in advance

 

[PWise]

how about
inner join tablename a
on isnull(arefno,tmp.refno)>b.refno

 

[PWise]

sorry SB

inner join tablename a
on isnull(a.refno,tmp.refno)>b.refno

 

[munchen]

pwise

thanks for your response. that's not what i'm after as:

case
when a.refno is null then b.refno > tmp.refno
else
a.refno > b.refno

If a.refno is null then I want b.refno to be greater than tmp.refno. If a.refno is not null then I want a.refno to be greater than b.refno whereas the code you supplied ALWAYS has either a.refno or tmp.refno to be greater than b.refno.

Does that make sense?

 

[PWise]

Try

Case when a.refno is null then b.refno else a.refno end 
>
Case when a.refno is null then tmp.refno else b.refno end [code]