Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    26

    Unanswered: 'isNull(Field1,0)' should return 0,but it didn't, WHY?

    --create a temp table like:
    select * into #tmp from
    (select 1 as ID
    union all select 2
    union all select 3
    union all select 4
    ) as A

    -then run this query:
    select B.ID,isNull(B.GID,0) as GID from
    (select #tmp.ID,A.GID
    from
    #tmp
    left outer join
    (select ID,1 as GID from #tmp where ID in (2,4)) A
    on #tmp.ID = A.ID
    ) as B

    --I thougth it should return:
    1 0
    2 1
    3 0
    4 1
    --but it actually returned:
    ID GID
    1 1
    2 1
    3 1
    4 1
    --if I change GID value in '...(select ID,1 as GID from #tmp where ID in (2,4)...', for example 2, then it return:
    ID GID
    1 2
    2 2
    3 2
    4 2

    WHY?
    ......

  2. #2
    Join Date
    Feb 2004
    Posts
    199
    on my SQL server it works as you thougth
    1 0
    2 1
    3 0
    4 1

    maybe you need SP
    MDB, ADP <-> MS SQL + VBA, ADO & RDO, .NET, Oracle, Java/Jsp.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Yep, I get:
    PHP Code:
    ID          GID         
    ----------- ----------- 
              
    1           0 
              2           1 
              3           0 
              4           1 

    (4 row(saffected
    when I run that query too.

    -PatP

  4. #4
    Join Date
    Jan 2004
    Posts
    26
    it's ok after I installed the sp3.

    thanks!
    ......

  5. #5
    Join Date
    Jan 2004
    Posts
    26
    it's ok after I installed the sp3.

    thanks!
    ......

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •