Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: neutral (asf.osuosl.org: local policy)
Message-ID: <441AC40C.5000808@brighton.ac.uk>
Date: Fri, 17 Mar 2006 14:13:32 +0000
From: John English <je@brighton.ac.uk>
Organization: University of Brighton
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US;
 rv:1.7.8) Gecko/20050511
MIME-Version: 1.0
To: Derby Discussion <derby-user@db.apache.org>
Subject: Difference between view and select statement
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit

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,
             email,
             allowance,
             CASE WHEN passwords.username IS NULL
                  THEN '' ELSE 'Y' END AS local,
             surname,
             initials
     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)
----------------------------------------------------------------------