Iam using following syntax to validate the prompts for avoiding the OR condition when we will use (Any) to select all the values but am getting the error
Error:
Database error: ORA-00907: missing right parenthesis Contact your Business Objects administrator or database supplier for more information. (Error: WIS 10901)
The IIF() function looks to be from Access database, not sure how you are using it for Oracle. Since the error thrown is an Oracle related one.
Also can you tell what you are trying to get in the above syntax, because you can use the ‘ALL’ keyword too fetch all records when nothing is selcted for the prompt.
Actually I have a prompt which will include ‘Any’ to select all the values
The code for that prompt object is
SELECT
SW_SPOT_FACT_L.BROADCAST_MONTH
where
SW_SPOT_FACT_L.BROADCAST_MONTH IN @Prompt(‘01. Broadcast Month’,‘A’,‘Date Details\Broadcast Month’,MULTI,FREE,User:1)
OR (’ (Any)’ IN @Prompt(‘01. Broadcast Month’,‘A’,‘Date Details\Broadcast Month’,MULTI,FREE,Persistent,{’ (Any)’},User:1) )
so if user is selecting a value the "OR (’ (Any)’ IN @Prompt(‘01. Broadcast Month’,‘A’,‘Date Details\Broadcast Month’,MULTI,FREE,Persistent,{’ (Any)’},User:1) ) " condition should not execute this is in order to improve query performance,but we are not sure how far it will add value to our performance
Since we are fetching millions of records and report performance is bottle neck for our solution
I need to look into the ‘any’ part but you can use an ‘ALL’ keyword if user is not going to select any value.
Like this
SW_SPOT_FACT_L.BROADCAST_MONTH IN @Prompt('01. Broadcast Month','A','Date Details\Broadcast Month',MULTI,FREE,,User:1)
OR
'ALL' IN @Prompt('01. Broadcast Month','A','Date Details\Broadcast Month',MULTI,FREE,,User:1)
When user selects random values the OR will be ignored is my understanding.
So the OR ‘ALL’ is not going to execute unless it is passed to the prompt.
Are you asking how the SQL for this condition works?
Or are you asking about performance implications of this condition?
Here are some comments for both…
This SQL works because either the user selects an actual value, and the SQL evaluates to:
WHERE ( sw_spot_fact.broadcast_month IN ('SEP 2007') )
Or, the user enters your “special” word, like ‘Any’, and the SQL evaluates to:
WHERE ( 'Any' IN ('Any'))
…which is TRUE, and therefore ignored by the SQL engine.
Performance: Hopefully you have an index on this column in the DB so that if the user selects a value for that field/column, the index is used and the query runs efficiently.
If the user selects ‘Any’ then the clause will not be executed, and your performance will depend on other things in your query, such as indexes on your join columns, have you used measure objects in your query to speed it up (i.e., aggregation), anything else from the DB point of view.
As you said that if we select (Any) indexes will not be efficient so is there any way we can encapsulate all the values of the column in to single value (Any) so that if user is selecting (Any) there will not be any OR clause
For reference my code is as follows correct if any mistake
Object :
Network Name :
SELECT DISTINCT
NETWORK_DIM.NETWORK_NAME
FROM
NETWORK_DIM
UNION
SELECT ’ (Any)’ FROM DUAL
Prompt :
SELECT
NETWORK_DIM.NETWORK_NAME
WHERE
(NETWORK_DIM.NETWORK_NAME) IN @Prompt(‘07. Network Name’,‘A’,‘Network\Network Name’,MULTI,FREE,User:7)
OR (’ (Any)’ IN @Prompt(‘07. Network Name’,‘A’,‘Network\Network Name’,MULTI,FREE,Persistent,{’ (Any)’},User:7) )
Basically we deal with huge amount data and also we display it on the report …so performance is main issue
The OR clause will always be there, with the syntax we discussed, but in certain situations, the clause will evaluate to TRUE and therefore be ignored.
If you are getting bad performance from this type of condition, then maybe you need to have 2 separate reports, one that uses a condition (prompt) where the user selects one or more values, and a second report that does not have any conditions. The report without conditions could be scheduled to run overnight.
Other than that suggestion, I would say you could try working with the DBA to speed up your query performance.
(BOB member since 2005-07-18)
(BOB member since 2006-02-13)
…so performance is main issue