Joining 2 CTe together in ORACLE SQL

HI - me again,

i have the 2 following CTE i need to join together:

with
cte_base
as
(
select 
e.PERSON_REF,
e.EMPLOYEE_NUMBER,
coalesce(rtrim(ltrim(known_as)),per.FIRST_FORNAME)  as FORENAME,
per.SURNAME as SURNAME,
per.NI_NO NINO,
per.INITIALS INITS,
per.TITLE TITLE,
post.number_R as POST_NUMBER,
post.long_desc as POST_LONG_DESC,
e.START_DATE EMPSTARTDATE,
e.END_DATE EMPENDDATE,
CGrp.ID CGRPID,
CGrp.LONG_DESC CGRPDESC,
PGrp.ID PGRPID,
PGrp.LONG_DESC PGRPDESC,
narr_occtype.long_desc as OCC_TYPE,
narr_joinreason.long_desc as POST_JOIN_REASON,
narr_joinreason2.long_desc as EMP_JOIN_REASON,
ph.projected_end_date
from D550M e
left outer join D500M per on e.PERSON_REF = per.PERSON_REF
left outer join D100M CGrp on e.LEVEL1_PAY_STR_REF = CGrp.REF
left outer join D100M PGrp on e.PAY_STR_REF = PGrp.REF 
left outer join D580M ph on e.PERSON_REF = ph.PERSON_REF and
( ph.start_date <= sysdate and (ph.end_date is null or ph.end_Date >= sysdate))
left outer join D200M post on ph.ref  = post.ref
left outer join D800M narr_occtype on ph.occ_type = narr_occtype.narrative_code and narr_occtype.narrative_category = 'OCCTYP'
left outer join D800M narr_joinreason on ph.join_reason= narr_joinreason.narrative_code and narr_joinreason.narrative_category = 'RSNCHG'
left outer join D800M narr_joinreason2 on e.orig_how_joined = narr_joinreason2.narrative_code and narr_joinreason2.narrative_category = 'RSNCHG'

),

CTE_PostGrade AS
(
select e.PERSON_REF,
Gr.NUMBER_R GRADE
from D550M e
left join D580M ph on e.PERSON_REF = ph.PERSON_REF
left join D554M phGr on phGr.PERSON_REF = ph.PERSON_REF and
phGr.POST_REF = ph.REF
left join D400M Gr on Gr.REF = phGr.REF
where ph.MAIN_FLAG = 'Y'
and e.START_DATE <= trunc(SYSDATE) and coalesce(e.END_DATE, trunc(SYSDATE)) >= trunc(SYSDATE)
and ph.START_DATE <= trunc(SYSDATE) and coalesce(ph.END_DATE, trunc(SYSDATE)) >= trunc(SYSDATE)
and phGr.START_DATE <= trunc(SYSDATE) and coalesce(phGr.END_DATE, trunc(SYSDATE)) >= trunc(SYSDATE)
)
select 
--PERSON_REF,
EMPLOYEE_NUMBER,
FORENAME as KNOWN_AS,
SURNAME,
TITLE,
POST_NUMBER,
POST_LONG_DESC,
EMPSTARTDATE,
EMPENDDATE,
CGRPID,
CTE_PostGrade.NUMBER_R GRADE,
CGRPDESC,
PGRPID,
PGRPDESC,
OCC_TYPE,
POST_JOIN_REASON,
EMP_JOIN_REASON,
projected_end_date
from 
cte_lv
CTE_PostGrade

WHERE
EMP_JOIN_REASON NOT IN ('contractor','FTC New Starter')
AND
EMPSTARTDATE = sysdate -7

Open in new window


I need to join these 2 CTE together, but wasnt sure what to join on?

If someone could point me in the right direction i would be greatful,

Many thanks,

Rich
Richiep86Asked:
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.

Geert GOracle dbaCommented:
a join is on columns

a cte just acts as a table in that case

with
  cte1 as (select column1 ...),
  cte2 as (select column2 ...)
select * from cte1, cte2
where cte1.column1 = cte2.column2
 
you didn't state what you are trying to join ...

employee_number to person_ref ???
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
Richiep86Author Commented:
Yeah - those are the correct fields - thankyou!

Thank you Geert for this explanation.
0
Richiep86Author Commented:
cte_lv.EMPLOYEE_NUMBER = CTE_PostGrade.PERSON_REF
 AND
EMP_JOIN_REASON NOT IN ('contractor','FTC New Starter')
AND EMPSTARTDATE  >= sysdate - 7

Open in new window


CTE_POSTGRADE.PERSON_REF: Invalid identifier.

Any ideaas?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Wasim Akram ShaikCommented:
Invalid identifier means..the column which you are selecting/using doesn't exist in the table/query used..

as you stated CTE_POSTGRADE.PERSON_REF doesnot exist..
0
Richiep86Author Commented:
I thought CTE_POSTGRADE.PERSON_REF does exist?

How do you kno it doesn't exist based on what I'v posted?

Many thanks!
0
Richiep86Author Commented:
Speedy, fast, helpful.

Thank you very much.
0
Geert GOracle dbaCommented:
in this system you can share points on multiple answers ...
just to let you know :)
0
Richiep86Author Commented:
Geeert! I was supposed to do that. I do apologise.

I'll request attention and see if I can distribute the points across multiple solutions.

You helped me alot, it's only right I do the same. Plus, I have several more questions I'd like your insight on!

Rich
0
Geert GOracle dbaCommented:
lol, don't bother, it was only a reminder
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.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.