Thread: 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.klaver@gmail.com
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 doyou 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 thatway... -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
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, andin the case where it currently doesn't return a row I have absolutely no idea what it would be that you would need toreturn. If it would be acceptable to always return some hard-wired dummy row in addition to the 0 or more rows that match the currentquery, then you could use a UNION to add the dummy row to the selection. Otherwise, perhaps the real problem is thatyou 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 forme to imagine how it's a bug to not take action when there is no event that needs processing... -- Scott Ribe scott_ribe@elevated-dev.com http://www.elevated-dev.com/ (303) 722-0567 voice
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.klaver@gmail.com
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 needto 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 currentquery, then you could use a UNION to add the dummy row to the selection. Otherwise, perhaps the real problem is thatyou 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 hardfor me to imagine how it's a bug to not take action when there is no event that needs processing... > > -- > Scott Ribe > scott_ribe@elevated-dev.com > http://www.elevated-dev.com/ > (303) 722-0567 voice > > > > > -- Kobi Biton Com N S Ltd. Mobile: +972 (54) 8017668
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
doyou 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
thatway...
>
> --
> Scott Ribe
> scott_ribe@elevated-dev.com
> http://www.elevated-dev.com/
> (303) 722-0567 voice
>
>
>
>
>
--
Kobi Biton
Com N S Ltd.
Mobile: +972 (54) 8017668
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.klaver@gmail.com
>
--
Kobi Biton
Com N S Ltd.
Mobile: +972 (54) 8017668
On 2010-12-07, Kobi Biton <kobi@comns.co.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@comns.co.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"?