We would like to know how to handle NULL values returned by the db_lookup function. We have the following expression, but we don't know if there's a polish way to do it:
IF DB_LOOKUP( dim_producto, PROD_ID, PRODUCTO_ID_NK, PRODUCTO_SK ) GE 1 THEN DB_LOOKUP( dim_producto, PROD_ID, PRODUCTO_ID_NK, PRODUCTO_SK ) ELSE -1
Thanks in advance.
Leonardo.This message has been edited. Last edited by: Tamra,
From the Using Functions reference manual:
If the lookup field has the MISSING=ON attribute in its Master File and the DEFINE or
COMPUTE command specifies MISSING ON, the missing value is returned when the
lookup field is missing. Without MISSING ON in both places, the missing value is converted
to a default value (blank for an alphanumeric field, zero for a numeric field).
Well, my question concerns when the db_lookup function returns NULL, how should I handle that NULL value (missing value)? I tried to find an NVL function or similar and I didn't. All our Oracle targets don't allow nulls, hence I shouldn't select MISSING option in the master.
You can check for a NULL value with syntax like
IF DB_LOOKUP( ... ) IS MISSING THEN ...
MISSING is the WebFocus keyword for NULL. But you have to explicitly look for it (MISSING=ON). Otherwise they kindly turn a NULL into a zero or a space. Dan gave you some key information for turning it on so you can use it. Clif told you how to use it.
WebFOCUS 7.7.05 (Someday 8)
Windows 7, All Outputs
In Focus since 1983.
If your Oracle targets don't allow nulls (and therefore MISSING isn't ON in the synonym), then the DB_LOOKUP function should return either a value, or a blank (if the field is alphanumeric and no match is found), or a zero (if the field is numeric and no match is found).
For alphanumeric field:
IF DB_LOOKUP(...) EQ '' THEN ....
For a numeric field:
IF DB_LOOKUP(...) EQ 0 THEN ....
|Powered by Social Strata|