where not empid is null

-----Original Message-----
From: semprolbat [mailto:semprolbat@...]
Sent: 11 March 2005 02:56
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Re: Interesting results with joined views
(WI-V6.2.972, fb1.0.3)



Svein Erling,

I do not know what is wrong; the database or my expectations. ;-)
I did not mention initially that the joins are based on views, which
seems to be the source of the problem.

Here is a test case:

Semp


/* test db */

create database "__test.fdb" user "sysdba" password "masterkey";

create table t_a (
id integer not null unique,
mgrid integer not null
);

create table t_b (
empid integer not null unique,
empname varchar(20) not null
);

create table t_c (
empid integer not null references t_b (empid),
mgrid integer not null
);

create view v_b as
select empid, empname
from t_b;

create view v_c as
select empid, mgrid
from t_c;

create view v_a as
select id, a.mgrid, c.empid
from t_a a
left outer join v_c c on a.mgrid = c.mgrid
;

create view v_a2 as
select id, a.mgrid, c.empid
from t_a a
left outer join t_c c on a.mgrid = c.mgrid
;

insert into t_a values(2, 5);
insert into t_b values(1, 'employee nr. 1');
insert into t_c values(1, 5);

commit;

/* test queries, view based view */


select count(*) from v_a;
/*
Result
COUNT
============

1

Expected result:
COUNT
============

1
*/

select count(*) from v_a where empid is null;
/*
Result
COUNT
============

1

Expected result:
COUNT
============

0
*/


select count(*) from v_a where empid is not null;
/*
Result
COUNT
============

1

Expected result:
COUNT
============

1
*/

select * from v_a;
/*
Result
ID MGRID EMPID
============ ============ ============

2 5 1

Expected result:
ID MGRID EMPID
============ ============ ============

2 5 1
*/

select * from v_a where empid is null;
/*
Result
ID MGRID EMPID
============ ============ ============

2 5 <null>
Expected result:
<zero records>

*/


;

/* test queries, table based view */


select count(*) from v_a2;
select count(*) from v_a2 where empid is null;
select count(*) from v_a2 where empid is not null;
select * from v_a2;
select * from v_a2 where empid is null;







Yahoo! Groups Sponsor
ADVERTISEMENT





----------------------------------------------------------------------------
--
Yahoo! Groups Links

a.. To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

b.. To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

c.. Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



[Non-text portions of this message have been removed]