Forum Stats

  • 3,780,665 Users
  • 2,254,422 Discussions


Interactive Report and ORA-01741: illegal zero-length identifier

pjflynn Member Posts: 62 Blue Ribbon
edited Sep 24, 2008 12:30PM in APEX Discussions
When trying to run the interactive report I am receiving ORA-01741 : illegal zero-length identififer

After reading a previous thread (which is similar, but it is not related to interactive reports) I have tried...
1. wrapping the report source columns with NVL - no success
2. wrapping the view columns with NVL - no success

Also I have recreated the page using an old style SQL report, which worked ok.

DB version
Apex Version

Any help would be much appreciated!!!

Interactive Report Source

SELECT programme_name
FROM mis_statistics_vw

View source

CREATE OR REPLACE VIEW mis_statistics_vw AS
timesheet_data AS (
SELECT /*+ materialize */
,r.firstname || ' ' || r.surname resource_name
FROM mis_timesheet t
,mis_time_period tp
,mis_resource r
WHERE t.time_period_id = tp.time_period_id
AND t.resource_id = r.resource_id),
temp_project_code AS (
SELECT /*+ materialize */
,wc2.work_code_name Parent_work_code_name
FROM mis_programme p
,mis_work_code wc1
,mis_work_code wc2
,mis_division d
WHERE p.programme_id = wc1.programme_id
AND wc1.division_id = d.division_id (+)
AND wc1.parent_work_code_id = wc2.work_code_id (+)
SELECT NVL(tpc.programme_name,'-') programme_name
,NVL(tpc.work_code_name,'-') work_code_name
,NVL(tpc.parent_work_code_name,'-') parent_work_code_name
,NVL(tpc.division_name,'-') division_name
,NVL(a.activity_desc,'-') activity_desc
,NVL(at.activity_type_desc,'-') activity_type_desc
,NVL(a.start_date,'-') activity_Start_date
,NVL(a.end_date,'-') activity_end_date
,NVL(a.target_end_date,'-') target_end_date
,NVL(a.on_business_plan,'-') on_business_plan
,NVL(req.user_id,'-') Requestor_user_id
,NVL(a.requested_date,'-') activity_requested_date
,NVL(b1.branch_name,'-') approval_branch
,NVL(a.approval_date,'-') approval_date
,NVL(b2.branch_name,'-') customer_branch
,NVL(ws.work_stream_name,'-') work_stream_name
,NVL(ws.active_flag,'-') active_flag
,NVL(rt.rate_type,'-') rate_type
,NVL(ws.start_date,'-') work_stream_start_date
,NVL(td.user_id,'-') user_id
,NVL(td.resource_name,'-') resource_name
,NVL(td.timesheet_date,'-') timesheet_date
,NVL(td.week_commencing,'-') week_commencing
,NVL(td.week_num,'-') week_num
,NVL(td.year,'-') year
,NVL(td.month,'-') month
,NVL(td.quarter,'-') quarter
FROM mis_work_stream ws
,mis_activity a
,mis_activity_type at
,mis_rate_type rt
,temp_project_code tpc
,mis_resource req
,mis_branch b1
,mis_branch b2
,timesheet_data td
WHERE ws.activity_id = a.activity_id
AND a.activity_type_id = at.activity_type_id (+)
AND a.work_code_id = tpc.work_code_id (+)
AND ws.rate_type_id = rt.rate_type_id
AND a.requestor_id = req.resource_id (+)
AND a.approval_branch_id = b1.branch_id (+)
AND a.customer_branch_id = b2.branch_id (+)
AND ws.work_stream_id = td.work_stream_id (+)


This discussion has been closed.