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.