Losing left padded zeroes in queries migrated to 12C

Jerry Casey

I wanted to share what was an unexpected (at least to me) behavior that I encountered after our upgrade from OBIEE 11.1.1.9 to 12.2.1.3.0.

In both 10G and 11G, I created a union to bring in a column that existed in a different data model than the one I was working in.  This would require a placeholder in the original query, which I would populate with “cast(null as char)” or “cast(null as double)”, depending on the data type of the column being brought in.  Then, I’d use a result column in a pivot table to “unify” the records.  It would look like this:

   

The 10G and 11G results looked like this:             After migrating to 12C, it produced this:             If I simply reverse the order of the union, I get the correct results.

(this is a 7 character, left-padded field)

                                                

I'm posting this as a question to get the attention of anyone that like me, is dealing with hundreds of older analyses migrated from 10G to 11G to 12C.

I know that 12C now supports varchar in the front end, and that the method described doesn't represent best practices.  I was just surprised by the effect of reversing the order.