Date: Fri, 11 Aug 2017 16:30:35 +0100
From: Jonathan Moules <jonathan-lists@lightpear.com>
To: Achilleas Mantzios <achill@matrix.gatewaynet.com>
Cc: <pgsql-sql@postgresql.org>
Message-Id: <15dd1ea396c.10c78e843106370.6070220804472515174@lightpear.com>
In-Reply-To: <893edec6-fc48-2635-aceb-11231c276424@matrix.gatewaynet.com>
References: <15dd19520d2.c2f4c716103038.4559674295652037989@lightpear.com>
 <893edec6-fc48-2635-aceb-11231c276424@matrix.gatewaynet.com>
Subject: Re: Always getting back a row, even with no results
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_253596_1592296799.1502465374575"
User-Agent: Zoho Mail
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

------=_Part_253596_1592296799.1502465374575
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: 7bit


Do smth like along the lines (might want to add additional code for ordering, this just happens to run correctly) :
 select * from my_table where cat = 500 UNION select null,null LIMIT CASE WHEN (select count(*) from my_table where cat=500)&gt;0 THEN (select count(*) from my_table where cat=500) ELSE 1 END ;
 Of course you can write a function to do that for you, but what made you want this in the first place? Maybe this is bad design ?


Unfortunately this is a constraint of the environment; not good design, but SQL seems like it should be the simplest place to solve this.

I tried your example; it seems the ordering is required, otherwise the results always include the null. So the below works for anyone who finds this in the future. Thanks


select
            id
from
            my_table
where
            cat = 2
 
UNION
 
select
            null as id
ORDER BY
            id ASC
LIMIT
    CASE
            WHEN (
                select
                        count(*)
                from
                        my_table
                where
                        cat = 2
                ) &gt; 0
            THEN (
                select count(*) from my_table where cat=2
                )
            ELSE
                1
    END;


------=_Part_253596_1592296799.1502465374575
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"><html><head>=
<meta content=3D"text/html;charset=3DUTF-8" http-equiv=3D"Content-Type"></h=
ead><body ><div style=3D'font-size:10pt;font-family:Verdana,Arial,Helvetica=
,sans-serif;'><br>Do smth like along the lines (might want to add additiona=
l code for     ordering, this just happens to run correctly) :<br><div clas=
s=3D"zmail_extra"><blockquote style=3D"border-left: 1px solid #0000FF; padd=
ing-left: 6px; margin:0 0 0 5px"><div>     select * from my_table where cat=
 =3D 500 UNION select null,null LIMIT     CASE WHEN (select count(*) from m=
y_table where cat=3D500)&gt;0 THEN     (select count(*) from my_table where=
 cat=3D500) ELSE 1 END ;<br>     Of course you can write a function to do t=
hat for you, but what made     you want this in the first place? Maybe this=
 is bad design ?<br><br></div></blockquote><br><div>Unfortunately this is a=
 constraint of the environment; not good design,  but SQL seems like it sho=
uld be the simplest place to solve this.<br><br>I tried your example; it se=
ems the ordering is required, otherwise the results always include the null=
. So the below works for anyone who finds this in the future. Thanks<br><br=
><br>select<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp; id<br>from<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp; my_table<br>where<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp=
;&nbsp;&nbsp;&nbsp;&nbsp; cat =3D 2<br>&nbsp;<br>UNION<br>&nbsp;<br>select<=
br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; null =
as id<br>ORDER BY<br>&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;=
&nbsp;&nbsp; id ASC<br>LIMIT<br>&nbsp;&nbsp;&nbsp; CASE<br>&nbsp;&nbsp;&nbs=
p; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; WHEN (<br>&nbsp;&nbsp;&nbsp; &nbsp=
;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; select<br>&nbsp;&nbsp;&=
nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;=
&nbsp; &nbsp;&nbsp;&nbsp; count(*)<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp;=
 &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; from<br>&nbsp;&nbsp;&nbsp; &nbsp;&nb=
sp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&n=
bsp;&nbsp; my_table<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&n=
bsp; &nbsp;&nbsp;&nbsp; where<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbs=
p;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; cat=
 =3D 2<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&n=
bsp;&nbsp; ) &gt; 0<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&n=
bsp; THEN (<br>&nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nb=
sp;&nbsp;&nbsp; select count(*) from my_table where cat=3D2<br>&nbsp;&nbsp;=
&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; )<br>&nbsp;=
&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; ELSE<br>&nbsp;&nbsp;&nbs=
p; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; &nbsp;&nbsp;&nbsp; 1<br>&nbsp;&nbs=
p;&nbsp; END;<br></div></div></div></body></html>
------=_Part_253596_1592296799.1502465374575--