I know how to use a Cognos parameter value in a pass-through SQL statement i.e.
Select stuff
From lotsOfStuff
Where date = #prompt('varDate')#
But is there a way to use a prior Cognos query result in a pass-through SQL statement?
Select stuff
From lotsOfStuff
Where value in ([query1].[vaules])
I have to start by asking the obvious question - why do you need to code a pass-thru SQL statement in the first place?
MF.
We wanted to use that an existing SQL statement that works. We haven't been able to model it correctly in Framework Manager so that it gets that same results as the original SQL statement. When we get it to work then we will use the model version. Until then we would like to limit the number of rows the statement returns before Cognos joins it with the modeled data.
Thanks.
In that case, you could use 'native' SQL instead of pass-through. The prompt macro works in native SQL. It will get the value from the prompt and hard code the value in the filter condition.
and by the way, this post belongs to the Framework Manager forum..
You could also join it to another query that has the 'prior query' and force the join using a filter.
or
write an SQL like this:
Select stuff
From lotsOfStuff
Where exists (prior query..)
Cognostechie,
I didn't understand how your answer works - and I need to do something similar - so would appreciate more detail. My report (Report Studio) has a report run date prompt, and I need to use a range of the run date +/- 24 months in a native SQL query object. I tried doing this in the native SQL query where clause:
where a.DTECASFIL between (#prompt('RunDate')# - 24 months) and (#prompt('RunDate')# + 24 months)
but the compiler (DB2) won't accept it, giving a "An expression with a datetime value or a labeled duration is not valid" error message.
I can use the run date prompt in a query item to calculate the start and end dates I need for the range, but can't use the query columns in the native SQL object. I'd like to do this in my native SQL query:
where a.DTECASFIL between [report dates].[start date] and [report dates].[end date]
but the complier just sees "[" as an unexpected token.
Any ideas on how to do this, or a better way of passing a date range based on a single date prompt value into a native SQL object?
Much appreciated!
Quote from: efields50 on 14 Nov 2011 01:47:58 PM
Cognostechie,
I didn't understand how your answer works - and I need to do something similar - so would appreciate more detail. My report (Report Studio) has a report run date prompt, and I need to use a range of the run date +/- 24 months in a native SQL query object. I tried doing this in the native SQL query where clause:
where a.DTECASFIL between (#prompt('RunDate')# - 24 months) and (#prompt('RunDate')# + 24 months)
but the compiler (DB2) won't accept it, giving a "An expression with a datetime value or a labeled duration is not valid" error message.
I can use the run date prompt in a query item to calculate the start and end dates I need for the range, but can't use the query columns in the native SQL object. I'd like to do this in my native SQL query:
where a.DTECASFIL between [report dates].[start date] and [report dates].[end date]
but the complier just sees "[" as an unexpected token.
Any ideas on how to do this, or a better way of passing a date range based on a single date prompt value into a native SQL object?
Much appreciated!
Did you tried to specify the format of the prompt?
For example
where [DAY_DATE] > #prompt('selDate','date')#
Please update if this worked for you
pricter is correct. First of all #prompt(..)# is a Cognos macro which cannot be interpreted by any database. It can be used only within Cognos tools. When Cognos genereates the SQL, it takes the date value from the prompt and uses that in the filter portion of the generated SQL.
I have it working in a Query subject like this -
Select .......
where #prompt(...)# between alais.Start_date and alias.End_Date.
You can also put this macro in a Query item but you cannot put this macro in a native SQL unless that SQL is used in a Query subject and specified as a 'native' in FM. The it will still work but it won't work if you use that SQL within the databse editor / SQL Developer/ Toad etc.
put a value prompt and hide it
you could use the [query].[value] as a source of the prompt
then use the prompt in SQL as you mentioned