RE: left join : SQL

Well, without more to go on, I'd say I think your join criteria will result in a super-massive result set... for each row in A, your join criteria will find every row in table B of the same value. If you have 673 values in scope in table A, and a like number of rows in table B, the result of the join will return 673 rows, 673 times... or 673^2, or around 450,000 rows total...

-Eric DeLong

-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of A Paul
Sent: Tuesday, January 15, 2013 3:13 PM
To: Midrange Systems Technical Discussion
Subject: left join : SQL

select count(*) from                                         
      DEVLIB/MASTERF A                                      
                                                             
             WHERE        A.PIOTY='S' AND A.PSTRH           
IN('9K','8U') 
gets me 673 records.
-----------
dspfd devlib/BOOKf--> 5822443    
dspfd devlib/MASTERF--> 1940039
--------
Ideally
the below query should get me 673 records,
but SQL keeps running for several minutes and fetch me unlimited
number of records.
SELECT A.*,B.PSTRH,B.QNTY,B.VATAMT
 FROM DEVLIB/MASTERF A left join                
DEVLIB/BOOKF B
ON A.PSTRH = B.PSTRH
WHERE        A.PIOTY='S' AND A.PSTRH in('9K','8U') 
does anyone see anything wrong here?


From: "Stone, Joel" <Joel.Stone@xxxxxxxxxx>
To: 'Midrange Systems Technical Discussion' <midrange-l@xxxxxxxxxxxx>
Sent: Tuesday, 15 January 2013, 20:07
Subject: RE: SQL help - Data base conversion mapping error. 0 fields in error.

This particular file definition pre-dates me at this company by about 20 years :)





-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Tuesday, January 15, 2013 1:17 PM
To: Midrange Systems Technical Discussion
Subject: RE: SQL help - Data base conversion mapping error. 0 fields in error.

Still using DDS to define files, aren't you?


Rob Berendt