Oracle stored procedure not executing and throwing error

Question

I have a stored procedure where I am merging two queries to get the results. But I am getting this error:

Error(331,2): PL/SQL: ORA-00933: SQL command not properly ended

This is the query:

SELECT 
    jb.span_id,
    jb.maintenancezonecode,
    jbp.job_progress_id AS req_id,
    jb.reofferflag,
    modified_by AS last_update_by,
    sm.status_name,
    TO_DATE(SYSDATE) - TO_DATE(jbp.ums_group_ass_to_date) AS pending_days
FROM   
    tbl_fiber_inv_jobs jb
INNER JOIN
    tbl_fiber_inv_job_progress jbp ON jb.job_id = jbp.job_id
INNER JOIN
    tbl_fiber_inv_status_master sm ON jbp.status_id = sm.status_id
WHERE  
    jb.maintenancezonecode = pmaintzonecode
    AND ums_group_ass_to_name = pusertype
    AND jb.span_type = pspantype
    AND jbp.job_progress_flag = 1
ORDER BY 
    jbp.job_progress_id DESC

UNION ALL
  
SELECT
    TO_CHAR(TRIM(rj_span_id)) AS SPAN_ID, 
    TO_CHAR(RJ_MAINTENANCE_ZONE_CODE) AS MAINT_ZONE_CODE
FROM 
    NE.MV_SPAN@ne
WHERE
    LENGTH(TRIM(rj_span_id)) = 21
    AND Regexp_like (TRIM(rj_span_id),
                     'SP(N|Q|R|S).*+_(AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$', 'i')
    AND NOT Regexp_like (Nvl (rj_intracity_link_id, '-'), '_(9)', 'i')
    AND inventory_status_code = 'IPL'
    AND rj_maintenance_zone_code = pmaintzonecode;

Answer 1

As commented, both (actually, all) unioned selects have to have matching column list (in both number and datatype).

In your case, that would be something like

SELECT jb.span_id,
       jb.maintenancezonecode,
       jbp.job_progress_id AS req_id,
       jb.reofferflag,
       modified_by AS last_update_by,
       sm.status_name,
       TO_DATE (SYSDATE) - TO_DATE (jbp.ums_group_ass_to_date) AS pending_days
  FROM tbl_fiber_inv_jobs  jb
       INNER JOIN tbl_fiber_inv_job_progress jbp ON jb.job_id = jbp.job_id
       INNER JOIN tbl_fiber_inv_status_master sm ON jbp.status_id = sm.status_id
 WHERE     jb.maintenancezonecode = pmaintzonecode
       AND ums_group_ass_to_name = pusertype
       AND jb.span_type = pspantype
       AND jbp.job_progress_flag = 1
-- ORDER BY jbp.job_progress_id DESC
UNION ALL
SELECT TO_CHAR (TRIM (rj_span_id)) AS SPAN_ID,
       TO_CHAR (RJ_MAINTENANCE_ZONE_CODE) AS MAINT_ZONE_CODE,
       NULL AS req_id,
       NULL AS reofferflag,
       NULL AS last_update_by,
       NULL AS status_name,
       NULL AS pending_Days
  FROM NE.MV_SPAN@ne
 WHERE     LENGTH (TRIM (rj_span_id)) = 21
       AND REGEXP_LIKE (
              TRIM (rj_span_id),
              'SP(N|Q|R|S).*+_(AC|BN|BS|DN|DP|ID|LA|MS|MT|MU|OG|OL|PG|RC|RG|RT|VF|VT|YH)$',
              'i')
       AND NOT REGEXP_LIKE (NVL (rj_intracity_link_id, '-'), '_(9)', 'i')
       AND inventory_status_code = 'IPL'
       AND rj_maintenance_zone_code = pmaintzonecode
ORDER BY 3;

Note that you also have to remove order by from the 1st query (I commented it out) and apply sorting to the whole result set at the end of the statement.