Link to home
Start Free TrialLog in
Avatar of FelineConspiracy
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.

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

Open in new window

SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of FelineConspiracy
FelineConspiracy

ASKER

Very nice, thank you!