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.
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.