I have a requirement in in which I would like to call a stored procedure from Cognos, passing an ID
to the SP. The SP would retrieve data from a table and then loop through the data, formatting it.
Here is the basic idea:
Cognos calls SP with ID "ABC100".
SP retrieves following data:
100 Julus Caesar
100 the Rubicon
200 Santa Claus
200 lives in the Bahamas
and formats it as follows:
100 Julius Caesar crossed the Rubicon
200 Santa Claus lives in the Bahamas
These last two lines would be returned to Cognos to be displayed in a report. The actual task as hand is
far more complex, with multiple nested loops and various lines to be concatenated, but essentially a set
of 1:n lines would be returned to Cognos.
Has anybody done this and can steer me in the right direction, or alternatively can say it cannot be done
(thereby saving me the time and effort of trying it)?
Of course you could do it with a SP, but there might be much simpler ways to do it.
How do you get the rows to be concatenated?
Can you elaborate on the "far more complex, with multiple nested loops" part?
The data I am dealing with is historical transaction data. From a set of vertical lines I have to
produce a horizontal line where the data from the vertical lines are concatenated. Ultimately I want to produce an answer set and build up a report output that is ready to be displayed (in Cognos).
What I have are transactions, and each transaction can have multiple categories and sub-categories. For each new transaction, I pull out the time and user info from the first line, then step through the categories. Each sub-category will yield one line, but within the category I can have four different types of output line. It is a classical task for a procedural language, with at least three nested loops and building up output lines by concatenation based upon different criteria. Here is a simplified example, omitting the transactions, categories and sub-categories:
Example of data in SP
locn time type txn cd new old user id
Car 1 11.01.2015 15:23:40 add last name smith P100F
Car 1 11.01.2015 15:23:40 add first name john P100F
Car 1 11.01.2015 15:23:40 chg doc type passport id card P100F
Car 1 11.01.2015 15:23:40 del service tablet P100F
Car 1 11.01.2015 15:23:40 add gender Male P100F
Car 1 11.01.2015 15:23:40 add DOB 11.07.1977 P100F
Car 2 11.01.2015 15:23:40 chg Title Mr Mrs P100F
Car 1 11.01.2015 21:11:02 del Gender: Male AB200
Example of output
Time: 11.01.2015 15:23:40 User: P100F
Car 1 Added: Last Name = Smith First Name = John DOB: 11.07.177 Gender = Male
Changed: Document ID Card --> Passport
Deleted: Service Tablet
Car 2 Changed: Title Mrs --> Mr
Time: 11.01.2015 21:11:02 AB200
Car 1 Deleted: Gender Male
I considered using arrays to build up the output, but I still have to step through each row and analyze if a break has taken place and where to add the information. The logic in the SP is not so much the problem as how to get the results back to Cognos so it doesn't even know something happened in the background.
Rats - sorry about that mess in the previous post. When I posted it, it was a nicely organized table, which was turned into a ghastly one-column post.
What's your TD release? Are XML services installed?
There's a nice XMLAGG function for concatenating multiple rows into one:
create volatile table vt (
) on commit preserve rows;
ins vt('Car 1',timestamp'2015-11-01 15:23:40','add','last name' ,'smith' , '' ,'P100F');
ins vt('Car 1',timestamp'2015-11-01 15:23:40','add','first name' ,'john' , '' ,'P100F');
ins vt('Car 1',timestamp'2015-11-01 15:23:40','chg','doc type' ,'passport' , 'id card' ,'P100F');
ins vt('Car 1',timestamp'2015-11-01 15:23:40','del','service' ,'' , 'tablet' ,'P100F');
ins vt('Car 1',timestamp'2015-11-01 15:23:40','add','gender' ,'Male' , '' ,'P100F');
ins vt('Car 1',timestamp'2015-11-01 15:23:40','add','DOB' ,'11.07.1977' , '' ,'P100F');
ins vt('Car 2',timestamp'2015-11-01 15:23:40','chg','Title' ,'Mr' , 'Mrs' ,'P100F');
ins vt('Car 1',timestamp'2015-11-01 21:11:02','del','Gender: Male' ,'' , '' ,'AB200');
SELECT time_, locn,
when type_ = 'del' then 'Deleted: '
when type_ = 'add' then 'Added: '
when type_ = 'chg' then 'Changed: '
when type_ = 'del' then txn_cd || ' ' || old_
when type_ = 'add' then txn_cd || '=' || new_
when type_ = 'chg' then txn_cd || ' ' || old_ || ' -> ' || new_
GROUP BY 1,2,type_
order by 1,2,type_
We're on 14.10. XML services are not installed. We had them on 13.10, but they were not reinstalled after the upgrade. I'll request they be installed and try out the XMLAGG function. The example looks promising, and even if I cannot use it here, it never hurts to have tried it.
If I do have to go with a SP, I still do not know how or if it is possible to return a result set to Cognos. Any idea if this is possible or should I look for another solution Iif XMLAGG does not wok out)?
just a short update - and a big thank you to you Dieter for the suggestion - that the XMLAGG does an excellent job of concatenating the different rercord types. The output I now have substantially reduces what I previously had (e.g. ten lines reduce to two). As opposed to arrays, XMLAGG is a piece of cake to use.
Now that I have an output I can work with, the next thing to try will be using this as input to a SP which writes to a volatile table. Embed the SP and a subsequent select on the VT in a macro and let Cognos call the macro. That hopefully wil return a set of records Cognos can display. Even if that fails, I am far ahead of where I was yesterday.
Thank you once more for the suggestion.
You need to return a single answer set to Cognos?
Why don't you do it directly from the SP using DYNAMIC RESULT SETS, afaik Cognos supports one result set returned by a SP (I don't know if it can handle multiple Selects in a macro):