db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English ...@brighton.ac.uk>
Subject Difference between view and select statement
Date Fri, 17 Mar 2006 14:13:32 GMT
I have a table of users, one of which has '' (empty string) as the username.
I issue the following select statement:

     SELECT  NameFormat(surname,initials) AS name,
             users.username AS link,
             CASE WHEN passwords.username IS NULL
                  THEN '' ELSE 'Y' END AS local,
     FROM    users LEFT OUTER JOIN passwords
     ON      users.username=passwords.username
     WHERE   users.username<>'';

This works fine, listing 171 users (all except the one with the empty string
as the username).

Then I try making this a view:

   CREATE VIEW user_list AS [the same select statement as above];

Now when I do "SELECT * FROM user_list" I get 172 results, and this includes
the one with the empty string as its name.

Anyone got any ideas why this should be so? The only way to get the expected
answer is to say SELECT * FROM user_list WHERE link<>'', which seems a bit
perverse to me...

  John English              | mailto:je@brighton.ac.uk
  Senior Lecturer           | http://www.it.bton.ac.uk/staff/je
  School of Computing & MIS | "Those who don't know their history
  University of Brighton    |  are condemned to relive it" (Santayana)

View raw message