Pass Cognos parameter to Manual SQL

[dbe123]

Hi,

Anyone can help me passing a Cognos Report Studio parameter (type string) to an SQL statement in the backend?

I found some information on this, on the www, but unfortunately, it doesn't work.

This is what I have so far:

CASE
WHEN #promptmany('Parameter1')# IS NOT NULL
THEN
SELECT * FROM table WHERE field = #promptmany('Parameter1')#
ELSE
SELECT * FROM table
END

Our database system is Oracle, the Cognos BI version is 8.4
The error I get is 'Invalid SQL statement'

Thanks in advance

 

[blom0344]

That is not a valid CASE statement. The WHEN and ELSE should return a value, not a DML statement. You can use imbedded SQL strings in a stored procedure but not in SQL itself.


Ties Blom