CODEWITHSUNDEEP
sqloracle-database
Georg
Georg

Reputation: 104

How to make a boolean column from another column with condition

I have to reach the best performance!

So, i have a table (my_table1) where are 3 columns: ID, RID(primary_key), VALUE1. I want to make a new boolean column in another table (my_table2).

I decode-d my VALUE1 column, so its output can be 1 or NULL.

 RID    |   ID   | VALUE1
--------|--------|--------
 132501 |  1001  |   1
 132501 |  1002  |   
 132501 |  1003  |   
 132501 |  1004  |   1
 132501 |  1005  |   
 132501 |  1006  |   1

If all values of VALUE1 column is 1 i want to get 1. If ANY value of VALUE1 column is NULL i want to get 0.

In this specific case output be like - (my_table2):

 RID_2  |ID_TBL_2| NEW_BOOL_COL
--------|--------|--------
 132501 |  3214  |   0

What's the best way to solve this problem ?

Upvotes: 2

Views: 3817

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521113

Here is an answer which aggregates VALUE1 based on the RID:

SELECT
    RID,
    CASE WHEN COUNT(VALUE1) = COUNT(*) THEN 1 ELSE 0 END AS NEW_BOOL_COL
FROM my_table1
GROUP BY RID;

You may easily enough use this logic in the context of joining to another table.

Upvotes: 6

Kaushik Nayak
Kaushik Nayak

Reputation: 31648

There's no boolean column type for tables in Oracle. I think you're looking for an Integer.

You may use a correlated update with a count query from my_table1

update my_table2 t2 set NEW_BOOL_COL = 
( select 
  case when count( case when value1 = 1 then 1 end ) = 
    count(*) then 1 else 0 end 
from my_table1 t1 where t1.rid = t2.rid_2 
group by t1.rid
);

DEMO

Upvotes: 1

Related Questions