If the lookup fails to find the corresponding key value, a question mark is returned. If you require a different datatype, such as date, decimal, or integer, you must expand the expression to accommodate this.
The following shows an example of how to return a default date if one is not found:
begin returns char;
if ( LookupDatabaseTable('qatest.dbo.ORD', 'ORDER_ID', 'ORDER_DATE', tostring("ORDERID") ) == '?')
return '1900-01-01 01:01:01.000';
else
return LookupDatabaseTable('qatest.dbo.ORD', 'ORDER_ID', 'ORDER_DATE', tostring("ORDERID") );
end;The following shows an example of how to return a decimal value when the lookup row is not found.
begin returns decimal;
if ( LookupDatabaseTable('qatest.dbo.ORD', 'ORDER_ID', 'COST', tostring("ORDERID") ) == '?')
return 999.99;
else
return todecimal( LookupDatabaseTable('qatest.dbo.ORD', 'ORDER_ID', 'COST', tostring("ORDERID") ));
end;