This discussion is archived
1 2 Previous Next 19 Replies Latest reply: Apr 16, 2011 11:22 AM by DavidThi808 RSS

select count(*) from (SELECT ... problem

DavidThi808 Newbie
Currently Being Moderated
Hi all;

I am trying to use the following select:
"select count(*) from (SELECT EMPLOYEES.LAST_NAME FROM EMPLOYEES) as numrows"

And am getting an error (calling via ADO.NET connector). All other queries are working fine.

The exception is:
System.Data.OracleClient.OracleException occurred
Message=ORA-12571: TNS:packet writer failure

Source=System.Data.OracleClient
ErrorCode=-2146232008
Code=12571
StackTrace:
at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
at WindwardReportsDrivers.net.windward.datasource.ado.AdoDataSource.ExecuteQuery(DataSourceNode[] stack, String select, ArrayList parameters, String origSelect, CommandBehavior cmdBehv)
InnerException:

Any ideas?

thanks - dave
  • 1. Re: select count(*) from (SELECT ... problem
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Dave,
    DavidThi808 wrote:
    Hi all;

    I am trying to use the following select:
    "select count(*) from (SELECT EMPLOYEES.LAST_NAME FROM EMPLOYEES) as numrows"
    And am getting an error (calling via ADO.NET connector). All other queries are working fine.
    In Oracle, you can't use AS before a table alias.
    Actually, you don't need a table alias, and you don't even need a sub-query. You could simply say
    SELECT  COUNT (*)
    FROM    employees;
    The exception is:
    System.Data.OracleClient.OracleException occurred
    Message=ORA-12571: TNS:packet writer failure

    Source=System.Data.OracleClient
    ErrorCode=-2146232008
    Code=12571
    StackTrace:
    at System.Data.OracleClient.OracleConnection.CheckError(OciErrorHandle errorHandle, Int32 rc)
    at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, Boolean needRowid, OciRowidDescriptor& rowidDescriptor, ArrayList& resultParameterOrdinals)
    at System.Data.OracleClient.OracleCommand.Execute(OciStatementHandle statementHandle, CommandBehavior behavior, ArrayList& resultParameterOrdinals)
    at System.Data.OracleClient.OracleCommand.ExecuteReader(CommandBehavior behavior)
    at System.Data.OracleClient.OracleCommand.ExecuteDbDataReader(CommandBehavior behavior)
    at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
    at WindwardReportsDrivers.net.windward.datasource.ado.AdoDataSource.ExecuteQuery(DataSourceNode[] stack, String select, ArrayList parameters, String origSelect, CommandBehavior cmdBehv)
    InnerException:
    That sounds like a problem connecting to the database. It doesn't sound like anything that would be caused by the code you posted earler.
    Can you run anything in the database, no matter how simple?
  • 2. Re: select count(*) from (SELECT ... problem
    Ganesh Srivatsav Guru
    Currently Being Moderated
    Hi,

    'AS' key word in select statements are used for column aliasing. It cannot be used to assign alias for inline view ;

    your query can be changed and simplified like this,
    SELECT COUNT (*) AS numrows FROM employees;
    G.
  • 3. Re: select count(*) from (SELECT ... problem
    DavidThi808 Newbie
    Currently Being Moderated
    Hi Ganesh;

    Unfortunately our situation is we get a random select that can be very very complex. I used "SELECT EMPLOYEES.LAST_NAME FROM EMPLOYEES" as an example.

    From that select we need to get a count of rows returned before running it (so that if it's more than 250, we limit it - this all displays in a Windows app). So what we are doing (works with all other vendors) is strip off the "order by..." and then put the rest in:
    select count(*) from ({0}) as numrows

    to get the count.

    So, is there a way to do the equivilent for Oracle where we can get a count against any select?

    thanks - dave
  • 4. Re: select count(*) from (SELECT ... problem
    DavidThi808 Newbie
    Currently Being Moderated
    Hi Frank;

    First off thank you for the help. And please see my reply to Ganesh on the first item.

    On the second, that is the exception I get for this select. I'm connected fine and all other selects work fine, but this select throws that exception.

    thanks - dave
  • 5. Re: select count(*) from (SELECT ... problem
    Frank Kulash Guru
    Currently Being Moderated
    Hi, Dave,

    Okay, but are you trying to give an alias to the column, or to the in-line view?

    If you want to assign an alias to the column, then the alias goes immediately after the expression in the SELECT clause, either with or without the keyword AS:
    SELECT  COUNT (*)    AS numrows
    FROM    (       -- Begin in-line view
                SELECT  EMPLOYEES.LAST_NAME 
                FROM    EMPLOYEES
            )       -- End in-line view
    You can fill in the in-line view with any valid query.

    If you just want to limit the output to, say, 250 rows, then you can use ROWNUM or ROW_NUMBER in the query itself:
    SELECT  last_name
    FROM    employees
    WHERE   ROWNUM  <= 250
    ;
    You don't have to know how many rows there are ahead of time.
  • 6. Re: select count(*) from (SELECT ... problem
    DavidThi808 Newbie
    Currently Being Moderated
    Frank - THANK YOU!

    Solution #1 is exactly what I needed. Any idea why Oracle does this differently from everyone else?

    And your solution #2 is what we do use in some cases where the number is too high - but we get the count first to determine if we can present all choices in a list box or if we need to ask them to type it in.

    thanks - dave
  • 7. Re: select count(*) from (SELECT ... problem
    6363 Guru
    Currently Being Moderated
    DavidThi808 wrote:

    Solution #1 is exactly what I needed. Any idea why Oracle does this differently from everyone else?
    It depends on, who everybody else is, how they are doing it, and whether they were doing it before Oracle was doing it.
  • 8. Re: select count(*) from (SELECT ... problem
    BillyVerreynne Oracle ACE
    Currently Being Moderated
    DavidThi808 wrote:

    Solution #1 is exactly what I needed. Any idea why Oracle does this differently from everyone else?
    Because it is truly idiotic to increase the I/O in the database when I/O has the biggest impact on performance. Running the SELECT to get the rows needs I/O. Running a SELECT now on that to count the rows, is more I/O on the very same data. What sensible about firing off additional and irrelevant I/O at the database?

    Because it is also truly idiotic to make the 1st SELECT dependent on the 2nd SELECT when the isolation level does not support it. The 1st SELECT selects a 1000 rows from the table. This select is a consistent read and will output a 1000 rows. Another session deletes all 900 rows from that table and commit. Your 2nd SELECT deals with a new and different version of the data than your 1st - and it returns a 100 row count.

    Despite the fact that your 1st SELECT has a 1000 rows and will output a +1000+ rows, you now erroneously think there are only a 100 rows. It is a major flaw treating the database as if it is a single process system and that the same read consistency will apply for every single DML your process fires off at the database.

    So why is Oracle different? Well, it is not that Oracle RDBMS is that different in this regard. It equally can be used in this idiotic fashion. Fortunately, people using Oracle are often more database savvy and can distinguish idiotic approaches from non-idiotic approaches.
  • 9. Re: select count(*) from (SELECT ... problem
    DavidThi808 Newbie
    Currently Being Moderated
    Hi Billy;

    Please don't hold back - tell us how you truly feel :)

    I do have a serious question though. I assumed that databases now intelligently look at a select and determine the optimal way to run them. So for example calling "select count (*) from table" is identical to "select count (ID) from table" - in both cases it does not pull all contents from all rows, it just gets a row count.

    Am I incorrect in that assumption?

    thanks - dave
  • 10. Re: select count(*) from (SELECT ... problem
    Centinul Guru
    Currently Being Moderated
    What Billy is saying (please correct me if I'm wrong :) ) is that it's pointless to do a count to get the total number of rows.

    Instead of counting everything to determine if you need to split the result set up (which is subject to change on every single call as Billy mentioned), just code the "pagination" in to begin with and if the next page has no rows so be it.

    You'll script all that repetitive useless I/O counting the total number of rows.

    This may be helpful:

    Ask Tom: On Top-n and Pagination Queries - Oracle

    Edited by: Centinul on Apr 13, 2011 1:36 PM
  • 11. Re: select count(*) from (SELECT ... problem
    DavidThi808 Newbie
    Currently Being Moderated
    Hi Centinul;

    Oh, that makes sense. But that is not what we are doing. What we use the count for is we get the count. If it is under 50 rows, we present the user with a drop-down list to select the row they want. If it is more than 50 we present them with an edit box to enter the value for the row they want.

    thanks - dave
  • 12. Re: select count(*) from (SELECT ... problem
    John Spencer Oracle ACE
    Currently Being Moderated
    DavidThi808 wrote:
    Hi Centinul;

    Oh, that makes sense. But that is not what we are doing. What we use the count for is we get the count. If it is under 50 rows, we present the user with a drop-down list to select the row they want. If it is more than 50 we present them with an edit box to enter the value for the row they want.

    thanks - dave
    If you expect them to be able to enter "the value for the row they want" if there are more than 50 rows, why not just ask them for that value in the first place?

    John
  • 13. Re: select count(*) from (SELECT ... problem
    DavidThi808 Newbie
    Currently Being Moderated
    Hi John;

    We've found users prefer to select from a list. So when there are few enough values, we present the choice that way.

    And generally people either have most/all under 50 rows or most/all over. So for the people with most/all under, everything is a list which means they always have that simplier interface.

    thanks - dave
  • 14. Re: select count(*) from (SELECT ... problem
    William Robertson Oracle ACE
    Currently Being Moderated
    DavidThi808 wrote:
    Any idea why Oracle does this differently from everyone else?
    Is it the AS syntax that is different? Just wondering what you meant.
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points