Post reply

select isnull

  • January 21, 2003 at 2:47 pm

    #80185

    Hi everybody!

    I have a join between 2 tables and one of the field is formed with the isnull function, like this

    select 1.a, 1.b, 2.c, isnull(2.d,1.a) as IssueFiled from 1 left outer join 2 on 1.b = 2.c

    where IssueField = 'AA'

    and the return is empty

    If I run

    select 1.a, 1.b, 2.c, isnull(2.d,1.a) as IssueFiled from 1 left outer join 2 on 1.b = 2.c

    I can see the issuefield with the value 'AA'

    Why is the first statement not returning data? It has something to do with isnull (I think) function, and how can I solve the problem?

    Thanks a lot,

    Durug

  • January 21, 2003 at 2:55 pm

    #446912

    DOes IssueField exist in one of the tables already (1 or 2). If so then you are doing the WHERE condition on that field not your resulting field. Instead you have to do as a subquery or better as

    WHERE 2.d = 'AA' or (2.d IS NULL AND 1.a = 'AA')

    You could also try

    WHERE ISNULL(2.d,1.a) = 'AA'

    but I believe there is an issue with index usage.

  • January 21, 2003 at 4:06 pm

    #446919

    Thanks a lot,

    You are great.

    I had to use the condition isnull(...)

    Durug

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply