🚀 go-pugleaf

Hi:


I have progressed to 90% success but have one last hole in my query:

This is stripped down for testing:

SELECT
        CAST(m."ID" AS INTEGER) AS ID,
        m4.YEARM2 AS "Prior",
        m2.YEARM2 AS "Current",
        m3.YEARM2 AS "Next",
        F_MAXDATE(F_MAXDATE(m2."CRDRPT_RNW", m3."CRDRPT_RNW"), 
m4."CRDRPT_RNW) AS "CR Renew"
FROM
     "MEMBERS" m
     LEFT OUTER JOIN "MEMPAY2" m2 ON m."ID" = m2."ID"
     LEFT OUTER JOIN "MEMPAY2" m3 ON m2."ID" = m3."ID"
     LEFT OUTER JOIN "MEMPAY2" m4 on m2."ID" = m4."ID"
WHERE
  (m.STATUS_MEM = 'NON-MEMBER' AND m.TOT_CNT > 0 and m."PHONE" IS NOT 
NULL) or


  (    (m."PWORD" IS NOT NULL AND m.STATUS_MEM = 'ACTIVE')  AND

   (    m2."YEARM2" = Extract(Year from CURRENT_DATE) and m3."YEARM2" = 
(Extract(Year from CURRENT_DATE)+1)
     and m4."YEARM2" = (Extract(Year from CURRENT_DATE)-1)      ) and

    (((m2."CRDRPT_RNW" is not null and m2."CRDRPT_PD" is null) or
     (m2."CRDRPT_RNW" is null and m2."CRDRPT_PD" is null)) or

    (m4."CRDRPT_RNW" is not null) and (m4."CRDRPT_PD" is null) or

    (m3."CRDRPT_RNW" is not null))

  )

ORDER BY m.ID ASC


Problem 1:

As long as each master record m has three detail records m2, m3 & m4 it 
produces the desired result. When only m2 and m4 are present, the query 
does not present the master detail, m is selected out.

This is contrary to my understanding of the left outer join where each 
and every record meeting the selection criteria for the master is 
presented whether or not detail records exits. Where the detail does not 
exits, a null record is produced.

I have also tried this joining on m instead of m2 and it makes no 
difference.


In sample of data below 5, 11, 14 do not present even though they meet 
the selection criteria:

    (((m2."CRDRPT_RNW" is not null and m2."CRDRPT_PD" is null) or <--
     (m2."CRDRPT_RNW" is null and m2."CRDRPT_PD" is null)) or



ID         YEARM2   RENEW_DATE   CRDRPT_RNW   CRDRPT_PD
====       ======   ==========   ==========   =========
3          2006     07/06/2006
3          2007     07/06/2007   08/30/2007
3          2008     07/06/2008
4          2006     01/30/2006   01/30/2006   02/03/2006
4          2007     01/30/2007   01/30/2007   01/19/2007
4          2008     01/30/2008   01/30/2008
5          2006     07/15/2006   07/15/2006   08/02/2006
5          2007     07/15/2007   07/15/2007               <--
7          2006     02/01/2006
7          2007     02/01/2007
7          2008     02/01/2008
9          2006     08/03/2006   08/03/2006
11         2006     08/19/2006   08/19/2006   08/25/2006
11         2007     08/19/2007   08/19/2007               <--
12         2006     08/19/2006   08/19/2006
13         2006     09/01/2006   08/20/2006   09/15/2006
13         2007     09/01/2007   08/20/2007   07/25/2007
13         2008                  08/20/2008
14         2006     09/12/2006
14         2007     09/12/2007   08/30/2007               <--
15         2006
15         2007     04/12/2007   08/30/2007
15         2008     04/12/2008





Problem 2: Where m2, m3 & m4 exist but CRDRPT_RNW is null in each, the 
returned date is a default 11/17/1858. How to exclude presenting this?