SQL & PL/SQL

The following SQL statement runs fine and returns data. Notice the ,nvl(fld.MULTSELECT,0).

SELECT exs.PK
,fld.Name
,fld.Desc_
,fld.DataTp
,case when rtrim(fld.InlineName) = '' then
NULL
--convert(nvarchar(255), Null)
else fld.InlineName
end InlineName
,fld.UOM
,fld.High
,fld.Low
,fld.CriticalHigh
,fld.CriticalLow
,fld.ChTp
,nvl(fld.MULTSELECT,0)
,fld.Type
,fld.IsProtected
,fld.RecTp
,fld.RecTpMbrSeq
,fld.SrcEtType
,fld.TargetEtType
,fld.FldCat
--,fc.ChFK FldCateoryFK
-- ,dbo.fn_StripCharacters(fld.Name, '^a-z0-9') SrcStagingFld -- strip out all characters except alphanumeric
,nvl(CAST(trim(fld.PropagateToEAV) AS NUMBER(1,0)),1) PropagateToEAV
,CAST(trim(fld.RecTimeStamp) AS NUMBER(1,0)) RecTimeStamp
,CAST(trim(fld.RecTimeStampEnd) AS NUMBER(1,0)) RecTimeStampEnd
,nvl(CAST(trim(fld.EtTypeTimeStampFld) AS NUMBER(1,0)),0) EtTimeStampFld
,nvl(CAST(trim(fld.EtTypeTimeStampEndFld) AS NUMBER(1,0)),0) EtTypeTimeStampEndFld
,nvl(CAST(trim(fld.EtTypeNameFld) AS NUMBER(1,0)),0) EtTypeNameFld
,nvl(CAST(trim(fld.EtTypeNameFldDeId) AS NUMBER(1,0)),0) EtTypeNameFldDeId
,CAST(trim(fld.EtSrcSystRecKey) AS NUMBER(1,0)) EtSrcSystRecKey
,CAST(trim(fld.EtSrcSystRecAlternativeRecKey) AS NUMBER(1,0)) EtSrcSystAlternativeRecKey
,CAST(trim(fld.ParentEtSrcSystRecKey) AS NUMBER(1,0)) ParentEtSrcSystRecKey
,CAST(trim(fld.MainEtRecTp) AS NUMBER(1,0)) MainEtRecTp
FROM Ld_UD_RecTp fld
-- left join MD_FldCat fc on fld.FldCat = fc.Name
left join Md_Fld exs on fld.RecTp = exs.RecTp and fld.Name = exs.Name
left join Aud_ModDataError Err on err.RecTp = fld.RecTp and Err.BatLogId = 1 -- check if the recordtype has any load errors
where nvl(fld.name,'zzzzzzz') <>'zzzzzzz'
and Err.RecTp is Null

When used in a stored procedure as follows, the procedure doesn't compile

create or replace PROCEDURE MD_Ld_Flds
(
v_BatLogID IN NUMBER DEFAULT NULL
)
AS
BEGIN
MERGE INTO MD_Fld targ
USING (SELECT exs.PK
,fld.Name
,fld.Desc_
,fld.DataTp
,case when rtrim(fld.InlineName) = '' then
NULL
--convert(nvarchar(255), Null)
else fld.InlineName
end InlineName
,fld.UOM
,fld.High
,fld.Low
,fld.CriticalHigh
,fld.CriticalLow
,fld.ChTp
,nvl(fld.MULTSELECT,0)
,fld.Type
,fld.IsProtected
,fld.RecTp
,fld.RecTpMbrSeq
,fld.SrcEtType
,fld.TargetEtType
,fld.FldCat
--,fc.ChFK FldCateoryFK
-- ,dbo.fn_StripCharacters(fld.Name, '^a-z0-9') SrcStagingFld -- strip out all characters except alphanumeric
,nvl(CAST(trim(fld.PropagateToEAV) AS NUMBER(1,0)),1) PropagateToEAV
,CAST(trim(fld.RecTimeStamp) AS NUMBER(1,0)) RecTimeStamp
,CAST(trim(fld.RecTimeStampEnd) AS NUMBER(1,0)) RecTimeStampEnd
,nvl(CAST(trim(fld.EtTypeTimeStampFld) AS NUMBER(1,0)),0) EtTimeStampFld
,nvl(CAST(trim(fld.EtTypeTimeStampEndFld) AS NUMBER(1,0)),0) EtTypeTimeStampEndFld
,nvl(CAST(trim(fld.EtTypeNameFld) AS NUMBER(1,0)),0) EtTypeNameFld
,nvl(CAST(trim(fld.EtTypeNameFldDeId) AS NUMBER(1,0)),0) EtTypeNameFldDeId
,CAST(trim(fld.EtSrcSystRecKey) AS NUMBER(1,0)) EtSrcSystRecKey
,CAST(trim(fld.EtSrcSystRecAlternativeRecKey) AS NUMBER(1,0)) EtSrcSystAlternativeRecKey
,CAST(trim(fld.ParentEtSrcSystRecKey) AS NUMBER(1,0)) ParentEtSrcSystRecKey
,CAST(trim(fld.MainEtRecTp) AS NUMBER(1,0)) MainEtRecTp
FROM Ld_UD_RecTp fld
-- left join MD_FldCat fc on fld.FldCat = fc.Name
left join Md_Fld exs on fld.RecTp = exs.RecTp and fld.Name = exs.Name
left join Aud_ModDataError Err on err.RecTp = fld.RecTp and Err.BatLogId = v_BatLogID -- check if the recordtype has any load errors
where nvl(fld.name,'zzzzzzz') <>'zzzzzzz'
and Err.RecTp is Null -- Exclude recordtypes which had errors
) src on (src.PK = Targ.PK)
when matched then
update Set Targ.Name =src.Name
,Targ.Desc_ =src.Desc_
,Targ.DataTp =src.DataTp
,Targ.InlineName =src.InlineName
,Targ.UOM =src.UOM
,Targ.High =src.High
,Targ.Low =src.Low
,Targ.CriticalHigh =src.CriticalHigh
,Targ.CriticalLow =src.CriticalLow
,Targ.ChTp =src.ChTp
,Targ.MultSelect =src.MultSelect
,Targ.Type =src.Type
,Targ.IsProtected =src.IsProtected
,Targ.RecTp =src.RecTp
,Targ.RecTpMbrSeq =src.RecTpMbrSeq
,Targ.SrcEtType =src.SrcEtType
,Targ.TargetEtType =src.TargetEtType
,Targ.FldCat =src.FldCat
--,Targ.SrcStagingFld =src.SrcStagingFld
,Targ.PropagateToEAV =src.PropagateToEAV
,Targ.RecTimeStamp =src.RecTimeStamp
,Targ.RecTimeStampEnd =src.RecTimeStampEnd
,Targ.EtTimeStampFld =src.EtTimeStampFld
,Targ.EtTimeStampEndFld =src.EtTypeTimeStampEndFld
,Targ.EtNameFld =src.EtTypeNameFld
,Targ.EtNameFldDeId =src.EtTypeNameFldDeId
,Targ.EtSrcSystRecKey =src.EtSrcSystRecKey
,Targ.EtSrcSystAlternativeRecKey=src.EtSrcSystAlternativeRecKey
,Targ.ParentEtSrcSystRecKey =src.ParentEtSrcSystRecKey
,Targ.MainEtRecTp =src.MainEtRecTp
,targ.LastModified = SYSDATE
when not matched then --target
insert (Name
,Desc_
,DataTp
,InlineName
,UOM
,High
,Low
,CriticalHigh
,CriticalLow
,ChTp
,MultSelect
,Type
,IsProtected
,RecTp
,RecTpMbrSeq
,SrcEtType
,TargetEtType
,FldCat
--,SrcStagingFld
,PropagateToEAV
,RecTimeStamp
,RecTimeStampEnd
,EtTimeStampFld
,EtTimeStampEndFld
,EtNameFld
,EtNameFldDeId
,EtSrcSystRecKey
,EtSrcSystAlternativeRecKey
,ParentEtSrcSystRecKey
,MainEtRecTp
,LastModified)
values (src.Name
,src.Desc_
,src.DataTp
,src.InlineName
,src.UOM
,src.High
,src.Low
,src.CriticalHigh
,src.CriticalLow
,src.ChTp
,src.MultSelect
,src.Type
,src.IsProtected
,src.RecTp
,src.RecTpMbrSeq
,src.SrcEtType
,src.TargetEtType
,src.FldCat
--,src.SrcStagingFld
,src.PropagateToEAV
,src.RecTimeStamp
,src.RecTimeStampEnd
,src.EtTimeStampFld
,src.EtTypeTimeStampEndFld
,src.EtTypeNameFld
,src.EtTypeNameFldDeId
,src.EtSrcSystRecKey
,src.EtSrcSystAlternativeRecKey
,src.ParentEtSrcSystRecKey
,src.MainEtRecTp
,SYSDATE);
END;
/

When you show errors you get the following;

7/7 PL/SQL: SQL Statement ignored
125/8 PL/SQL: ORA-00904: 'SRC"."MULTSELECT": invalid identifier

If I compile the procedure without the NVL, the procedure compiles with no errors.

We're running Oracle 11g