🚀 go-pugleaf

HI Again:

    I am screwing around with this trying to get the F_MAXDATE(v1,v2) to 
work. That is a whole other issue which results in numeric or string 
overflow or some such.


Trying to get query to work:

FROM
     "MEMBERS" m
     LEFT OUTER JOIN "MEMPAY2" m2 ON m."ID" = m2."ID"
     INNER JOIN "MEMPAY2" m3 ON m2."ID" = m3."ID"
WHERE
  (
  (m."PWORD" IS NOT NULL AND m.STATUS_MEM = 'ACTIVE') AND
     (
     ((m2."YEARM2" = (Extract(Year from CURRENT_DATE))) AND 
(m2."RENEW_DATE" IS NOT NULL) and
      (m2."DATE_PAID" IS NULL )) or

     ((m2."YEARM2" = (Extract(Year from CURRENT_DATE) - 1)) AND 
(m2."RENEW_DATE" IS NOT NULL) and
      (m2."DATE_PAID" IS NULL)) or

     ((m2."YEARM2" = (Extract(Year from CURRENT_DATE) + 1)) AND 
((m2."RENEW_DATE" IS NOT NULL)) ) OR

     ((m2."YEARM2" = (Extract(Year from CURRENT_DATE) + 1)) AND 
((m2."DATE_PAID" IS NOT NULL)))
/*    )*/
    OR

     ((m3."YEARM2" = (Extract(Year from CURRENT_DATE))) AND 
(m3."CRDRPT_RNW" IS NOT NULL) and
      (m3."CRDRPT_PD" IS NULL )) or

     ((m3."YEARM2" = (Extract(Year from CURRENT_DATE) - 1)) AND 
(m3."CRDRPT_RNW" IS NOT NULL) and
      (m3."CRDRPT_PD" IS NULL)) or

     ((m3."YEARM2" = (Extract(Year from CURRENT_DATE) + 1)) AND 
(m3."CRDRPT_RNW" IS NOT NULL)) OR

     ((m3."YEARM2" = (Extract(Year from CURRENT_DATE) + 1)) AND 
(m3."CRDRPT_PD" IS NOT NULL))

    )

 From this I get many, many duplicates including ones whose YEARM2 
ranges from 1999 to 2008. I can't see how since I limit the records to 
2007 +/- 1 in each clause (or so I think).

The first block is supposed to get all possibilities for RENEW_DATE and 
DATE_PAID and pick up CRDRPT_RNW and CRDRPT_PD within the year range 
while the second block gets all possibilities for CRDRPT_RNW and 
CRDRPT_PD within the year range which will pick up those values from
records which do not meet the criteria in the first bloc. The F_MAXDATE 
function will ferret out which value I want.






Bill Todd wrote:
> MrT wrote:
> 
>> The id is the only one effectively GROUP BY.
> 
> That is correct. The first query should return the the result you want
> with the GROUP BY ID added.
>