I created a query in Oracle SQL Developer using temp tables.
Now I want to use this query in Cognos Analytics to create a report.
I add a query and choose SQL.
Then I choose SQL syntax: Pass-through
I copy the query into the sql statement and validate the query.
Then Cognos gives an error message: The server prompting has failed. The request was cancelled.

Is there a(nother) way to use a sql statement in Cognos analytics?

Thanks for the reactions.

------------------------------
Hein van den Oetelaar
------------------------------

#CognosAnalyticswithWatson

Make sure you do 2 additional things (if you haven't done so already)

1. Add a Package to the report - There needs to be a Package even if it is made up entirely of User Defined SQL.
2. Add a Data Source to the SQL.
   In the Query View, select the SQL and then select properties - You need to define a Cognos source in this Properties pane.

Hope this helps,
Adam.

------------------------------
Adam McIlravey
------------------------------

Thanks for the reply, but I'm afraid this is not the problem.
When I create a simple sql based query on the same way it runs without problems.
However when I Create and drop temp tables in the query (and update them in the meanwhile), I receive the error message.

------------------------------
Hein van den Oetelaar
------------------------------

Original Message:
Sent: Mon November 23, 2020 12:23 PM
From: Adam McIlravey
Subject: using oracle sql temp table

Make sure you do 2 additional things (if you haven't done so already)

1. Add a Package to the report - There needs to be a Package even if it is made up entirely of User Defined SQL.
2. Add a Data Source to the SQL.
   In the Query View, select the SQL and then select properties - You need to define a Cognos source in this Properties pane.

Hope this helps,
Adam.

------------------------------
Adam McIlravey
------------------------------