Thread: if-clause to an exiting statement

if-clause to an exiting statement

From
Kobi Biton
Date:
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!


Re: if-clause to an exiting statement

From
Grzegorz Jaśkiewicz
Date:
lookup CASE WHEN END in docs.



--
GJ

Re: if-clause to an exiting statement

From
"kobi.biton"
Date:
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.

Re: if-clause to an exiting statement

From
Adrian Klaver
Date:
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

Re: *****SPAM***** Re: if-clause to an exiting statement

From
Scott Ribe
Date:
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





Re: *****SPAM***** Re: if-clause to an exiting statement

From
Scott Ribe
Date:
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





Re: if-clause to an exiting statement

From
Adrian Klaver
Date:
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

Re: *****SPAM***** Re: if-clause to an exiting statement

From
Kobi Biton
Date:
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


Re: *****SPAM***** Re: if-clause to an exiting statement

From
Kobi Biton
Date:
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


Re: if-clause to an exiting statement

From
Kobi Biton
Date:
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


Re: if-clause to an exiting statement

From
Jasen Betts
Date:
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

Re: if-clause to an exiting statement

From
Rob Sargent
Date:

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"?