CODEWITHSUNDEEP
sqlpostgresql
srghma
srghma

Reputation: 5323

refactor duplication of SELECT inside CASE inside SELECT statement

how to refactor this select statement to remove duplication

select
    (
      case
      when (
        select my_product_int_attribute -- duplication
        from app_public.products
        where product_id = entry.product_id
      ) > 0 then 1
      when (
        select my_product_int_attribute -- duplication
        from app_public.products
        where product_id = entry.product_id
      ) = 0 then 0
      else null
      end
    )
from app_public.entries as entry
where entry.project_id = 1;

are there exists easy options without joins, maybe something like with statements that can be used inside select?

N.B.

project has many entries, entry has one product

Upvotes: 0

Views: 52

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269623

You could use sign():

  nullif(sign( (select p.my_product_int_attribute
                from app_public.products p
                where p.product_id = entry.product_id
               ) 
             ), -1
          )

However, a left join -- such as sgeddes (now deleted) answer -- seems like the right approach.

Upvotes: 1

Faisal Mehboob
Faisal Mehboob

Reputation: 609

May be use CTE instead?

;with products as (
select products.product_id, products.my_product_int_attribute 
    from app_public.products
    group by products.product_id, products.my_product_int_attribute 
)
select
(
  case
  when products.my_product_int_attribute > 0 then 1
  when products.my_product_int_attribute = 0 then 0
  else null
  end
)
from app_public.entries as entry
left join products on products.product_id = entry.product_id
where entry.project_id = 1;

Upvotes: 2

Related Questions