Suppress if field is null (from an outer join) 1

[HoosHot]

Hi,

I have two tables:
ACCOUNT
id
amount

CREDIT
id
credit_amount_aaa
credit_amount_bbb

The join is:
SELECT account.id
, account.amount
, credit.credit_amount_aaa
, credit.credit_amount_bbb
FROM account
, credit
WHERE account.id = credit.id(+)

I have id in section a, amount in section b, credit_amount_aaa in section c and credit_amount_bbb in section d of my report. I want to suppress the section c if credit_amount_aaa is 0 or is null. I want to suppress the section d if credit_amount_bbb is 0 or is null.

Here is my suppress logic for section c:
{CREDIT.CREDIT_AMOUNT_AAA} = 0
or
IsNull ({CREDIT.CREDIT_AMOUNT_AAA})

Here is my suppress logic for section d:
{CREDIT.CREDIT_AMOUNT_BBB} = 0
or
IsNull ({CREDIT.CREDIT_AMOUNT_BBB})

Let's say I have this data in my database:
ACCOUNT
xxx 10
CREDIT
no rows
My report displays section a, section b, section c and section d. (It should only display section a and section b.)

Let's say I have this data in my database:
ACCOUNT
xxx 10
CREDIT
xxx 12 <null>
My report displays section a, section b and section c. (This is correct.)

How should I change my suppress logic for section c and section d so it correctly suppresses these two sections when the outer join brings back nothing from the CREDIT table because is isn't a corresponding row in CREDIT?

Thanks in advanced for your help
mnguyen_va@yahoo.com

 

[kenhamady]

You have to do the IsNull first, because formulas will otherwise choke on the null value. If you do:


IsNull ({CREDIT.CREDIT_AMOUNT_AAA})
OR
{CREDIT.CREDIT_AMOUNT_AAA} = 0

It should work. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[HoosHot]

Thanks, Ken, you are the best!

 

[HomerJS]

I have 3 tables, dTmpOwner, dTmpDriver, and NewTable.
dTmpOwner contains: IDOwner, Name
dTmpDriver contains: IDDriver, Name
NewTable contains: ID, Name, OwnerFlag

My problem is that some owners can also be drivers, but their IDs may be different. How can I combine the two tmp tables using SQL into the NewTable with their IDOwner if they're both a owner and driver or their IDDriver if they're just a driver as well as have the OwnerFlag = Y if they're from dTmpOwner?

Thanks in advance.

 

[kenhamady]

Please start a new question. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com