DROP TABLE user;
DROP TABLE ugroup;

-- user entries
CREATE TABLE user
(
vorname		char(20) NOT NULL,	--Vorname des Benutzers
name 		char(20) NOT NULL,	--Name des Benutzers
uname		char(10) PRIMARY KEY,
			--eindeutiger Benutzername des Benutzers im Server';
pass	 	char(16) NOT NULL,	--Paßwort des Benutzers
changed		date NOT NULL
);


-- group entries 
CREATE TABLE ugroup
(
uname		char(10) NOT NULL,	--Zeiger auf user
ugroup		char(10) NOT NULL,	--Name der Gruppe
expires		date NOT NULL,		--Verfallsdatum
changed		date NOT NULL
);

CREATE UNIQUE INDEX ugroup_idx ON ugroup (uname,ugroup);

vacuum;

--- Test entries

insert into user values ('Michael', 'Klemme', 'mklemme', 'xxxx', '04-06-1999');
insert into user values ('Michaela','Klemme','maklemme', 'xxxx', '04-06-1999');

insert into ugroup values ('mklemme', 'Physics', '31-12-1999',  '04-06-1999');
insert into ugroup values ('mklemme', 'Math', '31-12-1999',  '04-06-1999'); 
insert into ugroup values ('maklemme', 'Math', '31-12-1998', '04-06-1999');


--I need to create a view for the Apache module that only returns user
--entries if a non-expired group entry exists.
--The SELECT Statement below works okay.


SELECT uname, pass FROM user 
     WHERE UNAME IN  
       (SELECT uname FROM ugroup WHERE expires >=  datetime_date('now'));



--However, if I use the same statment in a view, I get this error message

-- view to be accessed by Apache
DROP VIEW legal_user; 
CREATE VIEW legal_user AS 
   SELECT uname, pass FROM user 
     WHERE UNAME IN  
       (SELECT uname FROM ugroup WHERE expires >=  datetime_date('now'));

SELECT * FROM legal_user;