Oracle Business Intelligence

Products Banner

Losing left padded zeroes in queries migrated to 12C


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

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:

pastedImage_1.png  pastedImage_2.png  pastedImage_3.png

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)

pastedImage_4.png                          pastedImage_5.png                     pastedImage_1.png  pastedImage_8.png

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.