Temporary Table in Native / Pass-Through SQL

[fhouser]

Hi Cognoise community, long time lurker, first time poster. :D

Here's my question:
Does Cognos (10.x) support the creation of temporary tables via Native or Pass-Through SQL?

Please note this is a query directly to the Database. There is no Framework Model.

Currently, it is throwing this error: ORA-24333: zero iteration count

I am converting old reports from a legacy system to Cognos.

In the SQL for this report generation, there exists a "CREATE" table statement. (I've cleaned out the private information in the SQL below)

Thanks in advance for your help and time!

Begin SQL:

create table DummyTemporaryTable nologging as
select   a11.column1,
   a11.column2,
   a11.column3,
   a11.column4,
   a11.column5,
   a11.column6,
   a11.column7
   
from   Schema.Table a11
where   ((a11.column1 = 'String' and a11.column2= 'String' and a11.column3 = 2012)
and a11.RPT_PERIOD_DT = 201203

group by   a11.column1,
   a11.column2,
   a11.column3,
   a11.column4,
   a11.column5,
   a11.column6,
   a11.column7

[bdbits]

I've never tried to perform multiple SQL statements in a report, especially with DDL. My guess would be that it is complaining it cannot determine the result set, which a reporting engine would of course expect to be receiving. What is your data source? Can you push the SQL into a stored procedure and call that?

Why no model? It does not take all that long if all you do is make a single database layer and use that for presentation.

[Tmdean]

I found a way to do this after trying to figure it out for a while.

There are two things you have to work around: Cognos will interpret the #s as the start of a macro command, and when you use temp tables, SQL Server has trouble identifying the metadata of your query before executing it.

Code Select Expand

SET QUOTED_IDENTIFIER ON;

CREATE TABLE "#test" (x int PRIMARY KEY);

EXEC('
    INSERT INTO "#test" (x) VALUES (1), (2), (3);
    SELECT x FROM "#test";
') WITH RESULT SETS ((x int NOT NULL));

To get around the first issue, turn QUOTED_IDENTIFIER on, so that you can quote the temp table name wherever you use it. Cognos for some reason won't expand macros if they occur within quotes. (This is one of Cogno's many undocumented "features" that ordinarily would drive me nuts but turns out to be surprisingly helpful here.)

The second issue can be worked around by dynamically executing the SQL code that uses the temp table and explicitly declaring the result type using WITH RESULT SETS. There shouldn't be any increased risk of SQL injection, but to be on the safe side, you should keep any macro commands that expand user input outside of the dynamic SQL.

Edit: sorry, just realized you're using Oracle. I hope you can find a way to adapt this solution to Oracle. The bottom line is you need to give the database a way to tell Cognos what the result set type is.