July 28, 2011 at 11:32 am
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
August 3, 2011 at 7:53 am
I guess it's too hard to do. 🙁
August 3, 2011 at 8:05 am
August 3, 2011 at 9:26 am
Thanks, but that doesn't work for me which is why I asked for help. I was hoping to find a person that knows SQL, Cognos, and willing to help. I guess I didn't find that person here.
August 3, 2011 at 9:31 am
Not me anyways.
I'll see if I can reping this...
August 3, 2011 at 9:48 am
savedanthony (8/3/2011)
Thanks, but that doesn't work for me which is why I asked for help. I was hoping to find a person that knows SQL, Cognos, and willing to help. I guess I didn't find that person here.
I am curious as to what makes up your datasets for your cognos reports (or is this for the matrix?).
What version of Cognos (7,8 or other)?
Also, more understanding of how this is setup is crucial. Cognos doesn't really have tables - the tables are in SQL. Cognos provides the presentation to those database tables in whichever DBMS you use.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 3, 2011 at 10:32 am
@ Ninja's_RGR'us
I am confident it will work if your putting into an SQL processing program. I am also confident it will work for me if I do the same. But I don't have the same. And all I get is errors were found do you want to continue? Yes or No.
I am using Cognos 8.4 with an oracle database. The ultimate way I want to use the code, it shouldn't pull anything from my database yet. That will come when I join it to my database query later. The goal I am trying to do now is to take a list input from a user, and turn it into a one column table that has each entry as a seperate row. This would then be joined with the database result from the same input so that values that aren't in the database can be seen by the user as not in the database. And the person that requested the report can see and confirm that they put in the right input it's just not there or oops that should be hello not helo. If your inputting 500 items and you get 499 it's a long process to see which one wasn't there. Hope this helps and thanks so much!
August 3, 2011 at 10:36 am
Never used cognos so I can't help.
August 3, 2011 at 10:41 am
K I see what you are trying to do. IMHO, I would use some other app to do this. Since you are trying to do it strictly within Cognos (assuming it is within report manager) I would recommend checking the Cognos forums at IBM to see if somebody has a workable solution. I have always used SQL Server as the backend for all datasets in cognos and wouldn't know where to begin with this one (combo of oracle backend and only wanting the dataset to live within memory from report manager).
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 4, 2011 at 7:35 am
It's just hard to find someone with the knowledge in both systems. But thank you both for trying.
August 4, 2011 at 9:24 am
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[/url]
Learn Extended Events
August 5, 2011 at 12:05 pm
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
August 5, 2011 at 1:47 pm
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[/url]
Learn Extended Events
September 14, 2011 at 2:10 pm
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.
September 14, 2011 at 2:56 pm
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
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply