I'm trying to pull data relating to a certain type of customer ID. For one of the columns, I need to know only whether there is a value present or not, while still returning all values for the rest of the columns. E.g.

I'm trying to pull data relating to a certain type of customer ID. For one of the columns, I need to know only whether there is a value present or not, while still returning all values for the rest of the columns. E.g.

In the last column I only want 'Yes' or 'No' values to appear. If there is a value, then 'Yes' else 'No'.

I have been searching for hours, and I found that others have used "select case when exists" but I don't understand how I could make that work for me in this scenario.

To get the values, I need to reference 2 tables. This is the query I used to pull all of the data.

SELECT a.Customer_Type, b.ID_Country, b.ID_Type, b.ID_Number, count(\*)  
FROM table a, table b  
WHERE a.Table_A_Customer_Number = b.Table_B_Customer_Number  
GROUP BY a.Customer_Type, b.ID_Country, b.ID_Type, b.ID_Number

This has given me all values for all columns, where I'd like to only see 'Yes' or 'No' for b.ID_Number.

• oracle-database
• case

1 Answer 1

That looks like a case expression indeed, but without exists.

  SELECT a.customer_type,
         b.id_country,
         b.id_type,
         b.id_number,
         COUNT (*),
         CASE WHEN b.id_number IS NOT NULL THEN 'Yes' ELSE 'No' END flag
    FROM tablea a, tableb b
   WHERE a.table_a_customer_number = b.table_b_customer_number
GROUP BY a.customer_type,
         b.id_country,
         b.id_type,
         b.id_number;

Or, as Tim suggested in a comment, join tables explicitly and leave where clause for filtering purposes (if any):

  SELECT a.customer_type,
         b.id_country,
         b.id_type,
         b.id_number,
         COUNT (*),
         CASE WHEN b.id_number IS NOT NULL THEN 'Yes' ELSE 'No' END flag
    FROM tablea a join tableb b 
         ON a.table_a_customer_number = b.table_b_customer_number
GROUP BY a.customer_type,
         b.id_country,
         b.id_type,
         b.id_number;