Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Convert comma seperated list into one column (Cognos) Expand / Collapse
Author
Message
Posted Thursday, August 4, 2011 9:24 AM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:17 PM
Points: 18,081, Visits: 16,116
savedanthony (8/4/2011)
It's just hard to find someone with the knowledge in both systems. But thank you both for trying.


Sorry we couldn't help more.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1154330
Posted Friday, August 5, 2011 12:05 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, November 14, 2013 3:13 PM
Points: 306, Visits: 1,458
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


  Post Attachments 
ufn_Split.txt (9 views, 1,011 bytes)
Post #1155287
Posted Friday, August 5, 2011 1:47 PM


SSCoach

SSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoachSSCoach

Group: General Forum Members
Last Login: Yesterday @ 5:17 PM
Points: 18,081, Visits: 16,116
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


Thanks for chipping in.




Jason AKA CirqueDeSQLeil
I have given a name to my pain...
MCM SQL Server, MVP


SQL RNNR

Posting Performance Based Questions - Gail Shaw
Post #1155340
Posted Wednesday, September 14, 2011 2:10 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 3, 2011 3:01 PM
Points: 7, Visits: 34
Hey, I did find a couple of steps from some coworkers. Basically I found that I could use the format:

SELECT (substr(#prompt('List', 'String')#,1,instr(#prompt('List','String')#,', ')-1)) Asset FROM Dual Union
SELECT (upper(#prompt('List', 'String')#) || ', ') Asset FROM Dual Union
SELECT 'Last' Asset FROM Dual

and basically add as many things as I want as long as the last option doesn't have union on it. So, the next part I am confused on is how to use an if then or case statement to decide what to select. For example the code above works, now ideally I would like to use:

if #prompt('Choice','String')# = 'Yes'
then
SELECT (substr(#prompt('List', 'String')#,1,instr(#prompt('List','String')#,', ')-1)) Asset FROM Dual Union
SELECT (upper(#prompt('List', 'String')#) || 'Test') Asset FROM Dual Union
SELECT 'It worked' Asset FROM Dual
else
SELECT (substr(#prompt('List', 'String')#,1,instr(#prompt('List','String')#,', ')-1)) Asset FROM Dual Union
SELECT (upper(#prompt('List', 'String')#) || 'Test') Asset FROM Dual Union
SELECT 'Some Text Data2' Asset FROM Dual end

I don't know the syntex for using the if then test to decide what to select though.
Post #1175268
Posted Wednesday, September 14, 2011 2:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 3, 2011 3:01 PM
Points: 7, Visits: 34
basically the logic of this code is what I need into the format of an sql select query:

declare @start as int;
declare @end as int;
declare @entries as int;

set @start = 1;

set @entries = (length(#prompt('List','String')#) - length(replace(#prompt('List','String')#, ',')))+1;

for i = 1 to @entries

@end = instr(#prompt('List','String')#,', ',@start)-1;

if i = @entries

then
SELECT (substr(#prompt('List', 'String')#,@start,@end)) Asset FROM Dual

else
SELECT (substr(#prompt('List', 'String')#,@start,@end)) Asset FROM Dual union

end

@start = @end + 2;

next

Post #1175301
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse