Writing SQL in Report studio "SQL object"

[Sunchaser]

Dear all,
Usually, I'm using the Query object in report studio as "Native SQL" (for some particular reason) and write the select statement inside.
I would like to know if it's possible to write more than the select statement, like declare a variable, give a value to this variable and then the "classical" select statement.
I mean something like:

Code Select Expand


var my_var clob;
exec :my_var := 'A value';

SELECT *
FROM TABLE( my_function(:my_var) )


For the moment, it's giving me an error that I can't solve but is there any work around about that ?

Thanks.
vinc.

[Evorus]

Vinc,

Working from a Data Manager point of view (and I may be wrong) but when you are writing SQL within Cognos, if you use the ; then it believes that to be the end of the SQL.

Something like this works within a SQL Node on Data Mnager

Code Select Expand



CREATE FUNCTION [dbo].[GetNumberFromData]

(@data VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS

BEGIN

DECLARE @pos BIGINT,@char CHAR(1),@rightside VARCHAR(MAX)

SET @pos = 1
WHILE (@data like '%[^0-9]%') AND @pos <= LEN(@data)

BEGIN
SET @char = SUBSTRING(@data, @pos, 1)
IF @char like '%[0-9]%'SET @pos = @pos + 1

ELSE

BEGIN
SELECT @rightside = SUBSTRING(@data, @pos + 1, LEN(@data)),@data = CASE WHEN @rightside like '%[0-9]%' THEN
LEFT(@data, @pos - 1) + @rightside

ELSE
LEFT(@data, @pos - 1)END

END

END

IF @data like '%[^0-9]%' OR LEN(@data) = 0

SET @data = NULL

RETURN LTRIM(RTRIM(@data))
End;




You just have to watch the ;

Hope this helps in some way.

Evorus

[Sunchaser]

Hi Evorus,

Thanks for your interest in my problem.
So, no sorry, nothing changed in report studio, I still have the error:

UDA-SQL-0107 A general exception has occurred during the operation "prepare".
ORA-24333: zero iteration count

and I don't know what to do with it at this level.

[blom0344]

Usually, I'm using the Query object in report studio as "Native SQL" (for some particular reason) and write the select statement inside.

Pardon for asking, but why do you use a premium BI tool when not using the modelling that goes with it?  ???

[Sunchaser]

Hi blom0344,
I don't know what to answer to you, as I don't know or understand what you mean.
If you have a clear example that could show me how to work with my table functions in Framework Manager, and that will solve my problems, I would say "great!!" and take it of course.
So, now, maybe the next question will be why are you working with table function for your SQL ?
I don't know if I describe the reasons it could help, but if yes, I will.
But to be clear, if the kind of statement I'm trying to write in report studio is possible to do in FWM (considering that the variable "my_var" in my example should take the value of a prompt), then of course I'm 100% interested in some example.

Thanks.
vinc.

[blom0344]

I guess that Cognos 8 was meant to be driven by static SQL. The metadata model drives the Cognos SQL and the server translates the Cognos version to a native one. While underway it offers a load of niceties like prompts etc to make life more pleasant.

It does however not have a compiler for dynamic SQL and procedural languages. (at least no to the point you seem to need)

What puzzles me is why you need a procedural approach if you can build complex static queries including pre and post filtering, a host of prompts, set based operators like unions etc.

I have known some programmers that switched to BI but kept thinking in procedural instead of set-based solutions. And then working with a metadriven BI tool will stay an awkward procedure.

So , why do you need what you are attempting to build?

[Sunchaser]

Hi blom0344,

Thanks for your answer.

One of the reasons  - I think - why I'm not simply using tables or views imported in Framework manager "in a classical way" is that one of the requirements was that all the SQL used to retrieve data must be obfuscated. And, as far as I know, I can't obfuscate the SQL of a view for example, only functions procedures, types (in oracle) etc ...
And other one is that these SQL are pointing on encrypted tables in addition to "classical" tables, so using functions and/or procedure the data needed from these tables are decrypted first (put in temporary variable, the tables are not modified) and then used in the rest of a SQL statement.
One more reason may be that I need (from requirements) to update/insert some tables each time the data are called (depending on some variables).
Last thing I could say for the moment, we are using a "row level security" for every single data called, based on the "user account" / encrypted data, etc ... It's also possible with oracle to know for example which process is calling data, and it is points that we are using too.

For the prompting side, I built some "web interface" in order to have "advanced prompt capabilities".

So, many actions before retrieving data, and I don't know if everything could have been possible in a "classical way". But I might be wrong ... of course.
Last point about that: this "model" is still working, and working correctly, with no particular problem on the performance side.

But, of course, life is not easy and now I would like to make a test to change something particular to get more flexible on some parts and It seems that I'll have problems to achieve it for a long time.

In the test I made, using native or pass-through sql, I was just expecting cognos to ask to the database "dear DB, you would please execute this statement", and then the database will send back the data or an error if there is one.
But Cognos doesn't seems ok with my requirement ... ;-)  and I surely don't know enough of how Cognos really process queries from report studio to the DB.

[blom0344]

Okay, that quite an impressive challenge.
I recall many years ago - with another BI-tool - a session about bi-directional reporting. The user would then have the ability to write back to the DB instead of just reading (obviously under secure conditions) AFAIK nothing much has been developed in that direction, probably cause most vendors where occupied with getting web-enabled versions on the market.
I would say though that best-practice Cognos C8 modelling creates the sort of SQL that is almost obfuscated by nature  ;D