FelineConspiracy
asked on
An Oracle WITH syntax question
I think this is a question about Oracle's WITH syntax. This is Oracle 11g.
Is there something wrong with the following query? I get ORA-00904: "DEAL"."SLS_MAN_NB": invalid identifier. But all the column names are correct.
Is there something wrong with the following query? I get ORA-00904: "DEAL"."SLS_MAN_NB": invalid identifier. But all the column names are correct.
with deal as
(
select
rfrnc_dlr_nb,
fnc_mgr_nb,
fnc_mgr_frst_nm,
fnc_mgr_lst_nm,
sls_mgr_nb,
sls_mgr_frst_nm,
sls_mgr_lst_nm,
sls_man_nb,
sls_man_frst_nm,
sls_man_lst_nm
from
fnc_cntrct
where
OPN_DL_DT >= to_date('20130930','yyyymmdd')
)
select
dlr_usr_prfl.RFRNC_DLR_NB,
dlr_usr_prfl.dlr_empl_id,
usr.USR_FRST_NM,
usr.USR_LST_NM,
pstn.PSTN_NM
from
dlr_usr_prfl
inner join
usr
on
dlr_usr_prfl.usr_id = usr.usr_id
inner join
pstn
on
dlr_usr_prfl.PSTN_CD = pstn.PSTN_CD
where
dlr_usr_prfl.rfrnc_dlr_nb = deal.rfrnc_dlr_nb
and
(
(
/* F&I Manager */
dlr_usr_prfl.pstn_cd = 130
and usr.usr_frst_nm = deal.fnc_mgr_frst_nm
and usr.usr_lst_nm = deal.fnc_mgr_lst_nm
and deal.fnc_mgr_nb != dlr_usr_prfl.dlr_empl_id
)
or
(
/* Sales Manager */
dlr_usr_prfl.pstn_cd = 280
and usr.usr_frst_nm = deal.sls_mgr_frst_nm
and usr.usr_lst_nm = deal.sls_mgr_lst_nm
and deal.sls_mgr_nb != dlr_usr_prfl.dlr_empl_id
)
or
(
/* Sales Consultant */
dlr_usr_prfl.pstn_cd = 270
and usr.usr_frst_nm = deal.sls_man_frst_nm
and usr.usr_lst_nm = deal.sls_man_lst_nm
and deal.sls_man_nb != dlr_usr_prfl.dlr_empl_id
)
)
order by
dlr_usr_prfl.RFRNC_DLR_NB,
usr.USR_LST_NM,
usr.USR_FRST_NM
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER