Select count(0) from table

Does anyone knows from which (minimum) version/patch of informix server, statement:select count (0) from table_name works ? I need this function on IDS 11.50.xy but, as I see, this is only works from version 11.70 ? I know that select count (*) returns same value as select count (0) but I have some application which generate SQL select count(0) and I can't change that SQL ...

On Thu, Oct 4, 2012 at 6:40 AM, Ivan Zavis DBA <ivan.zavis@mi-system.co.rs>wrote: > Does anyone knows from which (minimum) version/patch of informix server, > statement:select count (0) from table_name works ? > > I need this function on IDS 11.50.xy but, as I see, this is only works > from version 11.70 ? > Interesting; I wasn't aware that it didn't work. I've just tried an IDS 11.50.FC8 and it is rejected as a syntax error. It likely means every version of IDS 11.50 rejects it. I don't happen to have an FC9 instance configured. I know that select count (*) returns same value as select count (0) but > I have some application which generate SQL select count(0) and I can't > change that SQL ... > The COUNT(0) notation is not very sensible code generation; COUNT(*) is every bit as efficient, if not more so. COUNT(column) has a specific meaning — ignore the nulls, unlike COUNT(*) — so that has a purpose, but COUNT(0) means 'count the number of times zero is not null in the selected rows'. People also write things like EXISTS ( SELECT 1 FROM ...) where 'SELECT *' works fine because the optimizer knows what it is doing. It looks like you're either going to have to change the code generator or the version of IDS, or perhaps use I-Spy to modify COUNT(0) into COUNT(*) on its way to the database. Of the options, I think changing the code generator would be easiest, but who knows. -- Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org "Blessed are we who can laugh at ourselves, for we shall never cease to be amused." --bcaec55243b05bfecd04cb3c9283

A new feature in 11.70.xC1 is In earlier releases, queries can call the built-in COUNT function to return the number of qualifying rows, or the total number of non-NULL values (or of unique non-NULL values) in a specified column. This release extends the domain of COUNT arguments to SQL expressions that other aggregates accept, including CASE expressions. Current restrictions on the arguments to other SQL aggregate functions also apply to COUNT. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 10/04/2012 07:28:44 AM: > From: "Jonathan Leffler" <jonathan.leffler@gmail.com> > To: ids@iiug.org > Date: 10/04/2012 07:35 AM > Subject: Re: Select count(0) from table [28430] > Sent by: ids-bounces@iiug.org > > On Thu, Oct 4, 2012 at 6:40 AM, Ivan Zavis DBA > <ivan.zavis@mi-system.co.rs>wrote: > > > Does anyone knows from which (minimum) version/patch of informix server, > > statement:select count (0) from table_name works ? > > > > I need this function on IDS 11.50.xy but, as I see, this is only works > > from version 11.70 ? > > > > Interesting; I wasn't aware that it didn't work. > > I've just tried an IDS 11.50.FC8 and it is rejected as a syntax error. It > likely means every version of IDS 11.50 rejects it. I don't happen to have > an FC9 instance configured. > > I know that select count (*) returns same value as select count (0) but > > I have some application which generate SQL select count(0) and I can't > > change that SQL ... > > > > The COUNT(0) notation is not very sensible code generation; COUNT(*) is > every bit as efficient, if not more so. COUNT(column) has a specific > meaning — ignore the nulls, unlike COUNT(*) — so that has a purpose, but > COUNT(0) means 'count the number of times zero is not null in the selected > rows'. > > People also write things like EXISTS ( SELECT 1 FROM ...) where 'SELECT *' > works fine because the optimizer knows what it is doing. > > It looks like you're either going to have to change the code generator or > the version of IDS, or perhaps use I-Spy to modify COUNT(0) into COUNT(*) > on its way to the database. Of the options, I think changing the code > generator would be easiest, but who knows. > > -- > Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> > Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org > "Blessed are we who can laugh at ourselves, for we shall never cease to be > amused." > > --bcaec55243b05bfecd04cb3c9283 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

On version 11.50.xC9 don't work too. "Generator" is The Microstrategy 9.3, so it would by hard to change the generator :) Jonathan Leffler <jonathan.leffler@gmail.com> wrote: >On Thu, Oct 4, 2012 at 6:40 AM, Ivan Zavis DBA ><ivan.zavis@mi-system.co.rs>wrote: > >> Does anyone knows from which (minimum) version/patch of informix >server, >> statement:select count (0) from table_name works ? >> >> I need this function on IDS 11.50.xy but, as I see, this is only >works >> from version 11.70 ? >> > >Interesting; I wasn't aware that it didn't work. > >I've just tried an IDS 11.50.FC8 and it is rejected as a syntax error. >It >likely means every version of IDS 11.50 rejects it. I don't happen to >have >an FC9 instance configured. > >I know that select count (*) returns same value as select count (0) but > >> I have some application which generate SQL select count(0) and I >can't >> change that SQL ... >> > >The COUNT(0) notation is not very sensible code generation; COUNT(*) is > >every bit as efficient, if not more so. COUNT(column) has a specific >meaning ignore the nulls, unlike COUNT(*) so that has a purpose, >but >COUNT(0) means 'count the number of times zero is not null in the >selected >rows'. > >People also write things like EXISTS ( SELECT 1 FROM ...) where 'SELECT >*' >works fine because the optimizer knows what it is doing. > >It looks like you're either going to have to change the code generator >or >the version of IDS, or perhaps use I-Spy to modify COUNT(0) into >COUNT(*) >on its way to the database. Of the options, I think changing the code >generator would be easiest, but who knows. > >-- >Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> >Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org >"Blessed are we who can laugh at ourselves, for we shall never cease to >be >amused." > >--bcaec55243b05bfecd04cb3c9283 > > >******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. -- Sent from my Android phone.

What I was trying to say before in my cryptic tone was that this was a new feature introduced in 11.70.XC1. The ability for count () to take expressions was added. I copied and pasted the new features text out of the manual, but of course you can not put any fancy formatted text on this group. John F. Miller III STSM, Embedability Architect miller3@us.ibm.com 503-578-5645 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 10/04/2012 08:12:14 AM: > From: "Ivan Zavis" <ivan.zavis@mi-system.co.rs> > To: ids@iiug.org, > Date: 10/04/2012 08:16 AM > Subject: Re: Select count(0) from table [28432] > Sent by: ids-bounces@iiug.org > > On version 11.50.xC9 don't work too. "Generator" is The Microstrategy 9.3, so > it would by hard to change the generator :) > > Jonathan Leffler <jonathan.leffler@gmail.com> wrote: > > >On Thu, Oct 4, 2012 at 6:40 AM, Ivan Zavis DBA > ><ivan.zavis@mi-system.co.rs>wrote: > > > >> Does anyone knows from which (minimum) version/patch of informix > >server, > >> statement:select count (0) from table_name works ? > >> > >> I need this function on IDS 11.50.xy but, as I see, this is only > >works > >> from version 11.70 ? > >> > > > >Interesting; I wasn't aware that it didn't work. > > > >I've just tried an IDS 11.50.FC8 and it is rejected as a syntax error. > >It > >likely means every version of IDS 11.50 rejects it. I don't happen to > >have > >an FC9 instance configured. > > > >I know that select count (*) returns same value as select count (0) but > > > >> I have some application which generate SQL select count(0) and I > >can't > >> change that SQL ... > >> > > > >The COUNT(0) notation is not very sensible code generation; COUNT(*) is > > > >every bit as efficient, if not more so. COUNT(column) has a specific > >meaning ignore the nulls, unlike COUNT(*) so that has a purpose, > >but > >COUNT(0) means 'count the number of times zero is not null in the > >selected > >rows'. > > > >People also write things like EXISTS ( SELECT 1 FROM ...) where 'SELECT > >*' > >works fine because the optimizer knows what it is doing. > > > >It looks like you're either going to have to change the code generator > >or > >the version of IDS, or perhaps use I-Spy to modify COUNT(0) into > >COUNT(*) > >on its way to the database. Of the options, I think changing the code > >generator would be easiest, but who knows. > > > >-- > >Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> > >Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org > >"Blessed are we who can laugh at ourselves, for we shall never cease to > >be > >amused." > > > >--bcaec55243b05bfecd04cb3c9283 > > > > > > >******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- > Sent from my Android phone. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Ok. Thanks. John Miller iii <miller3@us.ibm.com> wrote: >What I was trying to say before in my cryptic tone was that this was >a new feature introduced in 11.70.XC1. The ability for count () to >take expressions was added. I copied and pasted the new >features text out of the manual, but of course you can not >put any fancy formatted text on this group. > >John F. Miller III >STSM, Embedability Architect >miller3@us.ibm.com >503-578-5645 >IBM Informix Dynamic Server (IDS) > >ids-bounces@iiug.org wrote on 10/04/2012 08:12:14 AM: > >> From: "Ivan Zavis" <ivan.zavis@mi-system.co.rs> >> To: ids@iiug.org, >> Date: 10/04/2012 08:16 AM >> Subject: Re: Select count(0) from table [28432] >> Sent by: ids-bounces@iiug.org >> >> On version 11.50.xC9 don't work too. "Generator" is The Microstrategy > >9.3, so >> it would by hard to change the generator :) >> >> Jonathan Leffler <jonathan.leffler@gmail.com> wrote: >> >> >On Thu, Oct 4, 2012 at 6:40 AM, Ivan Zavis DBA >> ><ivan.zavis@mi-system.co.rs>wrote: >> > >> >> Does anyone knows from which (minimum) version/patch of informix >> >server, >> >> statement:select count (0) from table_name works ? >> >> >> >> I need this function on IDS 11.50.xy but, as I see, this is only >> >works >> >> from version 11.70 ? >> >> >> > >> >Interesting; I wasn't aware that it didn't work. >> > >> >I've just tried an IDS 11.50.FC8 and it is rejected as a syntax >error. >> >It >> >likely means every version of IDS 11.50 rejects it. I don't happen >to >> >have >> >an FC9 instance configured. >> > >> >I know that select count (*) returns same value as select count (0) >but >> > >> >> I have some application which generate SQL select count(0) and I >> >can't >> >> change that SQL ... >> >> >> > >> >The COUNT(0) notation is not very sensible code generation; COUNT(*) >is >> > >> >every bit as efficient, if not more so. COUNT(column) has a specific > >> >meaning ignore the nulls, unlike COUNT(*) so that has a purpose, >> >but >> >COUNT(0) means 'count the number of times zero is not null in the >> >selected >> >rows'. >> > >> >People also write things like EXISTS ( SELECT 1 FROM ...) where >'SELECT >> >*' >> >works fine because the optimizer knows what it is doing. >> > >> >It looks like you're either going to have to change the code >generator >> >or >> >the version of IDS, or perhaps use I-Spy to modify COUNT(0) into >> >COUNT(*) >> >on its way to the database. Of the options, I think changing the >code >> >generator would be easiest, but who knows. >> > >> >-- >> >Jonathan Leffler <jonathan.leffler@gmail.com> #include ><disclaimer.h> >> >Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org >> >"Blessed are we who can laugh at ourselves, for we shall never cease >to >> >be >> >amused." >> > >> >--bcaec55243b05bfecd04cb3c9283 >> > >> > >> >> > >>****************************************************************************** * > > >> > >> > Forum Note: Use "Reply" to post a response in the discussion forum. > >> >> -- >> Sent from my Android phone. >> >> >> > >******************************************************************************* > > >> Forum Note: Use "Reply" to post a response in the discussion forum. >> > > >******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. -- Sent from my Android phone.

I would contact Microstrategy. I hav found that most BI vendors have parameterized many of their standard queries to better support different RDBMS products. They may have a solution for you. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Oct 4, 2012 at 11:12 AM, Ivan Zavis <ivan.zavis@mi-system.co.rs>wrote: > On version 11.50.xC9 don't work too. "Generator" is The Microstrategy 9.3, > so > it would by hard to change the generator :) > > Jonathan Leffler <jonathan.leffler@gmail.com> wrote: > > >On Thu, Oct 4, 2012 at 6:40 AM, Ivan Zavis DBA > ><ivan.zavis@mi-system.co.rs>wrote: > > > >> Does anyone knows from which (minimum) version/patch of informix > >server, > >> statement:select count (0) from table_name works ? > >> > >> I need this function on IDS 11.50.xy but, as I see, this is only > >works > >> from version 11.70 ? > >> > > > >Interesting; I wasn't aware that it didn't work. > > > >I've just tried an IDS 11.50.FC8 and it is rejected as a syntax error. > >It > >likely means every version of IDS 11.50 rejects it. I don't happen to > >have > >an FC9 instance configured. > > > >I know that select count (*) returns same value as select count (0) but > > > >> I have some application which generate SQL select count(0) and I > >can't > >> change that SQL ... > >> > > > >The COUNT(0) notation is not very sensible code generation; COUNT(*) is > > > >every bit as efficient, if not more so. COUNT(column) has a specific > >meaning ignore the nulls, unlike COUNT(*) so that has a purpose, > >but > >COUNT(0) means 'count the number of times zero is not null in the > >selected > >rows'. > > > >People also write things like EXISTS ( SELECT 1 FROM ...) where 'SELECT > >*' > >works fine because the optimizer knows what it is doing. > > > >It looks like you're either going to have to change the code generator > >or > >the version of IDS, or perhaps use I-Spy to modify COUNT(0) into > >COUNT(*) > >on its way to the database. Of the options, I think changing the code > >generator would be easiest, but who knows. > > > >-- > >Jonathan Leffler <jonathan.leffler@gmail.com> #include <disclaimer.h> > >Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org > >"Blessed are we who can laugh at ourselves, for we shall never cease to > >be > >amused." > > > >--bcaec55243b05bfecd04cb3c9283 > > > > > > > >******************************************************************************* > > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- > Sent from my Android phone. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --14dae9340e45e6bd1c04cb3f581a

Ok, thanks Art. Art Kagel <art.kagel@gmail.com> wrote: >I would contact Microstrategy. I hav found that most BI vendors have >parameterized many of their standard queries to better support >different >RDBMS products. They may have a solution for you. > >Art > >Art S. Kagel >Advanced DataTools (www.advancedatatools.com) >Blog: http://informix-myview.blogspot.com/ > >Disclaimer: Please keep in mind that my own opinions are my own >opinions >and do not reflect on my employer, Advanced DataTools, the IIUG, nor >any >other organization with which I am associated either explicitly, >implicitly, or by inference. Neither do those opinions reflect those of > >other individuals affiliated with any entity with which I am affiliated >nor >those of the entities themselves. > >On Thu, Oct 4, 2012 at 11:12 AM, Ivan Zavis ><ivan.zavis@mi-system.co.rs>wrote: > >> On version 11.50.xC9 don't work too. "Generator" is The Microstrategy >9.3, >> so >> it would by hard to change the generator :) >> >> Jonathan Leffler <jonathan.leffler@gmail.com> wrote: >> >> >On Thu, Oct 4, 2012 at 6:40 AM, Ivan Zavis DBA >> ><ivan.zavis@mi-system.co.rs>wrote: >> > >> >> Does anyone knows from which (minimum) version/patch of informix >> >server, >> >> statement:select count (0) from table_name works ? >> >> >> >> I need this function on IDS 11.50.xy but, as I see, this is only >> >works >> >> from version 11.70 ? >> >> >> > >> >Interesting; I wasn't aware that it didn't work. >> > >> >I've just tried an IDS 11.50.FC8 and it is rejected as a syntax >error. >> >It >> >likely means every version of IDS 11.50 rejects it. I don't happen >to >> >have >> >an FC9 instance configured. >> > >> >I know that select count (*) returns same value as select count (0) >but >> > >> >> I have some application which generate SQL select count(0) and I >> >can't >> >> change that SQL ... >> >> >> > >> >The COUNT(0) notation is not very sensible code generation; COUNT(*) >is >> > >> >every bit as efficient, if not more so. COUNT(column) has a specific > >> >meaning ignore the nulls, unlike COUNT(*) so that has a purpose, >> >but >> >COUNT(0) means 'count the number of times zero is not null in the >> >selected >> >rows'. >> > >> >People also write things like EXISTS ( SELECT 1 FROM ...) where >'SELECT >> >*' >> >works fine because the optimizer knows what it is doing. >> > >> >It looks like you're either going to have to change the code >generator >> >or >> >the version of IDS, or perhaps use I-Spy to modify COUNT(0) into >> >COUNT(*) >> >on its way to the database. Of the options, I think changing the >code >> >generator would be easiest, but who knows. >> > >> >-- >> >Jonathan Leffler <jonathan.leffler@gmail.com> #include ><disclaimer.h> >> >Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org >> >"Blessed are we who can laugh at ourselves, for we shall never cease >to >> >be >> >amused." >> > >> >--bcaec55243b05bfecd04cb3c9283 >> > >> > >> >> >> >>****************************************************************************** * > >> > >> > Forum Note: Use "Reply" to post a response in the discussion forum. > >> >> -- >> Sent from my Android phone. >> >> >> >> >******************************************************************************* > >> Forum Note: Use "Reply" to post a response in the discussion forum. >> >> > >--14dae9340e45e6bd1c04cb3f581a > > >******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. -- Sent from my Android phone.