I have created a parameter map with the follow option
default: where 1<> 1
pm: where 1=1
erica: where SALES_REGION.SALES_REGION_CODE = 710
and query with the following expression
Select
*
from
[gosales].SALES_REGION
# $AfterEqual{$account.defaultName}#
and I am getting the following error
This query contains an error and can not be executed.
It is recommended that you view the query feedback on the "Query Information" tab.
RQP-DEF-0177 An error occurred while performing operation 'sqlPrepareWithOptions' status='-16'.
UDA-SQL-0358 Line 6: Syntax error near "where".
Does anyone have a suggestion who to overcome this issue?
Put # $AfterEqual{$account.defaultName}# into an expression and see if it resolves to the value that you expect. For example, if you are logged in as erica does # $AfterEqual{$account.defaultName}# resolve to where SALES_REGION.SALES_REGION_CODE = 710? You can use the override of the session parameter in the session parameters dialog.
If you had the actual strings that you want in the query subject rather than the macro do you get the same error? I'm wondering what would happen if instead of SALES_REGION.SALES_REGION_CODE you had [namespace].[SALES_REGION].[SALES_REGION_CODE]. The fact that the syntax error is coming from near where might be pointing to that area.
I'm assuming that pm and erica are keys in your parameter map. Is that a correct understanding?
Is any additional information seen in the query information tab?
Is anything useful logged in the xqe log files?
Could you enlighten me about the 1=1 and 1<>1 stuff please. Since 1 usually always equals 1, what are you attempting for that case? Since 1 usually is not not equal to 1 (because it usually always equals 1) wouldn't that filter out everything?
Since you seem to be keying on the identity of users, are you ruling out data security filters and, if so, why?
Just as an aside, data source query subjects with sql other than select * from table can trigger unnecessary metadata callbacks. You should consider doing stuff what you're trying in the business layer.
Thanks for you reply.
Is is a security filter. Erica sees specific region but pm see all the regions.
The regions that they see there are in a different table.
Do you propose to use data security filter in database layer of in the presentation layer?
QuoteDo you propose to use data security filter in database layer of in the presentation layer?
business layer. The query layer is for storing the data base metadata and planning the queries (stuff like role-playing etc.).
When you tried the ideas that I suggested what results did you get?