Re: informix sql report

Path: emory!gatech!ukma!asuvax!anasaz!qip!briand From: briand@anasaz (Brian Douglass) Newsgroups: comp.databases.informix Keywords: report Message-ID: <1992Feb18.172014.18754@anasaz> Date: 18 Feb 92 17:20:14 GMT References: <1992Feb12.112611.12567@swindon.ingr.com> Organization: Anasazi, Inc. Phoenix, Az In article <1992Feb12.112611.12567@swindon.ingr.com> susan@sys2.uucp (Susan Court) writes: >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 > In theory, this is all fine and well, but in practice, it is nothing but a nightmare. You don't state whether you are using ACE or 4GL. In ACE, you're screwed, in 4GL, it's no sweat. The problem you're getting into is a large number of resulting rows from the joins, and lower elements in the join being repeated. The number of rows produced by such a join is basically the #rows in project X #rows in p_activity X (p_swpart,swpart join) X #rows in p_hwpart. For example: 1 row in project joins to 3 rows in p_activity, 5 in (p_swpart, swpart), and 2 in p_hwpart. You're going to get something like: project=1 p_activity=1 (psw,sw)=1 p_hwpart=1 project=1 p_activity=1 (psw,sw)=1 p_hwpart=2 project=1 p_activity=1 (psw,sw)=2 p_hwpart=1 project=1 p_activity=1 (psw,sw)=2 p_hwpart=2 project=1 p_activity=1 (psw,sw)=3 p_hwpart=1 project=1 p_activity=1 (psw,sw)=3 p_hwpart=2 project=1 p_activity=1 (psw,sw)=4 p_hwpart=1 project=1 p_activity=1 (psw,sw)=4 p_hwpart=2 project=1 p_activity=1 (psw,sw)=5 p_hwpart=1 project=1 p_activity=1 (psw,sw)=5 p_hwpart=2 Then repeated all over again for the next p_activity. 1*3*5*2=30 rows for just one project. >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. Well, I think you can see why software and hardware parts keep repeating, because their relation is with project, not p_activity. I once had a report generate 2.5 million records because of a join like this, which once boiled back down became 3500! ACE expects data in a heirarchal form Project p_activity psw,sw p_hwpart Which is great for inner joins, however, what you are looking for is: Project p_activity psw,sw p_hwpart The solution to this is dilemma is 4GL. You can do as many concurrent selects as you want and embedd the necessary branching to print as you need. The way I typically do it would be to build one cursor for project and p_activity, then a second for psw,sw, and a third for p_hwpart. For every project row I pull up, I then open the cursors to psw,sw and p_hwpart, passing the necessary foreign key data to retrieve their records. Print, and then fetch another project. Doing a large, multi-table joined select is always the theoretical ideal, but in practice it causes more problems than it solves (usually massive temporary tables are built, even though all is indexed). However, different DBMS and even different versions of Informix behave differently to the exact same query. Moral, always experiment with each new version to determine the optimum method for writing queries, you can never expect them to behave in the "SQL ideal". I would have E-mailed this, but having seen even the best and brightest make this same mistake under the misconception that SQL was SQL and that you should never break up a select statement, I thought others would be interested. P.S. It's ironic that a report that someone else in my company wrote made this very same mistake. I am now in the process of breaking up it's select statement (it creates 2 temporary tables and sequentially reads the tables, yuck!) so that it doesn't take up a dozen or so gigabytes in workspace when it's run against a primary table with ~3 million rows! Good Luck. > > Susan Court (Systems). -- Brian Douglass briand%anasaz.UUCP@asuvax.eas.asu.edu 602-870-3330 X657