Postgresql, case, select in when

Question

System

PostgreSQL 11.5 (Ubuntu 11.5-0ubuntu0.19.04.1) on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 8.3.0-6ubuntu1) 8.3.0, 64-bit

Case

Does select work in case condition? I need return 1 state from 4 states in case. I try to explain.

Shop has eshop (store_id = 7) and stores (store_id = 1 - 10, others, but not 7).

Eshop and every store have available_count - in stock.

Logic is:

When available_count > 1 where store_id = 7 (eshop) then in stock.

When available_count = 0 where store_id = 7 (eshop) and available_count > 1 where store_id != 7 (at least one value is greater than 1 for all stores, for eshop is 0) then in stock on store.

When available_count <= 1 where store_id = 7 (eshop) or available_count <= 1 where store_id != 7 (at least one value is 1, others are 0, but if all are 0, then not in stock) then on request.

When count_availability = 0 where store_id = 7 (eshop) and count_availability = 0 where store_id != 7 (all values are 0) then not in stock.

I tried

case
when (select ja.available_count where ja.store_id = '7') > 1 then 'IN STOCK'
when (select ja.available_count where ja.store_id = '7') = 0 and (select ja.available_count where ja.store_id != '7') > 1 then 'IN STOCK ON STORE'
when (select ja.available_count where ja.store_id = '7') <= 1 or (select ja.available_count where ja.store_id != '7') <= 1 then 'ON REQUEST'
when (select ja.available_count where ja.store_id = '7') = 0 and (select ja.available_count where ja.store_id != '7') = 0 then 'NOT IN STOCK'
end as "availability",

It returns more than one value for availability, just 'IN STOCK' or '[NULL]' and sometimes seems there is problem with right value.

Is betterr way to do that?

Thank you for any help.

Answer 1

1. You need one row per product. So GROUP BY needed. And the query will look like

   SELECT product_id, (expression for availability)
   FROM sourcetable
   GROUP BY product_id
   

2. You have 3 general variants for the amount - 0, 1 or >1 (if some record is absent we must assume its amount value is 0). For to formalize we will substract 1 and get the sign (it will compact the amount into 3 values of -1, 0 or +1). For store_id != 7 we can get max value only and ignore all another. So source data can be prepared in CTE, and final query will be

   WITH cte AS ( SELECT product_id, 
                        SIGN(MAX(CASE WHEN store_id != 7 
                                      THEN available_count 
                                      ELSE 0 END) -1) stock,
                        SIGN(MAX(CASE WHEN store_id = 7 
                                 THEN available_count 
                                 ELSE 0 END) -1) eshop 
                 FROM sourcetable
                 GROUP BY product_id )
   SELECT product_id,
          CASE stock * 3 + eshop 
              WHEN -4 THEN ...
              WHEN -3 THEN ...
              ...
              WHEN 3 THEN ...
              WHEN 4 THEN ...
          END AS availability
   FROM cte
   

Test this. Fill the variants in final CASE by yourself, please...