creating a view

Path: emory!swrinde!cs.utexas.edu!qt.cs.utexas.edu!yale.edu!heifetz!math.fu-berlin.de!uniol!unido!sbsvax!ukh840!becker From: becker@ukh840.med-in.uni-sb.de Newsgroups: comp.databases.informix Message-ID: <15648@sbsvax.cs.uni-sb.de> Date: 17 Jan 92 10:58:13 GMT Sender: news@sbsvax.cs.uni-sb.de Sirs, in out patient's information system, we have a table (hkt_nr) with the basic data of cardiac catheterisation. The table patient includes patients name, adress, ... The join is to be done by izahl, the patients identification. Every patient could be catheterisized many times. I want to create a view, which includes the name, date of birth, sex of the patient (from table patient) and a descending list of all cardiac catheterisations of this patient. I have constructed the following view, but I am not shure to include all catheterisations of the patients. (I think there is a max-function necessary anywhere, but i don't know where. Is this construction correct or have You any ideas? create view hkt_kathliste as select patient.izahl, patient.pname, patient.vorname, patient.gebname, patient.geschl, patient.gebdat, a01.hktnr hktnr_01, a01.datum datum_01, a02.hktnr hktnr_02, a02.datum datum_02, a03.hktnr hktnr_03, a03.datum datum_03, a04.hktnr hktnr_04, a04.datum datum_04, a05.hktnr hktnr_05, a05.datum datum_05, a06.hktnr hktnr_06, a06.datum datum_06, a07.hktnr hktnr_07, a07.datum datum_07, a08.hktnr hktnr_08, a08.datum datum_08, a09.hktnr hktnr_09, a09.datum datum_09 from patient, hkt_nr a01, outer (hkt_nr a02, outer (hkt_nr a03, outer (hkt_nr a04, outer (hkt_nr a05, outer (hkt_nr a06, outer (hkt_nr a07, outer (hkt_nr a08, outer (hkt_nr a09)))))))) where patient.izahl = a01.izahl and a01.izahl = a02.izahl and a02.izahl = a03.izahl and a03.izahl = a04.izahl and a04.izahl = a05.izahl and a05.izahl = a06.izahl and a06.izahl = a07.izahl and a07.izahl = a08.izahl and a08.izahl = a09.izahl and a01.hktnr > a02.hktnr and a02.hktnr > a03.hktnr and a03.hktnr > a04.hktnr and a04.hktnr > a05.hktnr and a05.hktnr > a06.hktnr and a06.hktnr > a07.hktnr and a07.hktnr > a08.hktnr and a08.hktnr > a09.hktnr Any comments ? Thank You Dieter -- Dr. med. dipl.-math Dieter Becker Tel.: (0 / +49) 6841 - 16 3046 Medizinische Universitaets- und Poliklinik Fax.: (0 / +49) 6841 - 16 3369 Innere Medizin III D - 6650 Homburg / Saar Email: becker@med-in.uni-sb.de