🚀 go-pugleaf

Bill Todd wrote:
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.
> 

> 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.
> 

Thanks.

I am not getting this.
What do I do at <----- below to get what I want?

I can't CAST(MAX(m."RENEW_DTE") AS VARCHAR(10)) says invalid.

The problem is that the where criteria will get me two records, one for 
2007 d1 and another for 2008 d2 and I want the result to utilize those 
values in one returned "record".  I think I need temp table as the only 
way. Brain dead right now. Sleep!!

SELECT
        CAST(m."ID" AS INTEGER) AS ID,
        'bong' || (CAST(CAST(m."ID" AS INTEGER) AS VARCHAR(10))),
        m."PWORD",
        PROPERCASESTRING(m."NAMEFIRST",''),
        PROPERCASESTRING(m."NAMELAST",''),
        PROPERCASESTRING(m."DBA",'') ,
        '' ,
        m."EMAIL" ,
        m."PHONE" ,
        CAST(m."RENEW_DTE" AS VARCHAR(10)),  <-----
        SUBSTR(m."NAMEFIRST",1,1) || 'boca' || SUBSTR(m."NAMELAST",1,1) ,
        CAST(m2."CRDRPT_RNW" AS VARCHAR(10))    <----------
FROM
     "MEMBERS" m
     LEFT OUTER JOIN "MEMPAY2" m2 ON m."ID" = m2."ID"
WHERE
  (
  (m."PWORD" IS NOT NULL) 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."CRDRPT_RNW" IS NOT NULL))) OR

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


ORDER BY m.ID ASC