What does COALESCE() function do and how is it used in BO?

building reports desktopintelligence
1

I need to know COALESCE() function in detail, how is it helpful in creating LOVs and in what cases do we use it.

Thanks in Advance.

bounty (BOB member since 2007-02-16)

2

Welcome to B:bob:B!

COALESCE function can take N arguments and returns the value of the first of them that is not null.

Basically COALESCE(argument1, argument2, … argumentN) is equivalent to:

CASE
   WHEN (argument1 IS NOT NULL) THEN argument1 
   WHEN (argument2 IS NOT NULL) THEN argument2 
   ...
   WHEN (argumentN IS NOT NULL) THEN argumentN 
   ELSE NULL
END

More about the function here.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

3

Thanks much Marek, In existing universe this function is used in LOV’s as:-

COALESCE(XYZ.CUS_DIMENSION.REGION_NAME,‘Unassigned’)

can you plz explain its significance.

There are two objects fetched in LOV, and the sql query is :-

SELECT DISTINCT
XYZ.CUS_DIMENSION.NATIONAL_ACCOUNT_NAME,
COALESCE(XYZ.CUS_DIMENSION.REGION_NAME,‘Unassigned’)
FROM
XYZ.CUS_DIMENSION
ORDER BY
2,
1

Thanks again.

bounty (BOB member since 2007-02-16)

4

It simply means that if the region value is missing or null, the value “Unassigned” will be displayed in its place.

Dave Rathbun :us: (BOB member since 2002-06-06)

5

Hey Dave…Even I feel that blanks should be replaced by “Unassigned” in such a case, however it doesn’t happen.
I tried to create a new object and changed its LOV sql to :-

SELECT DISTINCT
COALESCE( XYZ_SCHEMA.CUS_DIMENSION.ACCOUNT_NAME,‘Unassigned’)
FROM
XYZ_SCHEMA.CUS_DIMENSION

still I am getting nulls and Unassigned is nowhere…

bounty (BOB member since 2007-02-16)

6

blanks != null

The coalesce() function only operates on NULL or missing values. Having blanks (spaces) in the field means that it’s not null. If the field is defined as CHAR as opposed to VARCHAR it will typically have spaces in it.

Try to determine if you truly have null values, and then go from there.

Dave Rathbun :us: (BOB member since 2002-06-06)

7

ankit_gupta8,

Try the following:

COALESCE( TRIM(XYZ_SCHEMA.CUS_DIMENSION.ACCOUNT_NAME),'Unassigned')

TRIM() should get rid of spaces.

Marek Chladny :slovakia: (BOB member since 2003-11-27)