Using parameter map from within a query

[ordotan]

Hi,

I have a dimension table (id to name), and I created a parameter based on it.

I'm trying to use this parameter map from within a query using another data item, but i keep getting syntax error.
(The motivation is to avoid a join with the dimension table, to enhance the performance)

The options I had tried, that didn't work -
#sq($PT{[Procedure Type]})#
#sq($PT{to_char([Procedure Type])})#
#sq($PT{#sq([Procedure Type])#})#

Any suggestions?

[CognosPaul]

It's not going to work, unfortunately. Even if it did, it would probably be slower than the join.

Macros are processed before SQL is generated. Consider the following macro:

Code Select Expand

[Date] >= to_date(#sq(timestampMask(_first_of_month($current_timestamp),'yyyy-mm-dd'))#,'yyyy-mm-dd')

Cognos will process the contents of the macro,
1. $current_timestamp == 2014-01-15T17:32:00.000+02:00
2. _first_of_month == 2014-01-15T00:00:00.000+02:00
3. timestampMask == 2014-01-15
4. sq == '2014-01-15'

Finally the SQL statement will be prepared:

Code Select Expand

selected t.date from dates where t.date >= to_date('2014-01-15','yyyy-mm-dd')

The error you're getting is because the macro can't get at the data inside Procedure Type. Even if it could, it would still need to do the lookup for each row, effectively turning it into a join. Is there any reason a regular join would be inappropriate here?

Of course, if the user is selecting only one Procedure Type, then a parameter map would be perfectly reasonable:
#sq($PT{prompt('ProcType','string')})#