0

I have a table named Table1 with two integer fields:

ID1  ID2
1    NULL
2    NULL
3    1

I have a view of this table named ViewOfTable1:

SELECT ID1, ID2, 'Ok' AS Flag
FROM Table1
WHERE ID2 IS NOT NULL

I run the following query:

SELECT Table1.ID, ViewOfTable1.Flag
FROM Table1
LEFT OUTER JOIN ViewOfTable1 ON Table1.ID = ViewOfTable1.ID2

I expect the result to be :

ID    Flag
1     Ok
2     NULL
3     NULL

And this is what I get on my machine with SQL Server Desktop Edition installed, but when I run this query in another machine with SQL Server 2000 Enterprise Edition SP4 I get:

ID    Flag
1     Ok
2     OK
3     OK

What could be the problem?

ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
ssnkh
  • 109
  • 7
  • 5
    Double check that the data is the same. – Namphibian Jan 07 '13 at 09:16
  • Where did 'ID3' come from? There is no mention in your code of a column being renamed to this. This looks either your data is different (like @Namphibian said) or your queries/view are/is different. – Kevin Brydon Jan 07 '13 at 09:34
  • I can send you images of the different results – ssnkh Jan 07 '13 at 09:36
  • I tested this on another machine and there are 'Ok's returned for all rows... – ssnkh Jan 07 '13 at 14:36
  • Check your view definition is the same. Select * from view on both servers and compare results. – Bohemian Jan 07 '13 at 21:46
  • I double checked my work and found nothing different but the main problem is why 'Ok' is returned in all rows. there is just one row in the View but 3 'Ok's are returned – ssnkh Jan 08 '13 at 05:49
  • What SP is the machine running DE on? – SchmitzIT Oct 22 '13 at 19:23
  • Do you still have that problem or did you solve it? – Angelo Fuchs Oct 23 '13 at 07:37
  • Are you 100% sure that the view definition is the same in both instances? – ypercubeᵀᴹ Oct 23 '13 at 13:37
  • It could be a bug though that has been fixed in recent version. Version 2000, well, is out of support I think: http://support.microsoft.com/lifecycle/search/?sort=PN&alpha=SQL – ypercubeᵀᴹ Oct 23 '13 at 13:42
  • This bug seems similar: [Incorrect query results with Enterprise edition indexed view substitution and NULL usage.](http://connect.microsoft.com/SQLServer/feedback/details/471201/incorrect-query-results-with-enterprise-edition-indexed-view-substitution-and-null-usage) – ypercubeᵀᴹ Oct 23 '13 at 13:44

0 Answers0