Oracle SQL - CASE When condition for ANY record
I have a DB that creates a log table entry every time a document is started (opened). Each record contains the name Doc_Name, Doc_Owner, and Doc_Run_by (that is, the person who ran the document). I am trying to create a query to see the name of each document and whether it was launched by anyone other than the owner (i.e. regardless of whether it was split).
This is what the log table looks like:
+----------+-----------+------------+
| Doc_Name | Doc_Owner | Doc_Run_by |
+----------+-----------+------------+
| A | Bob | Bob |
| A | Bob | Joe |
| A | Bob | Sam |
| A | Bob | Matt |
| B | Matt | Matt |
| B | Matt | Bob |
| B | Matt | Joe |
| C | Sam | Sam |
| D | Bob | Bob |
| D | Bob | Matt |
+----------+-----------+------------+
This is what I want the query result to look like:
+----------+--------------+
| Doc_Name | Share_Status |
+----------+--------------+
| A | Y |
| B | Y |
| C | N |
| D | Y |
+----------+--------------+
It would be something like this (in pseudo sql):
SELECT distinct Doc_Name,
CASE
WHEN Doc_Owner <> Doc_Run_by for any record of each doc_name
THEN 'Y'
ELSE 'N'
END as Share_Status
FROM Log_Table;
Here's what I have so far:
SELECT distinct Doc_Name,
CASE
WHEN Doc_Owner <> Doc_Run_by
THEN 'Y'
ELSE 'N'
END as Share_Status
FROM Log_Table;
Current output:
+----------+--------------+
| Doc_Name | Share_Status |
+----------+--------------+
| A | Y |
| A | N |
| B | Y |
| B | N |
| C | N |
| D | Y |
| D | N |
+----------+--------------+
As you can see, it creates a separate entry for each case. I understand why it does this, but I cannot figure out how to get the desired result. Thanks for any help you can give.
source to share
You can use conditional aggregation:
SELECT Doc_Name,
CASE WHEN
COUNT(CASE WHEN Doc_Owner <> Doc_Run_by THEN 1 END) > 0 THEN 'Y'
ELSE 'N'
END AS Share_Status
FROM Log_Table
GROUP BY Doc_Name
If there is at least one record (in a group of records Doc_Name
) with Doc_Owner <> Doc_Run_by
, then it Share_Status
will be equal 'Y'
, otherwise it will be equal 'N'
.
source to share