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