Query get CLOBed?
Posted by jeggent on October 9, 2015
Disclaimer: This may only apply to Oracle databases.
I think my favorite PeopleSoft Query error occurs when you are outputting a CLOB field and try to use an aggregate or DISTINCT.
inconsistent datatypes: expected – got CLOB
The messages always makes me think, “Got Milk?” The two fields that I seem to run into this the most often with are 3C comments and checklist item Long Description. In PS Query the fields show as “Text” format, but behind the scenes the content is stored in what is called a CLOB field. You don’t really need to know the details about how CLOBs are stored in the database, you just need to know when it’s a problem with PS Query and what to do about it.
It seems that you can output a CLOB (Text) field in PS Query just fine. However, if you mark your query as distinct or make any fields an aggregate (count/sum/etc), you will get the above error message. The workaround is to create a “Character” type expression, use the TO_CHAR function, and specify a length. Our checklist item descriptions can be pretty long, so I’ve used a length of 900 in the example below. You then use your expression as a field instead of the CLOB field from the record. I hope this helps and maybe solves the mystery of the weird CLOB error. – Cheers, John
