I have a define in my report:

DEFINE FILE RCRAPP1
ASSETS_IND/I1=
IF RCRAPP1_AIDY_CODE EQ '&AID_YEAR' AND ((RCRAPP1_PAR_CASH_AMT + RCRAPP1_CASH_AMT + RCRAPP4_PAR_INV_NET_WORTH 
+ RCRAPP4_INV_NET_WORTH + RCRAPP4_PAR_BUS_NET_WORTH + RCRAPP4_BUS_NET_WORTH) LT 100000) THEN 1
ELSE 0;
END

PROBLEM: if the values for any of the fields (e.g. RCRAPP1_PAR_CASH_AMT) in the summation is null, then the end result is always a 0 and never a 1.

Is there a function that I can use with these fields that will return a zero value when it pulls the field instead of a null value?

I know that I can create a single define for each field (e.g. RCRAPP1_PAR_CASH_AMT) that i use in other defines, but I was hoping for a more efficient way of doing this.

Brian

What you could do is to first collect all needed fields and store them in a hold file using the holdmiss=off setting.
Next you can do your defines against this hold file where now all fields are by definition not-missing and filled with 0 iso null.

Hope this helps ...

I would try this

DEFINE FILE RCRAPP1
ASSETS_IND/I1=
IF RCRAPP1_AIDY_CODE EQ '&AID_YEAR' AND ((1*RCRAPP1_PAR_CASH_AMT + 1*RCRAPP1_CASH_AMT + 1*RCRAPP4_PAR_INV_NET_WORTH 
+ 1*RCRAPP4_INV_NET_WORTH + 1*RCRAPP4_PAR_BUS_NET_WORTH + 1*RCRAPP4_BUS_NET_WORTH) LT 100000) THEN 1
ELSE 0;
END

It might help.

quote:

DEFINE FILE RCRAPP1
ASSETS_IND/I1=
IF RCRAPP1_AIDY_CODE EQ '&AID_YEAR' AND ((RCRAPP1_PAR_CASH_AMT + RCRAPP1_CASH_AMT + RCRAPP4_PAR_INV_NET_WORTH
+ RCRAPP4_INV_NET_WORTH + RCRAPP4_PAR_BUS_NET_WORTH + RCRAPP4_BUS_NET_WORTH) LT 100000) THEN 1
ELSE 0;
END

Problem is in rdbms (stupidity**stupidity)

Value + null = null (something add nothing is nothing)

In wf (sensible option)

Value + null = Value (something add nothing is something)

The problem you have is that the calc is passed to the rdbms and done there.

To prevent this

DEFINE FILE RCRAPP1
ASSETS_IND/I1= IF EDIT('A','9') EQ 'B' THEN -1
 ELSE IF RCRAPP1_AIDY_CODE EQ '&AID_YEAR' AND ((RCRAPP1_PAR_CASH_AMT + RCRAPP1_CASH_AMT + RCRAPP4_PAR_INV_NET_WORTH 
+ RCRAPP4_INV_NET_WORTH + RCRAPP4_PAR_BUS_NET_WORTH + RCRAPP4_BUS_NET_WORTH) LT 100000) THEN 1
ELSE 0;
END

The IF EDIT('A','9') EQ 'B' THEN -1 is there to purely disable optimization since it uses a wf function EDIT. It will never be executed and the result of the expression still applies.

However the result will be calculated by wf and not the rdbms and thus will give you the correct result you desire!

We are waiting for SQL to catch up with wf!

Thanks everyone for your ideas. The forum went down after I posted, and I had another idea after a while.

Assuming there aren't any negative values, I was able to use abs(field_name) in my define and that seemed to work.

I do like the (1*field_name) option, and I'll try the edit option too.

Thanks!

The abs worked because wf did the calculation instead of the rdbms, because abs is a wf function not supported on the rdbms.

Inadvertently you've stumbled on my solution!

You can cheat and use SET NODATA = 0, but that will set all missing fields to 0.