using formulas where tables are left outer joined - oracle 1

[inncoggnitto]

i have a formula that uses a table and two left outer joined views. if there is no data in either of the two outer joined views i get nothing. this is the formula

{RC_BUDGET_ENTRY.AMT} - {RC_BUDGETENTRY_ISSUED_AMT_V.AMT} - {RC_BUDGETENTRY_DRAFT_AMT_V.AMT}

both of the views must have data for me to get a result, otherwise it is just blank.

hopefully there is a simple fix for this.

thanks
mike

 

[Turkbear]

Hi,
any time you set a condition or a criteria or reference a field in an Left-Outer joined table, the join becomes an Equi-Join and behaves exactly like you are experiencing...

Try testing for NULL first in your formula, something
like:

If 
(
Not IsNull({RC_BUDGETENTRY_ISSUED_AMT_V.AMT}) AND
Not IsNull({RC_BUDGETENTRY_DRAFT_AMT_V.AMT})
) Then

{RC_BUDGET_ENTRY.AMT} - {RC_BUDGETENTRY_ISSUED_AMT_V.AMT} - {RC_BUDGETENTRY_DRAFT_AMT_V.AMT}

Or have your views always create a value ( 0) for those amount fields when they are null.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[inncoggnitto]

that worked. i tried something similiar before but i thought that the problem was not the the value might be null but that there was not even a value to test at all null or otherwise.

 

[synapsevampire]

Your thinking was correct, there probably isn't a value to test, which is the definition of null, so it works in either case.

-k