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

FelineConspiracyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
You need to treat the WITH select as an actual table and include it in the FROM clause.

For example:
with data as (
select sysdate bob from dual
)
select bob from data;

All appropriate joins will also need to be made in the outer select.
0
PortletPaulfreelancerCommented:
I've attempted the join and made some other comments here:
with deal as
(
	select DISTINCT /* ?? does it need distinct ?? */
		rfrnc_dlr_nb,
		fnc_mgr_nb,
		sls_mgr_frst_nm,
		sls_mgr_lst_nm
                        /* remove any unused fields, esp. if doing distinct */
        /*,
		fnc_mgr_frst_nm,
		fnc_mgr_lst_nm,
		sls_mgr_nb,
		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	
inner join
    deal /* joining CTE here */
on 
    dlr_usr_prfl.RFRNC_DLR_NB = deal.rfrnc_dlr_nb
    and usr.usr_frst_nm = deal.sls_mgr_frst_nm 
    and usr.usr_lst_nm = deal.sls_mgr_lst_nm 
where
	(
		(
		/* F&I Manager */
		dlr_usr_prfl.pstn_cd = 130 
		and deal.fnc_mgr_nb != dlr_usr_prfl.dlr_empl_id
		)
		or
		(
		/* Sales Manager */
		dlr_usr_prfl.pstn_cd = 280 
		and deal.sls_mgr_nb != dlr_usr_prfl.dlr_empl_id
		)
		or 
		(
		/* Sales Consultant */
		dlr_usr_prfl.pstn_cd = 270
		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

BUT:
there is no gain in using a common table expression (the, with ... as) here. It could just as easily have been a nested subquery, like this:
inner join (
            select DISTINCT /* ?? does it need distinct ?? */
                rfrnc_dlr_nb,
                fnc_mgr_nb,
                sls_mgr_frst_nm,
                sls_mgr_lst_nm
                                /* remove any unused fields, esp. if doing distinct */
                /*,
                fnc_mgr_frst_nm,
                fnc_mgr_lst_nm,
                sls_mgr_nb,
                sls_man_nb,
                sls_man_frst_nm,
                sls_man_lst_nm
                */
            from
                fnc_cntrct
            where
                OPN_DL_DT >= to_date('20130930','yyyymmdd')
           )
    deal /* joining nested subquery here */
on 
    dlr_usr_prfl.RFRNC_DLR_NB = deal.rfrnc_dlr_nb
    and usr.usr_frst_nm = deal.sls_mgr_frst_nm 
    and usr.usr_lst_nm = deal.sls_mgr_lst_nm 

Open in new window

In my view CTEs are best used if there is recursion (not needed here), or if there is a need to repeat the subquery (not required here either).
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
PortletPaulfreelancerCommented:
using "WITH ... AS ( ...)" is called "Subquery Factoring" within Oracle's documentation but is perhaps more widely known as Common Table Expression (CTE).

e.g. http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#i2066378

note the CTE is referenced within the FROM clause of the subsequent query in those examples (but it does not have to be the "from table" it can be joined).
0
FelineConspiracyAuthor Commented:
Very nice, thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.