select records in report when Date is NULL

[lana123]

Hi,people!
I have to show in my report the records
based on this select:

select
claim_pk, Claim_no, judgement_date
from claim c, claim_noninsured cn
where c.claim_pk = cn.claim_fk
and cn.judgement_date in (select min(cni.judgement_date)
from claim_noninsured cni where cni.claim_fk = c.claim_pk ).

Everything works perfectly but I have the records where
judgement_date is null and I have to have them in my select also: claim_pk<>null,Claim_no<>null ,judgement_date is null
Any ideas how to do it?
I'll appreciate any answers.

Thanks in advance.

Lana

 

[synapsevampire]

This would be dependent upon your version of Crystal and the database (please include technical information when requesting it).

This is really a SQL question, not Crystal, so posting to a forum for whatever database you're using will net more and likely better results.

I'm guessing that you want:

select
claim_pk, Claim_no, judgement_date
from claim c, claim_noninsured cn
where c.claim_pk = cn.claim_fk
and
(
cn.judgement_date is null
or
cn.judgement_date in (select min(cni.judgement_date)
from claim_noninsured cni where cni.claim_fk = c.claim_pk)
)

-k

 

[Turkbear]

Hi,
Try

select  
claim_pk, Claim_no, judgement_date  
from claim c, claim_noninsured cn 
where c.claim_pk = cn.claim_fk     
and 
(cn.judgement_date in (select min(cni.judgement_date)
 or cn.judgement_date IS NULL)
from claim_noninsured cni where cni.claim_fk = c.claim_pk );

The above is Oracle syntax but you can see the logic.