Path: utzoo!censor!geac!torsqnt!hybrid!scifi!bywater!uunet!fciva!dag From: dag@fciva.FRANKCAP.COM (Daniel A. Graifer) Newsgroups: comp.databases Subject: Problem with SQL join/group-by in Unify database (long) Message-ID: <584@fciva.FRANKCAP.COM> Date: 20 Jan 91 19:05:57 GMT Reply-To: dag@fciva.UUCP (Daniel A. Graifer) Distribution: na Organization: Coastal Capital Funding Corp., McLean, VA Lines: 101 Let me appologize in advance for the lengthly example. I am having a problem with group-by with joins in Unify/Accell SQL: $ SQL -version 4.0-880916 SYS5LCK USSLCK M8BIT (international) $ #I have added "*" to the field listings to mark key fields $ SQL UNIFY TURBO/SQL Copyright Unify Corporation 1986 sql>fields apmaster NAME TYPE LENGTH ap_region INTEGER 2 * ap_brch INTEGER 2 * ap_reg_num LONG 6 * : ap_loan_amt LONG 8 : ap_funding_date DATE 2 : ap_broker STRING 8 : sql>fields rolodex NAME TYPE LENGTH rd_co_id STRING 8 * rd_co_name STRING 30 : sql>!# ap_broker should have been a ref to rd_co_id, but isn't (historical) sql>select ap_broker, count(*), sum(ap_loan_amt) from apmaster sql> where ap_funding_date between 12/01/90 and 12/31/90 sql> and ap_region = 2 sql> group by ap_broker / recognized query! ap_broker| count(*)|sum(ap_loan_amt) ------------------------------------- : 141 TUS | 3| 966250.00000 : 960 TUS | 1| 169200.00000 : sql> select rd_co_id, rd_co_name, count(*), sum(ap_loan_amt) from apmaster, sql> rolodex where ap_funding_date between 12/01/90 and 12/31/90 sql> and ap_region = 2 and rd_co_id = ap_broker sql> group by rd_co_name / recognized query! rd_co_id|rd_co_name | count(*)|sum(ap_loan_amt) ------------------------------------------------------------------- 960 TUS |REPLACED FOR PRIVACY 1 | 2| 374900.00000 141 TUS |REPLACED FOR PRIVACY 2 | 1| 169200.00000 : select rd_co_id, rd_co_name, count(*), sum(ap_loan_amt) from apmaster, rolodex where ap_funding_date between 12/01/90 and 12/31/90 and ap_region = 2 and rd_co_id = ap_broker group by ap_broker / recognized query! rd_co_id|rd_co_name | count(*)|sum(ap_loan_amt) ------------------------------------------------------------------- 141 TUS |REPLACED FOR PRIVACY 2 | 2| 388750.00000 : 178 TUS |REPLACED FOR PRIVACY 1 | 2| 326950.00000 : 960 TUS |REPLACED FOR PRIVACY 3 | 2| 246435.00000 : sql>select ap_broker, ap_loan_amt from apmaster where ap_region = 2 sql> and ap_funding_date between 12/01/90 and 12/31/90 sql> and ap_broker in <'141 TUS','178 TUS','960 TUS'> / recognized query! ap_broker|ap_loan_amt ---------------------- 960 TUS | 169200 141 TUS | 600000 178 TUS | 187450 178 TUS | 187450 141 TUS | 146250 141 TUS | 220000 sql>end I've only shown two lines of the about 200 that come out, but as you can see, joining to the rolodex table to get the broker's company name gives me 'incorrect' answers (both the id/co pairs are wrong, and the totals). I spoke with someone at Unify Tech support, and they were sure the problem was damaged explicit relationship tables. I tried rebuilding the hash tables and the explicit relationships yesterday (this took 8 hours), but the problem hasn't gone away. So now I turn to the net. Either I don't understand how joins work with 'group by', or I need to be very frightened about every report I run that does this. Fortunately, most of our reports need both loan details and totals, so we don't do a lot of group-bys, we pull detail and summarize in the report writer. Any Ideas? Anxiously awaiting any input, and thanks in advance... Dan -- Daniel A. Graifer Coastal Capital Funding Corp. Sr. Vice President, Financial Systems 7900 Westpark Dr. Suite A-130 (703)821-3244 McLean, VA 22102 uunet!fciva!dag fciva.FRANKCAP.COM!dag@uunet.uu.net