Specifying Null Values - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

Specifying Null Values

Author

Topic

Satiex
Starting Member

7 Posts

Posted - 2007-06-14 : 11:41:04

Hi

I would like to define a view (vwOverdueVideos), consisting of colums Fname, Lname, Title, Phone and Moble who have "overdue" videos (the ReturnDate is blank(null) and the DueDate < todays date.


Create View vwOverdueVideos
AS 
Select Fname, Lname, Title, Phone, Mobile
From Member M, Video V, Loan L, Tape T
Where M.MemberID = L.MemberID
And L.TapeID = T.TapeID
And V.VideoID = T.TapeID
And ReturnDate is Null
And DueDate < GetDate()
Order By Lname

The script runs without any syntax errors, however when I instert a test record;

set dateformat dmy
insert into loan(rentdate, duedate, rentcharge, memberid, tapeid)
values ('12/06/2007', '14/06/2007', 5.00, 100019, 1000901)

and return all rows from the View, it does not return any records.

I think the problem may be the "ReturnDate is Null" statement. I've tried various statements but none return the desired result.

Any help would be appreciated.

Thanks.

Kristen
Test

22859 Posts

Posted - 2007-06-14 : 11:44:55

What's the value for ReturnDate in the row you just added? Maybe it has a default value, and isn't NULL.

Otherwise perhaps some of the other data is missing.

It would be easier to debug if you use JOIN syntax, rather than putting all your Relationships in WHERE.

Kristen

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 11:47:40

[code]
Select Fname, Lname, Title, Phone, Mobile
From Member M, Video V, Loan L, Tape T
Where M.MemberID = L.MemberID
And L.TapeID = T.TapeID
And V.VideoID = T.TapeID
And ReturnDate is Null
And DueDate < GetDate()
Order By Lname
[/code]

Is your view logic correct ?

Kristen
Test

22859 Posts

Posted - 2007-06-14 : 11:55:22

Well, having converted it to use JOINs I read it like this:


Create View vwOverdueVideos
AS 
Select Fname, Lname, Title, Phone, Mobile
       , L.MemberID, L.TapeID        -- Debug only
From Loan AS L
     LEFT OUTER JOIN Member AS M
         ON M.MemberID = L.MemberID
     LEFT OUTER JOIN Tape AS T
         ON T.TapeID = L.TapeID
     LEFT OUTER JOIN Video AS V
          ON V.VideoID = T.TapeID
Where     ReturnDate is Null
      AND DueDate < GetDate()
Order By Lname -- Very bad idea to attempt to have ORDER BY in a View

Try the LEFT OUTER JOINS to see if any of the referential data is missing.


SELECT *
FROM vwOverdueVideos
WHERE     memberid = 100019
      AND tapeid = 1000901

Kristen

Satiex
Starting Member

7 Posts

Posted - 2007-06-14 : 12:05:05

Thanks for the quick replies.

khtan pointed out the error. That's quite a silly mistake I made, I should have double checked that before looking so far into it.

I'm now the message:

quote:
Server: Msg 1033, Level 15, State 1, Procedure vwOverdueVideos, Line 10
The ORDER BY clause is invalid in views, inline functions, derived tables, and subqueries, unless TOP is also specified.

I'd like the view to display Lname in alphabetical order. Is there any way to achieve this without using 'TOP'? (I realise this can be acomplished by specifying the Order By in a Select query).

Thanks.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 12:10:23

stick to the INNER JOIN format for you view and remove the ORDER BY statement

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-06-14 : 12:12:09

K-bot. Bad bot bad bad bot.

Satiex
Starting Member

7 Posts

Posted - 2007-06-14 : 12:22:26

Is the way I'm creating the view using the INNER JOIN format? I removed the ORDER BY statement and returned all rows from the view, however there is no alphabetical order.

Kristen
Test

22859 Posts

Posted - 2007-06-14 : 14:19:26

"returned all rows from the view, however there is no alphabetical order"

There isn't supposed to be any implied order in a VIEW. A VIEW is equivalent to a table, and that doesn't have any implied order either.

Use:

SELECT Col1, Col2, ...
FROM MyView
ORDER BY ColX, ColY, ...

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources