Hi, it's a bug. It will be fixed in the next version but no schedule by now. As a bypass you could use a from select like
select * from (select * from vtest) t where t.id_lookup is null Thank you for reporting this bug and sorry for your inconvenience. Kind regards, Holger SAP Labs Berlin -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]] Sent: Montag, 20. Mai 2002 14:47 To: SAPDB Subject: Problem with a "NOT NULL" statement in a VIEW Hello all, I struggled over following scenario and I am not really sure if I am just thinking the wrong way or if the DB has a problem with VIEWS and referential constraits. I couldn't find anything in the mailin-list(hope I didn't miss anything ;-) I think it is a wrong behavior. The scenario: I must test for a NOT NULL value on a view column, which is selected using an oracle type outer join. But the select command somehow seems to look-up the origin table-columns instead of the view-columns. I created a small script where you can check the behavior: Am I wrong, or is there a problem? Regards, Manfred PS: My DB-Version is 7.3.0 / 008-000-054-344 running on SUSE LINUX 7.1 // // TEST-Script for checking view behaviour // create table lookup ( id integer, name varchar(20) ) // alter table lookup add primary key (id) // create table test( id integer, name varchar (30), id_lookup integer ) // ALTER TABLE test ADD FOREIGN KEY FK_1 (id_lookup) REFERENCES lookup (ID) ON DELETE restrict // create view vtest( id, name, id_lookup ) AS SELECT t.id, t.name, l.name FROM test t, lookup l WHERE t.id_lookup = l.id (+) // insert into lookup (id, name) values (1,'first') // insert into lookup (id, name) values (2,'second') // insert into lookup (id, name) values (3,'third') // insert into test ( id, name , id_lookup) values (1,'test1',1) // insert into test ( id, name , id_lookup) values (2,'test2',2) // insert into test ( id, name , id_lookup) values (3,'test3',null) // insert into test ( id, name , id_lookup) values (4,'test4',null) // // select * from vtest // RESULT: // ID NAME ID_LOOKUP // 1 test1 first // 2 test2 second // 3 test3 ? // 4 test4 ? // // this should be ok! // select * from vtest where id_lookup is null // RESULT: // ID NAME ID_LOOKUP // 1 test1 ? // 2 test2 ? // 3 test3 ? // 4 test4 ? // // there must be an error: in the first select for id=1 id_lookup is not null!! // select * from vtest where id_lookup is not null // RESULT: // ID NAME ID_LOOKUP // 1 test1 first // 2 test2 second // 3 test3 ? // 4 test4 ? // some other wierd result here... _______________________________________________ sapdb.general mailing list [EMAIL PROTECTED] http://listserv.sap.com/mailman/listinfo/sapdb.general
