Scenario: I have a bunch of students and their email addresses. There are some cases where a student may have multiple e-mail addresses. Initial check is to make sure its a 'WU' email address. In the rare event a student has multiple 'WU' email addresses, there's an indicator value that can be specified to determine the correct email address. Query is as follows:
SELECT E.preferred_ind, E.emal_code, E.email_address, A.*
FROM STUDENTS A
LEFT JOIN EMAILS E on A.id = E.id
WHERE E.emal_code = 'WU'
AND CASE
WHEN ((SELECT count(*)
FROM EMAILS E2
WHERE A.id = E2.id
AND E2.EMAL_CODE = 'WU') != 1)
THEN (
SELECT E.preferred_ind, E.emal_code, E.email_address, A.*
FROM STUDENTS A
LEFT JOIN EMAILS E on A.id = E.id
WHERE E.emal_code = 'WU'
AND E.status_ind = 'I' )
END
I try using a count to determine if they have multiple (more than 1) 'WU' email addresses. I keep getting the ORA-00929 error. I've tried rewriting my query several times but I am just not sure how to fix this. I have looked at other posts as well and haven't been able to gather much of a fix for mine. I may need to use a WITH statement? I'm not sure. ANy help on this would be great. Thanks in advance