BO @prompt problem - Free hand SQL

[hoolio]

I have written a small SQL script that I want some users to use who have access to Business Objects 4.1.4. I am trying to run the script using the Business Objects Free Hand SQL function.

The problem is when I insert my @prompts into the script I get an Oracle Error "ORA-00907: missing right parenthesis :-907". I know the script is correct as when I take the prompts out the script works fine. I need for the users to be able to enter their own dates there the prompts are essential.

The bits that go wrong are:

to_date('@prompt('start date?',,,,)'||'@prompt('start time?',,,,)','DD-MON-YYYYHH24:MI')
and
to_date('@prompt('end date?',,,,)'||'@prompt('end time?',,,,)','DD-MON-YYYYHH24:MI')
and TO_DATE(to_char(a.system_date_1,'DD-MON-YYYY')||a.system_time_1,'DD-MON-YYYYHH24:MI')

Here is the full script:

select a.call__t, a.action_no, a.employees__key, e.employee_name, A.date_from, A.time_from, A.system_date_1, A.system_time_1, E.EMPLOYEE_FATHER__key, b.employee_name
from event_report a, employees e, EMPLOYEES B
where TO_DATE(to_char(a.date_from,'DD-MON-YYYY')||a.TIME_FROM,'DD-MON-YYYYHH24:MI') between
to_date('@prompt('start date?',,,,)'||'@prompt('start time?',,,,)','DD-MON-YYYYHH24:MI')
and
to_date('@prompt('end date?',,,,)'||'@prompt('end time?',,,,)','DD-MON-YYYYHH24:MI')
and TO_DATE(to_char(a.system_date_1,'DD-MON-YYYY')||a.system_time_1,'DD-MON-YYYYHH24:MI')
- TO_DATE(to_char(a.date_from,'DD-MON-YYYY')||a.time_from,'DD-MON-YYYYHH24:MI')
> 0.020833333
and a.event__t in ('CLSNW1', 'CLSFIX')
and e.employees__key = a.employees__key
and e.employee_father__key = b.employees__key

 

[Sridharan]

Surely a problem with the prompt sytax as it is not complete. Try giving all the remaining parameters and check whether it works instead of just first parameter.

Sri

 

[hoolio]

I thought I had used the correct prompt syntax? Putting the commas in just accepts the defaults does it not? Anyway-what syntax should I try please?

 

[hoolio]

I have the answer. I just needed to remove the single quotes from around the @prompt.

Thanks for your help.