Kin Jun Kin Jun - 3 years ago 153
SQL Question

PL/SQL: SQL Statement ignored in oracle query

CREATE OR REPLACE PROCEDURE "SP_MM_QC_DATA_MACH_JUN"
(PSEC VARCHAR2,
PCAT VARCHAR2,
PSUB VARCHAR2,
PMACHINE VARCHAR2,
SREFDATA OUT SYS_REFCURSOR ) as
BEGIN
OPEN SREFDATA FOR
select S.DESCRIPTION as SECTION,
MT.CODE as MACHINE_TYPE,
MH.MACH_NO,
MH.ID_MM_SPINMACHINE_RW_H as ID_MM_MACHINE,
S.ID_MM_SPINSECTION,
MT.CODE || ' - ' || MH.MACH_NO as Machine
from MM_SPINMACHINE_H MH
left join MM_MACHINETYPE MT
on MT.ID_MM_MACHINETYPE = MH.ID_MM_MACHINETYPE
left join MM_SPINSECTION S
on S.ID_MM_SPINSECTION = MT.ID_MM_SPINSECTION
where MH.REC_TYPE <> '5'
and s.id_mm_sec = PSEC
union all
select ms.DESCRIPTION as section,
mg.group_desc as machine_type,
mm.machine_code as MACH_NO,
id_mm_machine as id_mm_machine,
MS.ID_MM_SPINSECTION,
mg.group_desc || ' - ' || mm.machine_code as Machine
from MM_MACHINE MM
left join mm_spinsection ms
on mm.id_mm_sec = ms.ID_MM_SPINSECTION
left join mm_group mg
on mm.id_mm_group_mc_typ = mg.id_mm_group
where mm.record_typ <> '5'
and mm.id_mm_sec = PSEC
order by 1,2,3;
END;


I got error in


Error: PL/SQL:

ORA-00904: "S"."ID_MM_SEC": invalid identifier

Line: 19 Text: AND S.ID_MM_SEC = PSEC Error:

PL/SQL: SQL Statement ignored

Line: 13 Text: select S.DESCRIPTION as SECTION, MT.CODE as MACHINE_TYPE, MH.MACH_NO


Please advice.

APC APC
Answer Source

PL/SQL: SQL Statement ignored

This error message is the PL/SQL compiler saying, I found one error in the SQL I'm not going to check for any more. So the crucial message is the one which identifies the error. In your case it is this:

ORA-00904: "S"."ID_MM_SEC": invalid identifier

This is the compiler telling you table MM_SPINSECTION does not have a column called ID_MM_SEC. You need to check your table definition and get the actual column name.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download