Re: Select count(*) problem

"COOPER, Joseph" wrote: > > > > Or if using 7.3+ try > > > > > > SELECT FIRST 1 DBINFO('sqlca.sqlerrd2')> > > > > > > from table_x > > > > where > > > > column_a=12345 > > > > group by column_b > > > > > > HAVING count(*) > 200 > > > > That's NOT the coolest answer on this thread so far. :-( > > > > Selecting the number of rows processed for the first record only must > > surely return 1. > > Well, I'm using AIX and it has shown me a few funnys but ... > > SELECT FIRST 1 DBINFO('sqlca.sqlerrd2') > FROM sysindexes > WHERE tabid < 100 > GROUP BY tabid > HAVING count(*) > 2> > Definitely doesn't return 1 . > > The funny thing is that the hardest part of this SQL was > choosing one that anybody should be able to try :-) > And I have a funny feeling that I'm going to get replies > saying this doesn't work on ver X.XX using OS XXX Funny you should say that.... I get 0, yes zero, the first time, then I get 1, one every time after that. Keep digging, the hole's getting bigger... Okay, it took me a while, and I admit I did RTFM, but I understand it now. You have to read carefully, but it seems that sqlca.sqlerrd2 contains the number of rows processed in your LAST statement. So it may appear to work if your previous SELECT returned the correct number. To test this, run it more than once!! :-) So I am afraid using sqlca.sqlerrd2 is not an option unless it is in a subsequent SELECT, but then you might as well use COUNT(*). ;-) So if you'd left it at the cool post, you would have got away with it. :-) Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@mydas.freeserve.co.uk |//////// /| | http://www.informix.com http://www.informixhandbook.com |///// / //| | http://www.iiug.org +-----------------------------------+//// / ///| | |This email will self-destruct in |/// / ////| | |10 sec. If you received this email |// / /////| | |in error, sorry about the mess. |/ ////////| +----------------------+-----------------------------------+-----------+

In article <8geqpa$f2p$1@news.xmission.com>, Mark D. Stock <mdstock@myda s.freeserve.co.uk> writes > >So I am afraid using sqlca.sqlerrd2 is not an option unless it is in a >subsequent SELECT, but then you might as well use COUNT(*). ;-) > At least under 4gl sqlca.sqlerrd[2] (or whichever one has the count of in rows processed it) is NOT defined to work for select statements! >So if you'd left it at the cool post, you would have got away with it. >:-) > >Cheers, -- David Williams