Creating rows from a prompt
Here’s an interesting request. A developer wants to populate a query set using a prompt. The values don’t exist in a table, so a simple filter won’t work. How can we get the query to have the rows? Turns out there’s a really easy way.
Cognos SQL is based on the SQL-92 standard, which gives us a few very nice features. One of them is the values() keyword.
values() let’s us define a set, sort of like a union query. Plenty of info here: https://modern-sql.com/feature/values.
An example query might be:
SELECT
parameterValue
FROM (VALUES
('Scenario 1')
, ('Scenario 2')
, ('Scenario 2')
) query(parameterValue)
That would create a list with 3 rows, Scenario 1-3.
So how can we use this in Cognos? A prompt macro with a split and join works wonders.
SELECT
parameterValue
FROM (VALUES
(#join('),(',split(',',promptmany('Scenarios','string',sq('N/A'))))#)
) query(parameterValue)
The values passed to the parameter will appear as ‘Scenario 1′;’Scenario 2′;’Scenario 3’, the join/split functions will effectively replace the semicolons with ),(. This ultimately makes the SQL match the standard.
Check out the report here: params-to-rows.txt (5092 downloads)
Hi Paul,thanks for sharing your precious tips and experience, you are fantastic!
i’m trying to extend this macro in a way that make it possible to have multirows and multicolumns values inserted with values operator and macros but no i had no fortune…
it’s possible?how?
thx
The SQL would look something like:
select value1, value2
from (values(‘a’,1),(‘b’,2),(‘c’,3))
query(value1,value2)
So the hard part would be pairing the values together in the parentheses. How are you looking to pass the values to the prompt? Two separate parameters? I’d need to think on it a bit.
You will see alternate examples re row constructors in the macros section of the redbook.
https://www.redbooks.ibm.com/redbooks/pdfs/sg248121.pdf
A series of row constructors is effectively a table constructor. As applicable, you may need to explicitly cast the literal value to ensure the type, precision and scale is the one you need/expect.
Several (but not all) vendors support queries using row/table constructors. Where they do, the SQL will be pushed down versus performed locally.
Hi Paul,
Thanks for this post. Saved me big time.
However I am facing an issue using it in my code. When i use it, it truncates all the inputs to the size of first value entered. Like if i enter first vale as 88888(5 characters), all my next inputs will be cut short to 5 each. If my first entry is 8 characters, all the next will be truncated to 8. Like that.
Any help with it?
Is this with a macro? I can’t seem to replicate this. Can you post the entire expression after the macro finishes processing? Try it like:
#sq(
‘SELECT
parameterValue
FROM (VALUES
(‘+join(‘),(‘,split(‘,’,promptmany(‘Scenarios’,’string’,sq(‘N/A’))))+’)
) query(parameterValue)’
)#
Yes. The behaviour seem to repeat on both FM as well analytics side. I am using the macro as it is.
Macro used :
SELECT
parameterValue
FROM (VALUES
(#join(‘),(‘,split(‘,’,promptmany(‘p_taxID’)))#)
) query(parameterValue )
Query generated :
with
SQL1 as
(select
cast ( parameterValue as varchar( 50 ) ) as parameterValue
from ( ( values (‘888888’) , (‘888888888888’) , (‘8888888888888’) ) ) query ( parameterValue ) )
select
SQL1.parameterValue as parameterValue
from
SQL1
Output in rows:
888888
888888
888888
sorry, the generated sql is
with
SQL1 as
(select
parameterValue
from ( ( values (‘N/A’) ) ) query ( parameterValue ) )
select
SQL1.parameterValue as parameterValue
from
SQL1
What’s funny is that if you put the longest stringed value first, all the other shorter values are right padded with spaces to match the length of that longest first value. But I can’t seem to be able to run any other commands on the string values in any subsequent Data Items I try to add to the Query Subject (like RTRIM for example.)
Any ideas how to instantiate the initial series of values in the SQL query subject, but then be able to manipulate them in other Data Items (like Case statements and RTRIMS) to create a sort of poor man’s Dimension (descriptions, etc) based on the initial values?