🚀 go-pugleaf

I guess my problem is


Master m(id21)-> detail m2 (2007 id21 date1)
Master m(id21)-> detail m2 (2008 id21 date1)

And it should be

Master m(id21)-> detail m2(2007 id21 date1)-> detail m3(2008 id21 date1)

Max date m2.date1, m3.date1



MrT wrote:
> 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