Re: New Thread Combining data 2 records

Article View: borland.public.interbase.sql

Article #595

Back to borland.public.interbase.sql Previous Next

#595

From: MrT
Date: Wed, 08 Aug 2007 18:28

38 lines
2074 bytes

MrT wrote:
> MrT wrote:
>> Hi:
>> 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?
> 
> So the 11/17/1858 comes from F_MAXDATE(D1, D2) where D1 and D2 are null.
> 
> Anyone know any other similar that will return null if both are null?

Solved.

Thanks.

SELECT CAST(m."ID" AS INTEGER) AS ID
      , m4.YEARM2 AS "Prior"
      , m2.YEARM2 AS "Current"
      , m3.YEARM2 AS "Next"
      , CAST(CAST(F_MAXDATE(F_MAXDATE( m3."CRDRPT_RNW"
                 , m2."CRDRPT_RNW" )
                 , m4."CRDRPT_RNW" ) AS DATE) AS VARCHAR(10)) AS "CR Renew"
   FROM "MEMBERS" m
LEFT OUTER
   JOIN "MEMPAY2" m2
     ON m2."ID" = m."ID"
    and m2.YEARM2 = '2007'
LEFT OUTER
   JOIN "MEMPAY2" m4
     on m4."ID" = m."ID"
    and m4.YEARM2 = '2006'
LEFT OUTER
   JOIN "MEMPAY2" m3
     ON m3."ID" = m."ID"
    and m3.YEARM2 = '2008'
WHERE m."PWORD" IS NOT NULL
    AND m.STATUS_MEM = 'ACTIVE'
ORDER
     BY m.id asc

Technical Details

Message-ID: <46b9fd03$1@newsgroups.borland.com>

Path:

arc1-ca.pugleaf.net!g2news2.google.com!news2.google.com!border1.nntp.dca.giganews.com!border2.nntp.dca.giganews.com!nntp.giganews.com!newspeer1.nwr.nac.net!newsfeeds.ihug.co.nz!ihug.co.nz!newsgroups.borland.com!not-for-mail

References: <46b7b088@newsgroups.borland.com> <46b85fd5@newsgroups.borland.com> <46b86fbc@newsgroups.borland.com>

Show Full Headers

Complete Article Headers

X-Google-Thread: 114a0e,633e86cefe754509
X-Google-Attributes: gid114a0e,public,usenet
X-Google-Language: ENGLISH,ASCII-7-bit
Date: Wed, 08 Aug 2007 13:28:17 -0500
From: MrT <contact@heat-mi.org>
User-Agent: Thunderbird 1.5.0.12 (Windows/20070509)
MIME-Version: 1.0
Newsgroups: borland.public.interbase.sql
Subject: Re: New Thread Combining data 2 records
References: <46b7b088@newsgroups.borland.com> <46b85fd5@newsgroups.borland.com> <46b86fbc@newsgroups.borland.com>
In-Reply-To: <46b86fbc@newsgroups.borland.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: 69.39.70.127
Message-ID: <46b9fd03$1@newsgroups.borland.com>
X-Trace: newsgroups.borland.com 1186594051 69.39.70.127 (8 Aug 2007 10:27:31 -0700)
Lines: 38
Path: arc1-ca.pugleaf.net!g2news2.google.com!news2.google.com!border1.nntp.dca.giganews.com!border2.nntp.dca.giganews.com!nntp.giganews.com!newspeer1.nwr.nac.net!newsfeeds.ihug.co.nz!ihug.co.nz!newsgroups.borland.com!not-for-mail
X-Original-Bytes: 1917

Reply to Parent Message