Re: if-clause to an exiting statement

hi i am a newbie to sql statments , I am running postgres 8.1 with
application called opennms version 1.8.5 due to an application bug
queries that I execute aginst the DB which returns raw-count=0 are being
ignored and will not process a certain trigger I need to process.

My question is : Can I use an if-clause into my statement (see below)
which will check if the returned raw-count =0 then will return
raw-count=1 ?
----------------------------------------------------------------------------------
SELECT a.eventuei AS _eventuei,
a.nodeid AS _nodeid,
a.ipaddr AS _ipaddr,
now() AS _ts
FROM events a
WHERE
a.eventuei='uei.opennms.org/comns/backup-success-trap' AND
(eventcreatetime > now() - interval '10 minutes')
----------------------------------------------------------------------------------

Thanks!

lookup CASE WHEN END in docs.

--
GJ

hi thanks for the reply I did look at the CASE statement however cannot seem
to alter the returned row-count ...

CASE WHEN (@@ROW-COUNT = 0) THEN

[what do I write here?] @@ROW-COUNT = 1?

END
--
View this message in context: http://postgresql.1045698.n5.nabble.com/if-clause-to-an-exiting-statement-tp3295519p3295641.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

On Tuesday 07 December 2010 3:58:46 am kobi.biton wrote:
> hi thanks for the reply I did look at the CASE statement however cannot
> seem to alter the returned row-count ...
>
> CASE WHEN (@@ROW-COUNT = 0) THEN
>
> [what do I write here?] @@ROW-COUNT = 1?
>
> END
> --
> View this message in context:
> http://postgresql.1045698.n5.nabble.com/if-clause-to-an-exiting-statement-t
>p3295519p3295641.html Sent from the PostgreSQL - general mailing list
> archive at Nabble.com.

See below:

test=> SELECT count(*) from bool_test ;
count
-------
33
(1 row)

test=> SELECT count(*) ,
case count(*) when 0
then 1
else
count(*)
end
from
bool_test ;

count | count
-------+-------
33 | 33
(1 row)

test=> SELECT count(*) ,
case count(*) when 0
then 1
else
count(*)
end
from
bool_test
where
ifd=0;

count | count
-------+-------
0 | 1
(1 row)

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

Adrian hi,

Thanks for the reply can you please show me how to incorporate the below
into my below statement ?

SELECT a.eventuei AS _eventuei,
a.nodeid AS _nodeid,
a.ipaddr AS _ipaddr,
now() AS _ts
FROM events a
WHERE
eventuei='uei.opennms.org/comns/backup-success-trap' AND
(eventcreatetime > now() - interval '10 minutes')

Thanks!
Kobi

On Tue, 2010-12-07 at 07:36 -0800, Adrian Klaver wrote:
> On Tuesday 07 December 2010 3:58:46 am kobi.biton wrote:
> > hi thanks for the reply I did look at the CASE statement however cannot
> > seem to alter the returned row-count ...
> >
> > CASE WHEN (@@ROW-COUNT = 0) THEN
> >
> > [what do I write here?] @@ROW-COUNT = 1?
> >

> > END
> > --
> > View this message in context:
> > http://postgresql.1045698.n5.nabble.com/if-clause-to-an-exiting-statement-t
> >p3295519p3295641.html Sent from the PostgreSQL - general mailing list
> > archive at Nabble.com.
>
> See below:
>
> test=> SELECT count(*) from bool_test ;
> count
> -------
> 33
> (1 row)
>
> test=> SELECT count(*) ,
> case count(*) when 0
> then 1
> else
> count(*)
> end
> from
> bool_test ;
>
> count | count
> -------+-------
> 33 | 33
> (1 row)
>
> test=> SELECT count(*) ,
> case count(*) when 0
> then 1
> else
> count(*)
> end
> from
> bool_test
> where
> ifd=0;
>
> count | count
> -------+-------
> 0 | 1
> (1 row)
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)gmail(dot)com
>

--
Kobi Biton
Com N S Ltd.

Mobile: +972 (54) 8017668

On Dec 7, 2010, at 4:58 AM, kobi.biton wrote:
>
> hi thanks for the reply I did look at the CASE statement however cannot seem
> to alter the returned row-count ...

Well, yeah. The row count is the count of rows returned. If there are no rows matched by the query, then what exactly do you expect to happen? Set the row count to 1, so that the application then tries to access the 1st row of 0???

If you need some dummy row returned even in the case where there's no match, then you'll have to construct your query that way...

--
Scott Ribe
scott_ribe(at)elevated-dev(dot)com
http://www.elevated-dev.com/
(303) 722-0567 voice

Scott hi,

I know it does not sound logic however I do need to set the row count
to 1 in case row count returns 0 , can you show how to add that case
clause and dummy line in my below code ?

SELECT a.eventuei AS _eventuei,
a.nodeid AS _nodeid,
a.ipaddr AS _ipaddr,
now() AS _ts
FROM events a
WHERE
a.eventuei='uei.opennms.org/comns/backup-success-trap' AND
(eventcreatetime > now() - interval '10 minutes')

Thanks !!!!
Kobi

On Tue, 2010-12-07 at 09:51 -0700, Scott Ribe wrote:
> On Dec 7, 2010, at 4:58 AM, kobi.biton wrote:
> >
> > hi thanks for the reply I did look at the CASE statement however cannot seem
> > to alter the returned row-count ...

>
> Well, yeah. The row count is the count of rows returned. If there are no rows matched by the query, then what exactly do you expect to happen? Set the row count to 1, so that the application then tries to access the 1st row of 0???
>
> If you need some dummy row returned even in the case where there's no match, then you'll have to construct your query that way...
>
> --
> Scott Ribe
> scott_ribe(at)elevated-dev(dot)com
> http://www.elevated-dev.com/
> (303) 722-0567 voice
>
>
>
>
>

--
Kobi Biton
Com N S Ltd.

Mobile: +972 (54) 8017668

On Dec 7, 2010, at 9:58 AM, Kobi Biton wrote:
>
> I know it does not sound logic however I do need to set the row count
> to 1 in case row count returns 0

Perhaps I didn't make myself clear: you can't do that. The only thing you can do is make sure your query returns a row, and in the case where it currently doesn't return a row I have absolutely no idea what it would be that you would need to return.

If it would be acceptable to always return some hard-wired dummy row in addition to the 0 or more rows that match the current query, then you could use a UNION to add the dummy row to the selection. Otherwise, perhaps the real problem is that you do not have a matching event in the database and the real solution is to add such an event.

In your original post you referred to an application bug where a trigger does not run if the row count is 0. It's hard for me to imagine how it's a bug to not take action when there is no event that needs processing...

--
Scott Ribe
scott_ribe(at)elevated-dev(dot)com
http://www.elevated-dev.com/
(303) 722-0567 voice

I know it does not make sens "application bug" however consider the
following scenarion , looking at the Statement I sent I would like to
check if over the last 10 minutes a certain type of event was logged and
if NOT (row-count=0) then I would like to trigger and action.

hope it makes more sense.

Kobi.
On Tue, 2010-12-07 at 10:44 -0700, Scott Ribe wrote:
> On Dec 7, 2010, at 9:58 AM, Kobi Biton wrote:
> >
> > I know it does not sound logic however I do need to set the row count
> > to 1 in case row count returns 0
>
> Perhaps I didn't make myself clear: you can't do that. The only thing you can do is make sure your query returns a row, and in the case where it currently doesn't return a row I have absolutely no idea what it would be that you would need to return.
>
> If it would be acceptable to always return some hard-wired dummy row in addition to the 0 or more rows that match the current query, then you could use a UNION to add the dummy row to the selection. Otherwise, perhaps the real problem is that you do not have a matching event in the database and the real solution is to add such an event.
>
> In your original post you referred to an application bug where a trigger does not run if the row count is 0. It's hard for me to imagine how it's a bug to not take action when there is no event that needs processing...
>
> --
> Scott Ribe
> scott_ribe(at)elevated-dev(dot)com
> http://www.elevated-dev.com/
> (303) 722-0567 voice
>
>
>
>
>

--
Kobi Biton
Com N S Ltd.

Mobile: +972 (54) 8017668

On 12/07/2010 08:04 AM, Kobi Biton wrote:
> Adrian hi,
>
> Thanks for the reply can you please show me how to incorporate the below
> into my below statement ?
>
> SELECT a.eventuei AS _eventuei,
> a.nodeid AS _nodeid,
> a.ipaddr AS _ipaddr,
> now() AS _ts
> FROM events a
> WHERE
> eventuei='uei.opennms.org/comns/backup-success-trap' AND
> (eventcreatetime> now() - interval '10 minutes')
>
> Thanks!
> Kobi
>

>>
>

Not sure this is what you want but here, reminder count(*) can have
performance issues for large values of count():

SELECT a.eventuei AS _eventuei,
case count(*) when 0
then 1
else
count(*)
end
AS _ct,
a.nodeid AS _nodeid,
a.ipaddr AS _ipaddr,
now() AS _ts
FROM events a
WHERE
eventuei='uei.opennms.org/comns/backup-success-trap' AND
(eventcreatetime> now() - interval '10 minutes')

--
Adrian Klaver
adrian(dot)klaver(at)gmail(dot)com

On 2010-12-07, Kobi Biton <kobi(at)comns(dot)co(dot)il> wrote:
> hi i am a newbie to sql statments , I am running postgres 8.1 with
> application called opennms version 1.8.5 due to an application bug
> queries that I execute aginst the DB which returns raw-count=0 are being
> ignored and will not process a certain trigger I need to process.

I think you want this:

ORIGINAL QUERY
union
select DUMMY ROW DATA
where
not exists ( ORIGINAL QUERY )

you need to return something to get a rowcount of 1 this is what the
dummy row data provides. the "where not exists" part blocks the dummy
row data when the main query returns something.

--
⚂⚃ 100% natural

Jasen Betts wrote:
> On 2010-12-07, Kobi Biton <kobi(at)comns(dot)co(dot)il> wrote:
>
>> hi i am a newbie to sql statments , I am running postgres 8.1 with
>> application called opennms version 1.8.5 due to an application bug
>> queries that I execute aginst the DB which returns raw-count=0 are being
>> ignored and will not process a certain trigger I need to process.
>>
>
> I think you want this:
>
> ORIGINAL QUERY
> union
> select DUMMY ROW DATA
> where
> not exists ( ORIGINAL QUERY )
>
>
> you need to return something to get a rowcount of 1 this is what the
> dummy row data provides. the "where not exists" part blocks the dummy
> row data when the main query returns something.
>
>

Simple enough, but I suspect it runs the same query twice, so I hope
it's not to expensive. I wonder what the app is/was doing with the
vacuous single row or will do with the dummy data row? Seems the
app/trigger simply needs to know the execution of the query was
successful irrespective of the actual row count - or is that the bug
with "1.8.5"?