| Author |
 Topic  |
|
|
sasan.kh
Starting Member
23 Posts |
 Posted - 01/07/2013 : 10:54:19
|
Hi All
I have a table named Table1 with two integer fields: ID, ID2:
ID 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? |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 01/07/2013 : 11:27:28
|
check if definition of view is same in other machine
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/07/2013 : 11:50:55
|
And try recompiling it.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
sasan.kh
Starting Member
23 Posts |
Posted - 01/08/2013 : 08:25:33
|
| the main problem is why 3 'Ok's are returned. There should be just one |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/08/2013 : 09:09:49
|
Could be that the views are diffrent or the data is different.
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
sasan.kh
Starting Member
23 Posts |
Posted - 01/08/2013 : 11:53:32
|
Thanx for your reply...
I am sure about the Data and the View, I can run some queries and post the results if it helps... |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/08/2013 : 12:09:45
|
What does
select * from ViewOfTable1
return on each server
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
sasan.kh
Starting Member
23 Posts |
Posted - 01/08/2013 : 14:03:23
|
on server 1:
Select * from Table1
ID1 ID2
1 null
2 null
3 1
----------------------
SELECT *
FROM dbo.ViewOfTable1
ID1 ID2 Flag
3 1 Ok
----------------------
SELECT *
FROM dbo.Table1 LEFT OUTER JOIN dbo.ViewOfTable1 ON dbo.Table1.ID1 = dbo.ViewOfTable1.ID2
ID1 ID2 ID1 ID2 Flag
1 null 3 1 Ok
2 null null null Ok
3 1 null null Ok
|
Edited by - sasan.kh on 01/08/2013 14:05:07 |
|
|
|
sasan.kh
Starting Member
23 Posts |
Posted - 01/08/2013 : 14:17:53
|
On server 2:
-------------------------
SELECT *
FROM dbo.Table1
ID1 ID2
1 null
2 null
3 1
------------------------
SELECT *
FROM dbo.ViewOfTable1
ID1 ID2 Flag
3 1 Ok
------------------------
SELECT *
FROM dbo.Table1 LEFT OUTER JOIN dbo.ViewOfTable1 ON dbo.Table1.ID1 = dbo.ViewOfTable1.ID2
ID1 ID2 ID1 ID2 Flag
1 null 3 1 Ok
2 null null null null
3 1 null null null
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 01/08/2013 : 23:35:54
|
i think issue is in definition of view ViewOfTable1 . can you post definition from both servers?
you can get it by
sp_helptext 'ViewofTable1'
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
sasan.kh
Starting Member
23 Posts |
Posted - 01/09/2013 : 00:58:57
|
server 1:
CREATE VIEW dbo.ViewOfTable1
AS
SELECT ID1, ID2, 'Ok' AS Flag
FROM dbo.Table1
WHERE (ID2 IS NOT NULL)
server 2:
CREATE VIEW dbo.ViewOfTable1
AS
SELECT ID1, ID2, 'Ok' AS Flag
FROM dbo.Table1
WHERE (ID2 IS NOT NULL)
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
48012 Posts |
Posted - 01/09/2013 : 01:30:42
|
whats ANSI NULL setting on both servers?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/09/2013 : 03:48:54
|
I don't see how you can get a row (null, null, 'ok') from the view.
You might look at the query plans to see if it doing something odd.
Did you recompile the view?
Try this on both servers
create table #a (id int, id2 int)
insert #a select 1, null
insert #a select 2, null
insert #a select 3, 1
select *
from #a a
left join (select *, c='ok' from #a where id2 is not null) b
on a.id = b.id2
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
sasan.kh
Starting Member
23 Posts |
Posted - 01/09/2013 : 07:51:56
|
Your code returned:
1 NULL 3 1 ok
2 NULL NULL NULL NULL
3 1 NULL NULL NULL
as I expected but I can not understand the difference with my query...
quote:
Originally posted by nigelrivett
create table #a (id int, id2 int)
insert #a select 1, null
insert #a select 2, null
insert #a select 3, 1
select *
from #a a
left join (select *, c='ok' from #a where id2 is not null) b
on a.id = b.id2
|
|
|
|
sasan.kh
Starting Member
23 Posts |
Posted - 01/09/2013 : 08:02:53
|
When I change the query to :
select *
from table1 a
left join (select *, c='ok' from table1 where id2 is not null) b
on a.id1 = b.id2
I get :
1 NULL 3 1 ok
2 NULL NULL NULL NULL
3 1 NULL NULL NULL
but after running:
select *
from table1 a
left join viewOfTable1 b
on a.id1 = b.id2
when ViewOfTable1 is exactly equal to :
(select *, c='ok' from table1 where id2 is not null)
the result is :
1 NULL 3 1 ok
2 NULL NULL NULL ok
3 1 NULL NULL ok
|
Edited by - sasan.kh on 01/09/2013 08:05:49 |
|
|
|
sasan.kh
Starting Member
23 Posts |
Posted - 01/09/2013 : 08:09:04
|
quote:
Originally posted by visakh16
whats ANSI NULL setting on both servers?
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
It is OFF in both |
|
|
|
nigelrivett
Flowing Fount of Yak Knowledge
United Kingdom
3328 Posts |
Posted - 01/09/2013 : 09:39:24
|
did you recompile the view?
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
|
|
|
sasan.kh
Starting Member
23 Posts |
Posted - 01/09/2013 : 18:02:04
|
I didn't recompile it...
I will try to recompile and test |
|
|
| |
Topic |
|
Strange result set in left outer join
