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
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