DavidThi808 wrote:In Oracle, you can't use AS before a table alias.
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.
SELECT COUNT (*) FROM employees;
The exception is: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.
Message=ORA-12571: TNS:packet writer failure
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)
You can fill in the in-line view with any valid query.
SELECT COUNT (*) AS numrows FROM ( -- Begin in-line view SELECT EMPLOYEES.LAST_NAME FROM EMPLOYEES ) -- End in-line view
You don't have to know how many rows there are ahead of time.
SELECT last_name FROM employees WHERE ROWNUM <= 250 ;
DavidThi808 wrote: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?
Solution #1 is exactly what I needed. Any idea why Oracle does this differently from everyone else?
DavidThi808 wrote: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?
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