Postgresql, case, select in when

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.

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…