not returning 0 if value is NULL

All we need is an easy explanation of the problem, so here it is.

Everything works as intended, but function should return 0 if value is NULL. Currently it returns nothing (see problem at the end of the post).

How could I make to return 0 if no order is found?

‘orders’ Table

CREATE TABLE orders (
    id INT NOT NULL, 
    code INT NOT NULL, 
    service_id INT NOT NULL, 
    status CHARACTER VARYING(50), 
    creation TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    status_date TIMESTAMP,
    created_user INT
    );

Records

INSERT INTO orders 
(id, code, service_id, status, creation, status_date, created_user) 
VALUES 
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Fulfilled','2018-06-16 11:12','2018-06-18 14:08',1),
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2395,558152,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2396,558153,'Requested','2019-06-16 11:12','2019-06-18 14:08',1),
(100,2394,558151,'Requested','2019-06-16 11:12','2019-06-18 14:08',1);

Testing Statement

SELECT EXTRACT(YEAR FROM creation) AS year, 
                        orders.code, 
                        COUNT(orders.code) 
                FROM orders
                WHERE status = 'Requested' AND EXTRACT(YEAR FROM creation) = 2019 AND orders.code = 2394
                GROUP BY year, orders.code
                ORDER BY year ASC

Returns

year | code | count
:--- | ---: | ----:
2019 | 2394 |     3

Function

CREATE OR REPLACE FUNCTION 
yearly_orders(inyear numeric DEFAULT 0, incode INT DEFAULT 0) 
RETURNS TABLE (retyear numeric, retcode bigint, retcout bigint) AS 
$$
DECLARE
    retcount int DEFAULT 0;
    retcode int DEFAULT 0;
    retyear numeric DEFAULT 0;
BEGIN
    RETURN QUERY SELECT CAST(EXTRACT(year FROM creation) as numeric), 
                        CAST(orders.code as bigint), 
                        COUNT(orders.code)
                FROM orders
                WHERE status = 'Requested' 
                AND EXTRACT(YEAR FROM creation) = inyear 
                AND orders.code = incode
                GROUP BY EXTRACT(year FROM creation), orders.code
                ORDER BY retyear ASC;

END;
$$
LANGUAGE plpgsql;

Executing Function

select * from yearly_orders(2019, 1194);

Returns <– PROBLEM

retyear | retcode | retcout
------: | ------: | ------:
        |         |

SHOULD RETURN

retyear | retcode | retcout
------: | ------: | ------:
 0       |     0    |    0

db<>fiddle

The db<>fiddle to try this out can be found here.

The code was made by user:15356 (Here)

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

You are wanting GROUP BY and the COUNT aggregate to return a row with a zero value when there are otherwise no rows to return – GROUPing does not work like that. It combines what rows would have been returned if GROUP BY was not present, so no rows in that case.

You could add a second SELECT, combined with UNION ALL, that returns one row containing inyear, incode, 0 if there are zero matching rows (counted by sub-select). If there are rows this part returns nothing, if there are no matches it returns a row indicating that. Note that this may be quite inefficient.

Method 2

That is actually tricky, you could raise a message like in the example

CREATE OR REPLACE FUNCTION 
yearly_orders(inyear numeric DEFAULT 0, incode INT DEFAULT 0) 
RETURNS TABLE (retyear numeric, retcode bigint, retcout bigint) AS 
$$
DECLARE
    retcount int DEFAULT 0;
    retcode int DEFAULT 0;
    retyear numeric DEFAULT 0;
    row_cnt int DEFAULT 0;
BEGIN
    RETURN QUERY SELECT CAST(EXTRACT(year FROM creation) as numeric) , 
                        CAST(orders.code as bigint) , 
                        COUNT(orders.code) 
                FROM orders
                WHERE status = 'Requested' 
                AND EXTRACT(YEAR FROM creation) = inyear 
                AND orders.code = incode
                GROUP BY EXTRACT(year FROM creation), orders.code
                ORDER BY retyear ASC;
    GET DIAGNOSTICS row_cnt = ROW_COUNT;

   IF row_cnt =  0 THEN
      RAISE SQLSTATE '45000'   -- 5 ASCII chars
      USING MESSAGE = format('No orders found for year %s.',inyear);
   END IF;

END;
$$
LANGUAGE plpgsql;

select * from yearly_orders(2020, 2394);

ERROR:  No orders found for year 2020.
CONTEXT:  PL/pgSQL function yearly_orders(numeric,integer) line 20 at RAISE

db<>fiddle here

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Tags:

Leave a Reply