How to give Count up to a limited value, "countar up to 90 on a table that has 100"

Asked

Viewed 123 times

-1

I assembled the following Query - "INFORMIX"

SELECT COUNT(1) FROM (SELECT * FROM NOTA_FISCAL_NFE LIMIT 90);

Which returns me the following error:

An error occurred when executing the SQL command: SELECT COUNT(1) FROM (SELECT * FROM NOTA_FISCAL_NFE LIMIT 90);

A syntax error has occurred. [SQL State=42000, DB Errorcode=-201] 1 statement failed.

Execution time: 0.02s

I want to perform a Count in a table up to a specific value, even if you have more records, I just want to know if you have that specific quantity, in the example "90".

sql query count informix
  • 1

    SELECT COUNT(*) FROM NOTA_FISCAL_NFE LIMIT 90;

  • The code you posted in the question does not match the code executed in the DBMS according to the error message: SELECT TABLE_TESTE.COUNT(1) FROM (SELECT * FROM NOTA_FISCAL_NFE LIMIT 90) AS TABLE_TESTE. In the case of the code in the error message, you informed TABELA_TESTE.COUNT(1), which is really wrong. If you write as you asked it should work, although the subquery is unnecessary.

  • So, worst that doesn’t work, but I updated the error now.

  • In terms of logic, your SQL doesn’t make much sense. It makes sense only because of a technical/performance limitation. But Informix has no performance problems when running count(*) , as with Oracle or Sql Server. All Count(*) are returned instantly because Informix maintains an online control of the number of rows in all tables. Can make Count simple without fear it will be fast: select count(*) from nota_fiscal_nfe;

1 answer

-1

It would be good for you to specify the database you are working on. Usually you need to specify an alias when doing a subquery. Another thing, replace '1' with a '*' or a column name.

select count(*) 
from (
    select * from NOTA_FISCAL_NFE limit 90
) as tabela_subquery;
  • 1

    That one sub-query is not necessary.

  • It didn’t work, man.

  • @Robertodecampos Without this sub-query, the result may be higher than 90. From what I understand, this cannot occur.

  • 1

    @Francopan the result will be the same with or without the sub-query.

  • 1

    Important: Informix supports the syntax of limit Mysql igua only in newer versions. But it does not support the use of it within Queries, when specified at the end of SQL, but if specified at the beginning, it works : select limit 90 ...

  • Thank you, it worked perfectly.

Show 1 more comment

Browser other questions tagged sql query count informix

You are not signed in. Login or sign up in order to post.