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

On 12/08/2017 05:41, Michael Moore wrote:
> 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(dot)g(dot)johnston(at)gmail(dot)com <mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> wrote:
>
> On Fri, Aug 11, 2017 at 8:36 AM, Jonathan Moules <jonathan-lists(at)lightpear(dot)com <mailto:jonathan-lists(at)lightpear(dot)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