Hi EE,
This is a project for work. I work for a school.
I want to pull students from ACADEMIC_STUDY, and ADDRESS_CURRENT tables.
The ADDRESS_CURRENT table can have many different rows for ONE student, because we carry many different addresses.
MA - mailing address
BU - business address (for staff members)
FA - for financial aid to be sent to
So since I am pulling students, i want MA information. If the student DID NOT provide mailing info. They would not have a MA row, but i still want to include them.
Sample data
ACADEMIC_STUDY
1111111 Washington, George
2222222 Lincoln, Abraham
ADDRESS_CURRENT
1111111 Washington, George MA
2222222 LIncoln, Abraham BU
results set
1111111 Washington, George MA
2222222 Lincoln, Abraham null
I coded IN ('MA', NULL), but I think Oracle is handling this as an EQUI JOIN and i am not getting the unmatched records
Do I have to code the WHERE CLAUSE in a special way like with a + sign?
tx for your help, sandra
======
select ACADEMIC_STUDY.ACADEMIC_YEAR,
ACADEMIC_STUDY.ACADEMIC_YEAR_DESC,
ACADEMIC_STUDY.ACADEMIC_PERIOD,
ACADEMIC_STUDY.ACADEMIC_PERIOD_DESC,
ACADEMIC_STUDY.ID,
ACADEMIC_STUDY.NAME,
ADDRESS_CURRENT.ADDRESS_TYPE,
ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_Uid,
'201330',
'N',
ACADEMIC_STUDY.Program) as Calc_MaxTerm
from ODSMGR.DMC_V_ACADEMIC_STUDY_WITH_ATTR ACADEMIC_STUDY
,ODSMGR.ADDRESS_CURRENT ADDRESS_CURRENT
where ACADEMIC_STUDY.ACADEMIC_PERIOD =
ODSMGR.DMC_MAX_TERM_FOR_STUDENTS_PRGM(ACADEMIC_STUDY.Person_Uid,
'201330',
'N',
ACADEMIC_STUDY.Program)
and ACADEMIC_STUDY.PERSON_UID = ADDRESS_CURRENT.ENTITY_UID(+)
AND ACADEMIC_STUDY.COLLEGE = 'MD'
AND ACADEMIC_STUDY.STUDENT_STATUS IN ('AS', 'LA')
AND ADDRESS_CURRENT.ADDRESS_TYPE IN ('MA', NULL)
order by
ACADEMIC_STUDY.ACADEMIC_PERIOD
,ACADEMIC_STUDY.NAME
Source: http://www.techrepublic.com/article/oracle-tip-understand-how-nulls-affect-in-and-exists/#.
Just remove NULL from IN