If/then

[LKBrwn_DBA]

Try this (not tested):

SELECT   A.Passport_listing_id, A.Passport_registration_id,
         COALESCE (B.Email, C.Email) Email,
         COALESCE ( (B.Major1), C.Dept) Major,
         CASE
             WHEN B.Major2 IS NOT NULL OR B.Minor IS NOT NULL
             THEN
                    B.Major1
                 || DECODE (B.Major2, NULL, '', ',' || B.Major2)
                 || DECODE (B.Minor, NULL, '', ',' || B.Minor)
         END AS A_major, 
         E.Passport_survey_id, F.Title, F.Start_date
  FROM                       Passport_registration A
                         LEFT OUTER JOIN
                             Students B
                         ON A.Student_id = B.Student_id
                     LEFT OUTER JOIN
                         People C
                     ON A.People_id = C.Id
                 LEFT OUTER JOIN
                     Passport_attendance D
                 ON A.Passport_listing_id = D.Passport_listing_id
                    AND A.S_uid = D.S_uid
             LEFT OUTER JOIN
                 Passport_survey E
             ON A.Passport_listing_id = E.Passport_listing_id
                AND A.S_uid = E.S_uid
         LEFT OUTER JOIN
             Passport_listing F
         ON A.Passport_listing_id = F.Passport_listing_id
 WHERE   A.S_uid = #s_uid#