OBIEE: Gotcha #5

Or the CHAR edition.

A report writer asked me why this report isn't returning any rows. So I brought up the report, ran it, then grabbed the SQL, when I saw this:

AND concat( concat( T364349.DEV_CD, ' - ' ), T359839.DEV_DESC ) = 'DLC        -Developer conflict'

Yeah, that's a bunch of spaces between "DLC" and the "-". Most likely the cause of the problem.

Took a look at the database and sure enough, it was defined as a CHAR(10).

This was already a "view" in that it was a stored SQL statement or Table Type of "Select".

I then added the TRIM function to both the code and the description. I didn't bother to test it because surely, this would work.

Later in the evening I went to the report to see if I could help with a different problem. No data was coming up. I opened it up to all time ranges and all lines of business...nothing.

I asked the dev if he had changed anything today. Nope.

Hmm...

I can "View Data" on the table (with the TRIM). I went ahead and removed it anyway. Now the report works. WTF?

Repeated this process a couple of times just to make sure and that was it. The TRIM function in the SELECT statement was causing issues. No warnings or errors though.

I guess the good news is I learned how the TrimTrailing function in OBIEE works now. It's not just:

TRIMTRAILING( column_name )

like I would have thought, it acts more like a substring:

TRIM( ' ' FROM column_name )

I just love these little things that can drive you nuts.