DB2 left join query difficulties

Question

I am working to prepare what should be a relatively simple query through ODBC on an iSeries server. I want it to return a segment of the data no matter what the join is for a report we are creating. Here is the query:

  Select   SCDATA.SCCLNT.CCLNT,
  SCDATA.SCCLNT.CNAME,
  SCDATA.SCCLNT.CLTGRP As Group,
  CONCAT(TRIM(SCDATA.SCCLNT.CADD1), SCDATA.SCCLNT.CADD2) As Address1,
  CONCAT(TRIM(SCDATA.SCCLNT.CCITY), CONCAT(', ',
      CONCAT(TRIM(SCDATA.SCCLNT.CSTATE), CONCAT('  ', 
      TRIM(SCDATA.SCCLNT.CZIP)))))
      As Address2,
  SCDATA.SCACCT.GCLNT,
  SCDATA.SCACCT.GPLACE,
  SCDATA.SCACCT.GPLDAT,
  SCDATA.SCACCT.GACCT# As GACCTnum,
  CONCAT(TRIM(SCDATA.SCACCT.GLAST), CONCAT(', ', 
    TRIM(SCDATA.SCACCT.GFIRST))) As
    Debtor,
  CONCAT((Select 
     VARCHAR_FORMAT(Min(TIMESTAMP_FORMAT(Cast(SCDATA.SCACCT.GPLDAT
     As Char(10)), 'YYYYMMDD')), 'MM/DD/YYYY') From SCDATA.SCACCT
     Where SCDATA.SCACCT.GPLDAT Between 20170401 And 20170501), CONCAT(' 
     - ', (Select 
     VARCHAR_FORMAT(Max(TIMESTAMP_FORMAT(Cast(SCDATA.SCACCT.GPLDAT As
     Char(10)), 'YYYYMMDD')), 'MM/DD/YYYY') From SCDATA.SCACCT
     Where SCDATA.SCACCT.GPLDAT Between 20170401 And 20170501))) As 
     dateRange
From SCDATA.SCCLNT
  Left Outer Join SCDATA.SCACCT On 
    CDATA.SCACCT.GCLNT = SCDATA.SCCLNT.CCLNT
Where SCDATA.SCACCT.GPLDAT Between 20170401 And 20170501 And
  SCDATA.SCCLNT.CLTGRP Like '######'

This query works fine when the SCACCT table returns values but I need at least a single row from SCCLNT to populate client details in the report. The report software we are using (DBExtra.net) does not support multiple queries per report so it all has to be bundled into a single query.

Unfortunately beyond basic queries SQL is not one of my strong suits so all help will be appreciated. Thank you.

You have conditions in your where clause relating to columns in SCACCT (the table you are outer joining to) which mean that no null rows from the outer join will be returned. Either include these conditions in the join clause, or include null possibility in all the conditions: (gpldat between x and y or gpldata is null) and (cltgrp like '######' or cltgrp is null) — Lord Peter, May 12 '17 at 12:38
Side note - please use an exclusive upper-bound (`<`) for date/time/timestamp queries, [or risk unintended data](https://sqlblog.org/2011/10/19/what-do-between-and-the-devil-have-in-common). — Clockwork-Muse, May 17 '17 at 20:16

score 2 · Accepted Answer · edited May 23 '17 at 11:54

2

There is a contradiction between Left Outer Join SCDATA.SCACCT and Where SCDATA.SCACCT.GPLDAT Between 20170401 And 20170501. What you can do is move this WHERE clause to the ON clause of the outer join, so the date criteria applies before SCACCT is joined to SCCLNT:

From SCDATA.SCCLNT
  Left Outer Join SCDATA.SCACCT On 
    CDATA.SCACCT.GCLNT = SCDATA.SCCLNT.CCLNT And
    SCDATA.SCACCT.GPLDAT Between 20170401 And 20170501 
Where SCDATA.SCCLNT.CLTGRP Like '######'

See Filter Table Before Applying Left Join for an explanation.

edited May 23 '17 at 11:54

Community

1
1

answered May 12 '17 at 12:39

Stavr00

3,219
1
16
28

That worked perfectly thanks. Now I just have to figure out how to get my report to eliminate the null values in the details segment :) I appreciate the help. – Geoff May 12 '17 at 15:07
@Geoff You may want to add `GROUP BY` to simplify how you create your `dateRange` computed column. If you want a substitution for null values, use `VALUE(expression,expression ... )` which returns the first non-null argument – Stavr00 May 12 '17 at 17:00

Lukas Eder · Answer 2 · 2017-05-15T11:00:44.443

Probably not what you wanted, exactly, but in case someone else finds this question for the reason I had in mind:

Here's how to generate at least one row (repeating only the relevant part):

From SYSIBM.DUAL
  Left Outer Join (
    Select *
    From SCDATA.SCCLNT
      Left Outer Join SCDATA.SCACCT On 
        CDATA.SCACCT.GCLNT = SCDATA.SCCLNT.CCLNT
    Where SCDATA.SCACCT.GPLDAT Between 20170401 And 20170501 And
    SCDATA.SCCLNT.CLTGRP Like '######'
  ) t On 1 = 1

Notice how I wrapped your original query in a derived table called t. This means:

You'll have to handle ambiguous column names
You'll have to dereference your columns from t, no longer from SCCLNT or SCACCT

SYSIBM.DUAL is guaranteed to always contain exactly one row.

DB2 left join query difficulties

2 Answers2