Darren Wallace (8/5/2011)

---

savedanthony (7/28/2011)

---

I am trying to use SQL to transfer a prompt list of values into a column that I can join with values in a cognos table in order to have a full report rather then needing to do external work in excel or somewhere outside of cognos. Here's what I have so far for my sql:

select

(#prompt('List', 'String')#) as Data_Item1,

(#sq(csv( split(', ', prompt('List', 'token') ) ))#) as Data_Item4

from

MAXIMO_DW..MAXIMOCGNS.ASSET C__wo__ASSET

where

(C__wo__ASSET.ASSETNUM = 'LC061808' or C__wo__ASSET.ASSETNUM = 'MA3004')

This will bring back the prompted value as one entry as entered (data_item1) or with extra single quotes on each entry (Data_item4). Now I have seen functions that will do what I want, but I don't know how to integrate them into this setup. The purpose for this setup is it atleast passes and gives me an outcome. The other functions I have tried only give me an error that I can't figure out. So, any help would be greatly appreciated. Thanks

The way I've done something similar to this (note I have Cognos 8.4 and SQL Server so you may have to change the code to work in ORACLE) is:

1) Create a table valued function (TVF) in SQL Server that accepts a comma separated string and parses it out to a table. There are many good ways to do this (see the Tally Table option presented previously) but I've used the attached funtion -ufn_Split.txt. It's not perfect or robust but works well for the limited set of circumstances I use it for (no embedded commas in the data and a not terribly large data set) and it's obvious enough that if I'm hit by a bus I don't worry much.

2) Create a Query Subject in Framework Manager. Pass the cognos macro value (#macro stuff#) into the TVF and get back the table.

SELECT *

FROM dbo.ufn_Split (#CSVIdentityNameList(',')#)

This really belongs in a Cognos forum since the complexity is all in Cognos and it's macro handling oddities rather than SQL Server but since I was here...

-Darren Wallace