informix sql report

Path: emory!wupost!cs.utexas.edu!uunet!infonode!ingr!nijmeg!swindon!sys2!susan From: susan@sys2.uucp (Susan Court) Newsgroups: comp.databases.informix Keywords: report Message-ID: <1992Feb12.112611.12567@swindon.ingr.com> Date: 12 Feb 92 11:26:11 GMT Sender: usenet@swindon.ingr.com (swindon usenet) Organization: Intergraph I am writing a report in Informix SQL which makes use of outer joins but is not working. I have five tables, linked as described below: The project table is the main table containing project information. This project has many activities, many software parts and many hardware parts associated with it. The project table is linked to the p_activity table by the p_code field, it is also linked to the p_swpart table and p_hwpart table via the p_code field. There is also a link from the p_swpart table to the swpart table via the s_partno field (since the information about software is split between these two tables). The select statement is trying to get all project information and all the activities, software parts (all software information) and hardware parts associated with a project. The select statement I use is as follows: select project.p_code, p_status, c_code, p_value, p_desc, p_purpose, p_seats, r_name1, r_name2, a_date, a_code, a_desc, swpart.s_partno, s_name, s_desc, s_usage, h_desc, h_qty from project, outer p_activity, outer (p_swpart, swpart), outer p_hwpart where project.p_code matches $my_proj and p_activity.p_code = project.p_code and p_swpart.p_code = project.p_code and swpart.s_partno = p_swpart.s_partno and p_hwpart.p_code = project.p_code order by p_code, a_date, s_partno, h_desc I go on to use the before group of statements in order to get a report which should look as below: PROJECT CODE STATUS CUSTOMER CODE VALUE COMPETITORS 1 I 1 $11111 PURPOSE: SEATS: DESCRIPTION here is a description ACTIVITY DATE ACTIVITY CODE ACTIVITY DESCRIPTION 12/12/1990 W this is an example description to show how the report should look 13/12/1991 A another description SOFTWARE PART NO SOFTWARE USAGE NAME SSS** This is useful for something SST*** MSTATION HARDWARE QTY HARDWARE DESCRIPTION 1 Pem Disk 3 Workstation 6 PC Instead of this report I am not getting the information in groups and I am getting the software and hardware repeated. I am getting an activity, a software partall the hardware for a project then the other software part, the same hardware and the other activity for the project. I then get the software and hardware repeated. If anyone has any idea what is wrong with the select statement could they reply to this message. Thank you. Susan Court (Systems).