Concatenation does not work if one element is null 1

[nileshcssi]

I am concatenating two or more data elements (VARCHAR2's) using a formula like this:

    {DB_TABLE.DATA_1} + {DB_TABLE.DATA_2};

When I use this formula, if one of the data elements is null, the entire result of the concatenation is null, even if one of the elements is not null.

The only thing that I have found that works is the following bit of code:

if isNull({DB_TABLE.DATA_1}) then
    {DB_TABLE.DATA_2};
if isNull({DB_TABLE.DATA_2}) then
    {DB_TABLE.DATA_1}
else
    {DB_TABLE.DATA_1} + {DB_TABLE.DATA_2};

However, I have many fields where I have to apply this, and don't want to implement this piece of code, as it is a bit of a cludge, unless it is my only option. This code will get much worse when I have more than 2 elements to concatenate. Any advice?

 

[Ngolem]

you don't have too many options here....

Crystal chokes on Null values so you must generally test for them as you have done in most cases

HOWEVER, You can in the report options have the Null's set to a string default .... say " " - a single space.

Advantage
---------
1. This will remove any possiblity of having a formula fail due to a null

Disadvantage
------------

1. This default is not field specific - ie. if you want to handle nulls differently for different fields this will not do it.

2. You will never know when you are missing data in your database....this is not a big problem with strings but with numbers typically the default is set to 0 (zero) and zero may be a perfectly valid result in the database...so you will never know when it is a true zero or artificial.

But changing string nulls to a single space by default would seem to work for you


Jim Broadbent

 

[synapsevampire]

Consider creating a SQL Expression containing something like:

isnull({DB_TABLE.DATA_1},"&quot + isnull({DB_TABLE.DATA_2},"&quot

This buys you 2 things, the database does the processing (faster), and you can leave the default of null on other columns.

If you decide to just set all null values to a default instead, use:

File->Report Options->Convert Null Values to Default

-k

 

[kenhamady]

Another option is to write a single formula for each field, and then concatenate the formulas. Each would be:

if isNull({DB_TABLE.DATA_1})
then ""
else {DB_TABLE.DATA_1};


Or, instead of concatenating the fields, you could drop them into a text object to combine them. This is not affected by Null values.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com