Analytics Software

Hi,

I've created a custom folder in Discoverer with the following SQL:

SELECT DISTINCT trunc(dlinl.labor_start_date) lab_date
,dhdr.task_assignment_id task_assignment_id_l
,dhdr.debrief_header_id debrief_header_id_l
,dlinl.labor_start_date
,dlinl.labor_end_date
,round((dlinl.labor_end_date - dlinl.labor_start_date) * 24,
2) onsite_hrs
,decode(dlinl.transaction_type_id,
50,
'FIELD',
45,
'DEPOT',
dlinl.transaction_type_id) labor_type
,travel_hours.quantity travel_hrs
FROM csf_debrief_headers dhdr
,(SELECT debrief_header_id
,transaction_type_id
,labor_start_date
,labor_end_date
,trunc(service_date) service_date
,row_number() over(PARTITION BY debrief_header_id, trunc(service_date) ORDER BY trunc(service_date)) AS lab_line_num
FROM csf_debrief_lines
WHERE transaction_type_id IN (50, 45) ---add type 45 to include depot labor
AND uom_code = 'HR') dlinl
,(SELECT debrief_header_id
,quantity
,trunc(service_date) service_date
,row_number() over(PARTITION BY debrief_header_id, trunc(service_date) ORDER BY trunc(service_date)) AS line_number
FROM csf_debrief_lines cdl
WHERE cdl.transaction_type_id = 51
AND cdl.uom_code = 'HR') travel_hours
WHERE dhdr.debrief_header_id = dlinl.debrief_header_id
AND travel_hours.debrief_header_id(+) = dlinl.debrief_header_id
AND travel_hours.service_date(+) = dlinl.service_date
AND travel_hours.line_number(+) = dlinl.lab_line_num
UNION ALL
SELECT DISTINCT trunc(dlint.labor_start_date) lab_date
,dhdr.task_assignment_id task_assignment_id_l
,dhdr.debrief_header_id debrief_header_id_l
,dlint.labor_start_date
,dlint.labor_end_date
,0 onsite_hrs
,decode(dlint.transaction_type_id,
51,
'TRAVEL',
dlint.transaction_type_id) labor_type
,dlint.quantity
FROM csf_debrief_headers dhdr
,csf_debrief_lines dlint
WHERE dhdr.debrief_header_id = dlint.debrief_header_id
AND dlint.transaction_type_id = 51
AND dlint.uom_code = 'HR'
AND NOT EXISTS
(SELECT 'X'
FROM csf_debrief_headers dhdr
,(SELECT debrief_header_id
,transaction_type_id
,labor_start_date
,labor_end_date
,trunc(service_date) service_date
,row_number() over(PARTITION BY debrief_header_id, trunc(service_date) ORDER BY trunc(service_date)) AS lab_line_num
FROM csf_debrief_lines
WHERE transaction_type_id IN (50, 45) ---add type 45 to include depot labor
AND uom_code = 'HR') dlinl
,(SELECT debrief_header_id
,debrief_line_id
,quantity
,trunc(service_date) service_date
,row_number() over(PARTITION BY debrief_header_id, trunc(service_date) ORDER BY trunc(service_date)) AS line_number
FROM csf_debrief_lines cdl
WHERE cdl.transaction_type_id = 51
AND cdl.uom_code = 'HR') travel_hours
WHERE dhdr.debrief_header_id = dlinl.debrief_header_id
AND travel_hours.debrief_header_id(+) = dlinl.debrief_header_id
AND travel_hours.service_date(+) = dlinl.service_date
AND travel_hours.line_number(+) = dlinl.lab_line_num
AND dlint.debrief_header_id = travel_hours.debrief_header_id
AND dlint.debrief_line_id = travel_hours.debrief_line_id)

Now, the query runs in PL/SQL Developer. When I validate the query in Discoverer Admin, it says that SQL is Valid. But when I run the report, I get "ORA-00907 Missing Right Parenthesis" error. Any help from Discoverer experts out there is appreciated as I'm cluless.

Alka.