Help with view query condition

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Help with view query condition

Author

Topic

uholaza73
Starting Member

5 Posts

Posted - 2010-04-07 : 10:08:49

Hello,

I'm having a big problem getting right result from a query
involving a non-indexed view.

View looks like this:

select T.ID as ID,
ISNULL(TA.Date,T.Date) as ServiceDate,
T.ServiceID
from Expenses T
left join ArrExpenses TA on TA.ExpID = T.ID

When I make a query like :
SELECT vt.ServiceDate,Services.Code
FROM vExpenses vt
left join Services on Services.ID=vt.ServiceID
WHERE vt.ServiceDate = '20100402'

I get results including other dates.
Can someone explain me why is that?

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-04-07 : 11:23:00

I think that since u are using Left join you are getting other records also.

Regards,
Bohra.

I am here to learn from masters and help new bees in learning.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-07 : 13:28:14

whats the datatype of ServiceDate ?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

uholaza73
Starting Member

5 Posts

Posted - 2010-04-07 : 14:39:47

The datatype of ServiceDate is smalldatetime
(from both tables).

If I use whole query instead of this view,
I get the right result, so I don't think
it's about left join
(and this condition is a part of where clause).

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-08 : 01:06:34

quote:
Originally posted by uholaza73

The datatype of ServiceDate is smalldatetime
(from both tables).

If I use whole query instead of this view,
I get the right result, so I don't think
it's about left join
(and this condition is a part of where clause).

Was view changed recently?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

uholaza73
Starting Member

5 Posts

Posted - 2010-04-08 : 01:52:58

quote:
Originally posted by visakh16

Was view changed recently?

No.

If I create a simple query
SELECT * FROM vExpenses WHERE ServiceDate = '20100402'
the results are ok.
But if I join it with other table
execution plan changes
(not using the filter after the view join,
and using it only on ArrExpenses table!!!)

I thought of using a NOEXPAND hint,
but it can't be used on non-indexed views.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-04-08 : 02:12:25

Try:
SELECT vt.ServiceDate,Services.Code
FROM vExpenses vt
left join Services on Services.ID=vt.ServiceID AND vt.ServiceDate = '20100402'

No, you're never too old to Yak'n'Roll if you're too young to die.

uholaza73
Starting Member

5 Posts

Posted - 2010-04-08 : 03:57:53

quote:
Originally posted by webfred

Try:
SELECT vt.ServiceDate,Services.Code
FROM vExpenses vt
left join Services on Services.ID=vt.ServiceID AND vt.ServiceDate = '20100402'

No, you're never too old to Yak'n'Roll if you're too young to die.

Still the same.
I tried using udf instead of ISNULL(TA.Date,T.Date)
in view and that worked ok.

I understand that optimizer could produce a poorer
performance as a result of (not) using indexes,
but here it produces wrong result!

uholaza73
Starting Member

5 Posts

Posted - 2010-04-09 : 02:00:52

Solved it!

I used RTM version of SQL Server 2008.
When I installed SP1, everything worked fine.

Thanks to everyone who tried to help.

If anyone is interested, I created a
script that reproduces the problem.
(records count matters)

CREATE TABLE tJoin (ID int IDENTITY(1,1) PRIMARY KEY ,
	Opis varchar(10) NOT NULL )

CREATE TABLE tMaster (ID int IDENTITY(1,1) PRIMARY KEY,
	JoinID int NOT NULL REFERENCES tJoin(ID),
	Datum smalldatetime NOT NULL )
	
CREATE TABLE tDetail (ID int IDENTITY(1,1) PRIMARY KEY,
	MasterID int NOT NULL REFERENCES tMaster(ID),
	Datum smalldatetime NOT NULL )


CREATE INDEX tDetail_MasterID_tMaster ON tDetail(MasterID)

DECLARE @JoinID int

INSERT INTO tJoin (Opis) 
VALUES ('Test')

SET @JoinID = @@IDENTITY 

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402') 

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402') 

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402') 

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402') 

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402') 

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')
INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402') 

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402') 

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402') 

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402') 

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402') 

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100401',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

INSERT INTO tMaster (Datum,JoinID) 
VALUES ('20100402',@JoinID)
INSERT INTO tDetail (MasterID,Datum) 
VALUES ( @@IDENTITY, '20100402')

SELECT ISNULL(tDetail.Datum,tMaster.Datum), tJoin.Opis
FROM tMaster 
	LEFT JOIN tDetail ON tMaster.ID = tDetail.MasterID
	LEFT JOIN tJoin ON tMaster.JoinID = tJoin.ID
WHERE ISNULL(tDetail.Datum,tMaster.Datum) = '20100402'

DROP TABLE tDetail
DROP TABLE tMaster
DROP TABLE tJoin

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources