Vender specific function not supported

[hespora]

Hi there,


(C10 on Oracle relational)

Does anybody know what types of things force Cognos to execute a query as IBM Cognos SQL rather than native? I have a report that returns XQE-PLN-0098 "The vendor specific function "lpad" is not suppoerted." - I have a different report where I can use that Oracle-specific function just fine.

[hespora]

nvm, found it. in this case it was a manual aggregate function "maximum( [xxx] for report )"

[Lynn]

nvm, found it. in this case it was a manual aggregate function "maximum( [xxx] for report )"

Is your package CQM or DQM (Compatible or Dynamic query mode)? I feel like this happened to me more with CQM.

The root cause (I think) is when local processing is required. A vendor-specific function must be processed on the database, not locally by the Cognos server. If there is an equivalent Cognos function then using that syntax rather than vendor-specific syntax will give you less pain. I don't think there is one for lpad, but Oracle SUBSTR vs. Cognos SUBSTRING syntax might be a relevant example. It has been awhile since I've used Oracle so take that FWIW  :D

[hespora]

Is your package CQM or DQM (Compatible or Dynamic query mode)? I feel like this happened to me more with CQM.

I..... have no idea. How do I tell? I'm a mere report author with no FM access.

The root cause (I think) is when local processing is required. A vendor-specific function must be processed on the database, not locally by the Cognos server. If there is an equivalent Cognos function then using that syntax rather than vendor-specific syntax will give you less pain. I don't think there is one for lpad, but Oracle SUBSTR vs. Cognos SUBSTRING syntax might be a relevant example. It has been awhile since I've used Oracle so take that FWIW  :D

Yea, exactly that is my problem - there's no oracle equivalent for cognos's aggregate functions, and there's no cognos equivalent for oracle's lpad, only workarounds. Those do work, but if you stumble across one in a report you created half a year ago, the first three letters that come to mind are WTF.... :D

[Lynn]

Navigate to the package in Cognos Connection and open the properties. On the "General" tab towards the bottom you will see "Query Mode" and either "Compatible" or "Dynamic". This is available for 10.2.2 although I'm not certain about earlier v10 releases.

I feel your pain! I had this issue often in a previous role but not so much any more. In my current situation we have a proper Kimball star schema data warehouse and DQM packages from a Framework Model that also follows best practice recommendations. These two things combined mean the need for these types of functions is minimal. I suspect that DQM plays a role as well, although I'm not sure, which is why I was curious about your situation.

[pogacsa]

Hi All, I'm having the same problem, but not when using a package as source. With that, even in "compatible" query mode, all is fine. But as soon as I switch to using a data module, I get the "not supported" error message. Has anyone solved this one?