Using @Prompt to Dynamically Choose Logical Operator (5.1.x)

I tried @prompt('Enter Logic Operator: ',A,MULTI,FREE) but I’m getting a DA0005 with an underlying ORA-00920: invalid relational operator.

Just wondering if there is a trick to get around this.

Thanks.


tscoccol :us: (BOB member since 2003-11-15)

An object defined in Designer as

@prompt('Enter Logic Operator: ', A, , MULTI, FREE)

will never parse as it is not associated with any database table/view. What are you really trying to achieve?

If you get this error when refreshing a data provider, please post the Business Objects generated SQL code.


Andreas :de: (BOB member since 2002-06-20)

A user wants to run a report to count the number of users who have logged in a certain number of times on the website.

He wants the flexibility of being able to express the threshold amount and logical operator at runtime so that he count the users who have logged in > 3, <= 6, > 10, for example. He doesn’t want to be locked into doing a > comparison all the time, or a < comparison.

If I can’t user the @prompt for the logical operator, I think I’ll just have him enter two numbers for the threshold amount, one being a mininum and the other being the maximum and just doing a range check.


tscoccol :us: (BOB member since 2003-11-15)

So when are you getting the error? When parsing the object in Designer? What does a sample SQL code of a data provider look like?

Your goal would be to achieve something like:

(
@Prompt ('Enter comparison operator', ...) = 'Less than'
AND TableName.Value < @Prompt ('Enter threshold', ...)
)
OR(
@Prompt ('Enter comparison operator', ...) = 'Greater than'
AND TableName.Value > @Prompt ('Enter threshold', ...)
)
...

Andreas :de: (BOB member since 2002-06-20)

I’m getting it at runtime because I’m using Freehand SQL. Here’s the SQL. When I take out the prompt for the logical operator the query runs fine.

SELECT "YYYY-MM of Activity Date"
       ,COUNT(USER_ID) AS "Count ID Numbers"
FROM 
(
SELECT t.user_id, TO_CHAR(t.activity_date,'YYYY-MM') AS "YYYY-MM of Activity Date"
       ,SUM(CASE 
             WHEN t.APP_ID = 'MOS' 
                  AND t.ACTIVITY_CODE = 'LOGIN'  
             THEN 1 
             ELSE 0 
             END) AS "Count Login"
FROM app_usage_logging_hist t
WHERE t.APP_ID = 'MOS'
GROUP BY t.user_id, t.app_id, t.activity_code, TO_CHAR(t.activity_date,'YYYY-MM')
) 
WHERE "YYYY-MM of Activity Date" >= @Prompt('1) Start Date: (YYYY-MM)','A',,MONO,FREE)
AND "YYYY-MM of Activity Date" <= @Prompt('2) End Date: (YYYY-MM)','A',,MONO,FREE)
GROUP BY "YYYY-MM of Activity Date"
HAVING SUM("Count Login") @prompt('Enter Logical Comparison Operator:  ',A,,MONO,FREE)
@prompt('Enter User Login Threshold (0 for All):  ',N,,MONOI,FREE)

tscoccol :us: (BOB member since 2003-11-15)

This will simply not work, period. Try the approach I outlined previously.


Andreas :de: (BOB member since 2002-06-20)