Midrange News for the IBM i Community


Posted by: neilrh
A/P
Jackson, MI
Left Join with subselects on both files
has no ratings.
Published: 05 Jan 2012
Revised: 23 Jan 2013 - 1852 days ago
Last viewed on: 18 Feb 2018 (3403 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Left Join with subselects on both files Published by: neilrh on 05 Jan 2012 view comments(3)

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: DaleB
Premium member *
Reading, PA
Comment on: Left Join with subselects on both files
Posted: 6 years 1 months 14 days 8 hours 55 minutes ago
Edited: Thu, 05 Jan, 2012 at 09:31:35 (2236 days ago)

Think of the result of the outer join as a temporary table. You have some rows in the temp table where A and B had matching INTKEY, and the temp table has column data from both. You may also have rows in the temp table where INTKEY didn't exist in B, so the temp table has all NULLs for the B columns.

Next you apply the WHERE condition to the result, i.e., to the temporary table. For the non-matching records, the B columns are all NULL, so the date test fails.

Instead of testing the date it in the WHERE clause, try putting it in the join conditions:

insert into OUTFILE
    select A.FLD1, A.FLD2, A.FLD3, B.FLD4
    from FILEA A
    left outer join FILE B
      on (A.INTKEY = B.INTKEY AND B.FLD5 between :StartDate and :EndDate)
    where exists(select 1 from CTLFILE C where  A.FLD1 = C.FLD1)

You could also rewrite it using a CTE, which might make it more obvious what is happening:

insert into OUTFILE
    with B as (select INTKEY, FLD4 from FILEB
                 where FLD5 between :StartDate and :EndDate)
    select A.FLD1, A.FLD2, A.FLD3, B.FLD4
    from   FILEA A
    left outer join
           B on (A.INTKEY = B.INTKEY)
    where exists(select 1
                 from   CTLFILE C
                 where  A.FLD1 = C.FLD1)

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Left Join with subselects on both files
Posted: 6 years 1 months 14 days 8 hours 53 minutes ago

And, as always, I'd try running the SELECT separately, to make sure you're getting the expected rows, before turning it into an INSERT statement.

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: Left Join with subselects on both files
Posted: 6 years 1 months 14 days 6 hours 24 minutes ago

Yup - I always run interactive SQL (obviously replacing the substitution variables with literals), until I get the result I'm expecting, and then drop it into embedded SQL (occassionally forgetting to replace the literals with replacement variables - and then wondering why it always does the same thing regardless of program call parameters!!).

Well thanks, that appears to have gotten the desired result.