User Defined Functions ( Oracle ROWNUM )

[angiole]

Good Afternoon,

Has anyone successfully implemented the ROWNUM feature for Oracle queries into the User Defined Functions.

I can't more specific into why it doesn't work here except to say that even when I get it to appear in the report functions list, I can't use it.

Thanks in Advance.

AA 8~)

 

[griffindm]

I haven't used it myself, but Cognos has this on their knowledgebase from Impromptu version 5:

KB article: 83542

Description:

I am working with an Oracle database. Is it possible to transform a list report to display the values of one column on one line?

Example:
Type Line Products
Outd Sport Bag
Ski
Skate

Type Line Products
Outd Sport Bag Ski Skate

Solution:

1. Create a UDF to use the ROWNUM Oracle pseudo-column with Impromptu:
- In Cogudfor.sql add the following lines:
DECLARE DATABASE FUNCTION rownum
RETURNS NUMBER
FUNCTION NAME rownum;
- In Orfunct.ini add the following lines:
rownum=
[rownum]
label=row number
param=0
return=NM
exp=rownum ( )
tip=Syntaxe : row number ()
- delete Orfunct.fdt

2. In a list report, insert the Type, Line, and Products columns, as well as the following calculation:
mod ( row number ( ) ; count ( Product ) for Line )
This will return a number between 0 and the number of products per line minus one.

3. Execute the report. As we do not need this data to be displayed, we can hide it.

4. From the View menu, click Show Hidden, drag the calculation just above the column headers to transform the report into a crosstab. Uncheck Show Hidden.
This crosstab has as many columns as the maximum number of products per line (4 in Great Outdoors). These columns display the products in line :
Type Line
Outd Sport Bag Ski Skate

As I said I haven't tried this, but I would make a copy of Orfunct.fdt before I deleted it. No guarantee as to how it will work with newer releases of Impromptu either.


Hope this helps,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
"Magic with Data"

http://www.decisionsupportgroup.com

 

[angiole]

Thanks again Dave,
My current assignment doesn't have the access to Cognos Support, so you're including the code was good foresight ... AA 8~)