How to assign a value to all rows based on condition of one row? (see details)

Question

I have a table that looks like this:

Category	Sub Category	Calculation
Furniture	Chair	1
Furniture	Table	1
Stationery	Pen	0
Stationery	Pencil	0

I'm trying to come up with the third column 'Calculation' which will check for 'Table' in the data and whichever category has 'Table' in it's sub-category, all rows of that category will be 1.

I wrote a query like this:

CASE WHEN select category in (select category from table where sub-category='Table') THEN 1 ELSE 0 END Calculation

The above works but it increases the runtime of query from 1 min to 50 mins and it times out at times. Is there a less intensive way of writing the above query? The only condition is that whenever 'Table' comes up then all the rows belonging to parent 'Category' will be 1 under Calculation.

Thanks in advance

Answer 1

One of the options is to use EXISTS in CASE expression:

WITH
    tbl (CATEGORY, SUB_CATEGORY) AS
        (
            Select 'Furniture',   'Chair'   From Dual Union All
            Select 'Furniture',   'Table'   From Dual Union All
            Select 'Stationery',  'Pen'     From Dual Union All
            Select 'Stationery',  'Pencil'  From Dual 
        )
Select    CATEGORY, SUB_CATEGORY, 
          CASE  WHEN EXISTS(Select 1 From tbl Where CATEGORY = t.CATEGORY And SUB_CATEGORY = 'Table') 
                THEN 1 
          ELSE 0 
          END "CALCULATION"
From      tbl t
Order By  CATEGORY, SUB_CATEGORY

CATEGORY   SUB_CATEGORY CALCULATION
---------- ------------ -----------
Furniture  Chair                  1
Furniture  Table                  1
Stationery Pen                    0
Stationery Pencil                 0

Another option that could be faster:

Select    t.CATEGORY, t.SUB_CATEGORY, Nvl(t2.CALC, 0) "CALCULATION"
From      tbl t
Left Join (Select CATEGORY, 1 "CALC" From tbl Where SUB_CATEGORY = 'Table' Group By CATEGORY) t2 ON(t2.CATEGORY = t.CATEGORY)
Order By  t.CATEGORY, t.SUB_CATEGORY