Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Geoff.

Asked: October 05, 2016 - 4:07 pm UTC

Last updated: October 06, 2016 - 1:43 am UTC

Version: 11.2

Viewed 1000+ times

You Asked

Tom,

I have an existing view that works fine. It is used to compare rows in two tables and to display which table has records that the other doesn't. I've been asked to add one more column to this view from a new table. For one reason or another I keep getting a "missing right parenthesis" or "SQL Command not properly ended" even though I'm pretty confident I've got it right. Below is the working view code without the added row.

create or replace view trans_gis.sbat_comparison_vw (SOURCE, EQUIPMENT_TYPE, EQUIPMENT_NUMBER, E_CODE, SUB_SYSTEM_MNEMONIC, SYSTEM_CODE) as
(select distinct 'In SUBSTATION_EQUIPMENT_VW only', a.passport_equipment_type, a.designation_number, a.e_code, a.substation_mnemonic, a.system
from trans_gis.substation_equipment_vw a where a.passport_equipment_type='SBAT' and a.system in ('TRANS', 'DISTR') and a.drawing_file_status='AS BUILT'
minus
select distinct'In SUBSTATION_EQUIPMENT_VW only', b.a_equipment_type, b.equipment_number, b.a_e_code, b.op_system, b.system_code
from trans_gis.passport_data b where b.a_equipment_type='SBAT' and b.system_code in ('TRANS', 'DISTR'))
union all
(select distinct 'In PASSPORT_DATA only', b.a_equipment_type, b.equipment_number, b.a_e_code, b.op_system, b.system_code
from trans_gis.passport_data b where b.a_equipment_type='SBAT' and b.system_code in ('TRANS', 'DISTR')
minus
select distinct 'In PASSPORT_DATA only', a.passport_equipment_type, a.designation_number, a.e_code, a.substation_mnemonic, a.system
from trans_gis.substation_equipment_vw a where a.passport_equipment_type='SBAT' and a.system in ('TRANS', 'DISTR') and a.drawing_file_status='AS BUILT')
order by 5
/

What I'm essentially trying to do is this, but inside the above view.

select a.*, b.project_op_system from trans_gis.sbat_comparison_vw a, trans_sub.substation_mnemonic b where a.sub_system_mnemonic=b.mnemonic;

Here's a description of the trans_sub.substation_mnemonic table

SQL> desc trans_sub.substation_mnemonic;
Name Null? Type
----------------------------------------- -------- ----------------------------
SUBSTATION_MNEMONIC_ID NOT NULL NUMBER
MNEMONIC NOT NULL VARCHAR2(7)
OPERATING_COMPANY NOT NULL VARCHAR2(7)
PROJECT_OP_SYSTEM VARCHAR2(7)
CREATE_DATE DATE
LAST_UPDATED_DATE DATE

My best try so far is this :


create or replace view trans_gis.sbat_comparison_vw (SOURCE, EQUIPMENT_TYPE, EQUIPMENT_NUMBER, E_CODE, SUB_SYSTEM_MNEMONIC, SYSTEM_CODE, PROJECT_OP_SYSTEM) as
(select c.*, d.project_op_system
from trans_sub.substation_mnemonic d,
(select distinct 'In SUBSTATION_EQUIPMENT_VW only', a.passport_equipment_type, a.designation_number, a.e_code, a.substation_mnemonic, a.system
from trans_gis.substation_equipment_vw a where a.passport_equipment_type='SBAT' and a.system in ('TRANS', 'DISTR') and a.drawing_file_status='AS BUILT'
minus
select distinct'In SUBSTATION_EQUIPMENT_VW only', b.a_equipment_type, b.equipment_number, b.a_e_code, b.op_system, b.system_code
from trans_gis.passport_data b where b.a_equipment_type='SBAT' and b.system_code in ('TRANS', 'DISTR'))
union all
(select distinct 'In PASSPORT_DATA only', b.a_equipment_type, b.equipment_number, b.a_e_code, b.op_system, b.system_code
from trans_gis.passport_data b where b.a_equipment_type='SBAT' and b.system_code in ('TRANS', 'DISTR')
minus
select distinct 'In PASSPORT_DATA only', a.passport_equipment_type, a.designation_number, a.e_code, a.substation_mnemonic, a.system
from trans_gis.substation_equipment_vw a where a.passport_equipment_type='SBAT' and a.system in ('TRANS', 'DISTR') and a.drawing_file_status='AS BUILT')) c
where c.sub_system_mnemonic = d.mnemonic
/

ERROR at line 14:
ORA--00933: SQL command not properly ended

or

a.drawing_file_status='AS BUILT') c)
where c.sub_system_mnemonic = d.mnemonic
/

ERROR at line 14:
ORA-00907: missing right parenthesis

What am I doing wrong here?

and Connor said...

If we replace the working chunks of code with simple replacements, we can see the problem

SQL> CREATE OR REPLACE VIEW sbat_comparison_vw (SOURCE, EQUIPMENT_TYPE, EQUIPMENT_NUMBER, E_CODE, SUB_SYSTEM_MNEMONIC, SYSTEM_CODE, PROJECT_OP_SYSTEM)
  2  AS
  3    (SELECT c.*,
  4      d.project_op_system
  5    FROM trans_sub.substation_mnemonic d,
  6         xyz
  7    UNION ALL
  8      (SELECT * from abc)
  9    ) c
 10  WHERE c.sub_system_mnemonic = d.mnemonic ;
  ) c
    *
ERROR at line 9:
ORA-00933: SQL command not properly ended


Line 3 and 9 are saying get "c.*" from "c" which doesnt exist yet, ie, they are at the same nested level.

You possibly were intending to have

CREATE OR REPLACE VIEW sbat_comparison_vw (...)
AS
  SELECT c.*,
    d.project_op_system
  FROM 
  ( select * from   xyz
  UNION ALL
    (SELECT * from abc)
  ) c,
  trans_sub.substation_mnemonic d
WHERE c.sub_system_mnemonic = d.mnemonic ;



Is this answer out of date? If it is, please let us know via a Comment

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library