0

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.

enter image description here

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.

0
  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...

  • Thanks, but I am using one cte already. Is possible use more ctes? I tried add new cte as cte2 under cte I have already, but it shows me syntax error near WITH. – genderbee Sep 2 at 11:54
  • @genderbee You can use as muct CTEs as you need. Separate them by commas: WITH cte1 AS (...), cte2 AS (...), ..., cteN AS (...) {main query}. Moreover, CTE (and main query) can refer to any previous CTEs as a data source in addition or instead of static table(s). – Akina Sep 2 at 11:58
  • Yes, I did it now. But in case I have for first cte with recursive? – genderbee Sep 2 at 11:59
  • @genderbee If at least one of your CTEs is recursive you MUST use WITH RECURSIVE, if none CTE is recursive you may use either WITH or WITH RECURSIVE. Remember that if you use WITH RECURSIVE then you have additional overhead to each your CTE, ever it is not recursive. CTE recursivity is a property of separate CTE, it is not dependent by the presence and structure of another CTEs. – Akina Sep 2 at 12:01
  • Thank you, seems it works. But I don't understand why is for example for -2 value IN STOCK? – genderbee Sep 2 at 13:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.