CODEWITHSUNDEEP
sqloracle-databaseduplicates
Ana Mar
Ana Mar

Reputation: 31

Identify records with two identical values in two different columns?

I have a table of student id's, major1, major2 and minor. I want to identify those records of students who registered twice for the same major. I need a function to select only those who have the same major like "FIN" in column Major1 and Major2. I have so far:

CREATE VIEW A5T5 AS
SELECT (firstname || ' ' || lastname)"FullName", StudentID AS "StudentID", Major1 as "DoubleDipping"
FROM A5
Group by Major1, Major2 ?????
Having count ?????
ORDER BY Major,LastName,FirstName;

Upvotes: 1

Views: 50

Answers (2)

Bob Jarvis - Слава Україні
Bob Jarvis - Слава Україні

Reputation: 50017

I think you're making this harder than it is. If I understand your question correctly the following query should give you what you're looking for:

SELECT (firstname || ' ' || lastname) AS "FullName",
       StudentID AS "StudentID",
       Major1 as "DoubleDipping"
  FROM A5
  WHERE MAJOR1 = MAJOR2

If you really need this to be a function, the usual way to return a result set would be to return an opened SYS_REFCURSOR which the caller would then be responsible for closing. For example:

CREATE OR REPLACE FUNCTION DOUBLE_DIPPING_STUDENTS
    RETURN SYS_REFCURSOR
IS
  csr SYS_REFCURSOR;
BEGIN
  OPEN csr FOR SELECT (firstname || ' ' || lastname) AS "FullName",
                      StudentID AS "StudentID",
                      Major1 as "DoubleDipping"
                 FROM A5
                 WHERE MAJOR1 = MAJOR2;

  RETURN csr;
END DOUBLE_DIPPING_STUDENTS;

The above function might be called from a PL/SQL block as:

DECLARE
  csr       SYS_REFCURSOR;
  strNAME   VARCHAR2(2000);
  nID       A5.STUDENTID%TYPE;
  strMAJOR  A5.MAJOR1%TYPE;
BEGIN
  csr := DOUBLE_DIPPING_STUDENTS;

  LOOP
    FETCH csr
      INTO NAME, ID, MAJOR;

    WHEN csr%NOTFOUND THEN EXIT;

    DBMS_OUTPUT.PUT_LINE(strNAME || '  ' || nID || '  ' || strMAJOR);
  END LOOP;

  CLOSE csr;
END;

Upvotes: 1

John3136
John3136

Reputation: 29266

Why isn't it as simple as:

SELECT firstname, lastname FROM A5 WHERE Major1 = Major2

Upvotes: 1

Related Questions