Forum Stats

  • 3,839,636 Users
  • 2,262,517 Discussions
  • 7,901,025 Comments

Discussions

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

pjflynn
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 9.2.0.8.0
Apex Version 3.1.1.00.09


Any help would be much appreciated!!!



Interactive Report Source

SELECT programme_name
,work_code_name
,parent_work_code_name
,division_name
,activity_desc
,activity_type_desc
,activity_start_date
,activity_end_date
,target_end_date
,on_business_plan
,Requestor_user_id
,activity_requested_date
,approval_branch
,approval_date
,customer_branch
,work_stream_name
,active_flag
,rate_type
,work_stream_start_date
,user_id
,resource_name
,timesheet_date
,week_commencing
,week_num
,year
,month
,quarter
FROM mis_statistics_vw




View source

CREATE OR REPLACE VIEW mis_statistics_vw AS
WITH
timesheet_data AS (
SELECT /*+ materialize */
r.user_id
,r.firstname || ' ' || r.surname resource_name
,t.work_stream_id
,t.timesheet_date
,tp.week_commencing
,tp.week_num
,tp.year
,tp.month
,tp.quarter
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 */
p.programme_id
,p.programme_name
,wc1.work_code_id
,wc1.work_code_name
,wc2.work_code_name Parent_work_code_name
,d.division_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 (+)
Tagged:

Answers

This discussion has been closed.