### Title

How to account for records that may be empty/not exist

### Post

I'm struggling with a calculation in my database and there must be a solution to my problem but I have not been able to figure it out by myself. Essentially, the calculation takes a stock price from one table and multiplies it by the shares held in another table. Here's the catch: the shares held may be a positive integer, it may be zero, it may be blank, or there may be no record at all. The relationship for this calculation is based on the stock ticker and the companyID for the holding firm. If the firm has never held a position in the ticker, there will be no matching record.

What I want my calculation to do is substitute an a zero for the shares held if it's anything other than a positive integer. I have tried the following, and neither has been able to account for the possibility of a non-record, I get a ? in my resulting field:

Lookup (Market Data 3::Last Price) * Case ( IsValid (Holdings Prio::Q3'09) ; Holdings Prio::Q3'09 ; 0 ) / 1000000

Lookup (Market Data 3::Last Price) * ( If ( IsValid ( Holdings::Q3'09 ) ; If ( IsEmpty (Holdings::Q3'09) ; 0; Holdings::Q3'09 ) ; 0 ) / 1000000 )

Okay I took the calculation apart and tested it bit by bit and it turns out the issue was (of course) in a completely different area than I was focusing on. It was the Lookup() function for the Market Data table. I took out the lookup() function and it worked fine. My final calculation ended up being:

Market Data 3::Last Price * Case ( Q3'09 > 0 ; Q3'09 ; 0 ) / 1000000

Thanks for all the help.