Re: [SQL] Always getting back a row, even with no results

with x as 

(select id,1 mark from my_table where cat = 3

union all

select null,0 mark)

select id from x where mark = (select max(mark) from x) alia;

 

Don't have SQL right now so can't test it.

On Fri, Aug 11, 2017 at 8:43 AM, David G. Johnston <david.g.johnston@gmail.com> wrote:

On Fri, Aug 11, 2017 at 8:36 AM, Jonathan Moules <jonathan-lists@lightpear.com> wrote:

Hi David,
I'm afraid I don't really understand this response (I've not done much with arrays), but it doesn't seem to work for my purpose.

No NULL is returned if there is no result (i.e. cat = 50); instead, there's simply no rows.

What aspect of Arrays is this trying to take advantage of?

​My bad, I had tested the "false" version with a single record, without the array, and it indeed works.  But the scalar subselect prevents the inner query from returning more than one row.  I added the array to handle the multiple rows setup (which required testing the true path) and forgot to go back and test the false path.

The idea of the array was to keep the inner subquery scalar.

The following works on 9.5 - not positive whether it will on 10 though, we made some changes in this area.

SELECT 

unnest(

COALESCE(

(SELECT array_agg(col) FROM ( VALUES (1), (2) ) vals (col) WHERE true),

 ARRAY[null]::int[]

)

);

I liked this!
Also an array solution, since it was mentioned, first an existing example, then a non-existing :
smadev dynacom=# select unnest(CASE WHEN arr='{}' THEN '{null}' ELSE arr END) FROM (select ARRAY(select id from flags where id=221) as arr) qry;
 unnest
--------
    221
(1 row)

smadev dynacom=#
smadev dynacom=# select unnest(CASE WHEN arr='{}' THEN '{null}' ELSE arr END) FROM (select ARRAY(select id from flags where id=-221) as arr) qry;
 unnest
--------
      
(1 row)

smadev dynacom=#

David J.

-- 
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt