I am new to SQL (Oracle) and I am trying to write a statement that if Parent1 and Parent2 address is different, then I want Parent2 to insert in the next row with the same student information. The first part (before the CASE statement with the bottom part (FROM, WHERE, GROUP) work by itself but I wanted to being in Parent2 info if it's different from Parent1.
Thanks!
SELECT
MAX(STUDENTS.LAST_NAME) AS LASTNAME,
MAX(US.GUARDIAN1_FIRSTNAME || ' ' || US.GUARDIAN1_LASTNAME) AS PARENT1,
MAX(US.GUARDIAN2_FIRSTNAME || ' ' || US.GUARDIAN2_LASTNAME) AS PARENT2,
MAX(US.GUARDIAN1_ADDRESSSTREET) AS PARENTADDRESS
CASE
WHEN (MAX(US.GUARDIAN1_ADDRESSSTREET) <> MAX(US.GUARDIAN2_ADDRESSSTREET)
INSERT (MAX(STUDENTS.LAST_NAME) AS LASTNAME,
MAX(' ' || ' ') AS PARENT1,
MAX(US.GUARDIAN2_FIRSTNAME || ' ' || US.GUARDIAN2_LASTNAME) AS PARENT2,
MAX(US.GUARDIAN2_ADDRESSSTREET)
FROM
STUDENTS,
U_STUDENTSUSERFIELDS US
WHERE
US.studentsdcid = students.dcid
AND STUDENTS.ENROLL_STATUS = 0
AND STUDENTS.ENROLLMENT_SCHOOLID = 340
GROUP BY
STUDENTS.FAMILY_IDENT
I want it to look like this:
| LASTNAME | PARENT1
| PARENT2 | PARENTADDRESS
|
|---|
Smith | Jane Doe | John Smith | 1818 Street |
| Smith | | John Smith | 2525 Other Street |
| Jones | Alice Jones | Mike Jones | 111 Old Street |
| Johnson | Perl Johnson | Bob Johnson | 222 Sesame Street |
| Apples | Eddy Apples | Sally Apples | 555 Street |
| Apples | | Sally Apples | 333 Helmme Ave. |