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.

+3


source to share


2 answers


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'

.

Demo here

+1


source


This is a dirty trick, but since there is Y

more than N

, you can take the query you have, group it with, Doc_Name

and select the maximum Share_Status

:



SELECT   Doc_Name, 
         MAX (CASE WHEN Doc_Owner <> Doc_Run_by
                   THEN 'Y'
                   ELSE 'N'
              END) as Share_Status
FROM     Log_Table
GROUP BY Doc_Name

      

0


source







All Articles