BusinessObjects Board

don't generate where clause if user enters *

building semantic layer
#1

Is it possible to have a condition created in Designer that would not add a where clause if the user enters an asterisks (*)?

Basically the problem is this… I have a predefined condition:

upper(member.last_nm) matches upper(@Prompt(‘Last Name Matches (* is wildcard)’,‘A’,))

If the user inputs ‘*’, I don’t want:

“UPPER(db:member.last_nm ) MATCHES ‘*’ )” in the where clause of the generated SQL.

“1=1” would be better if there is no way to not generate the SQL.

Patrick McDonough (BOB member since 2002-08-20)

#2

No way to do it exactly the way your are describing.

However, the Optional Promting that you are really looking for can be done other ways. The following has been posted her many times:

I’m sure it could be adjusted to make it more efficient.

This is a sample of the SQL that can be used in a pre-defined condition
to accomplish “1, many or all” functionality. You don’t need to do this
if it’s 1 or all. You can simply use the “matches pattern” operator.
Unfortunately, matches pattern doesn’t work if the user wants to enter
several items.

(PER_JOBS.ATTRIBUTE3 IN @Prompt(‘Enter Employee Survey Code(s) (“ALL” for
all)’,‘A’,‘Job Information\Add. Job Details Survey Code’,multi,free) OR
‘ALL’ IN @Prompt(‘Enter Employee Survey Code(s) (“ALL” for all)’,‘A’,‘Job
Information\Add. Job Details Survey Code’,multi,free) OR
‘All’ IN @Prompt(‘Enter Employee Survey Code(s) (“ALL” for all)’,‘A’,‘Job
Information\Add. Job Details Survey Code’,multi,free) OR
‘all’ IN @Prompt(‘Enter Employee Survey Code(s) (“ALL” for all)’,‘A’,‘Job
Information\Add. Job Details Survey Code’,multi,free) OR
‘*’ IN @Prompt(‘Enter Employee Survey Code(s) (“ALL” for all)’,‘A’,‘Job
Information\Add. Job Details Survey Code’,multi,free) OR
‘%’ IN @Prompt(‘Enter Employee Survey Code(s) (“ALL” for all)’,‘A’,‘Job
Information\Add. Job Details Survey Code’,multi,free))

Steve Krandel :us: (BOB member since 2002-06-25)

#3

Steve.

I added your example of how to deal with case sensitivity and multiple prompt inputs to the One, Many, or ALL FAQ Answer

Cindy Clayton :us: (BOB member since 2002-06-11)

#4

Thanks for the quick reply.

The prompt as it is works fine. The LOV path is cumbersome due to the fact that there are >50k unique values.

I am working on optimization of a particularly large query. The problem is that the DB’s optimizer picks one table prior to another (better for optimization) because it believes that it is being filtered. The optimizer thinks that ‘Member.first_nm matches “"’ is limiting the data set from Member. When I take the 'member.first_nm matches "”’ out, the query uses a better filtered table first (a much better optimization scheme in general). The difference in an example query’s execution is from 1.2 minutes to 15 seconds.

I am working on other routes too: indexes, statistics, fragmentation, etc…

Sounds like a where clause has to be generated when using a condition.

Thanks

Patrick

Patrick McDonough (BOB member since 2002-08-20)