Today I discovered the difference between select count (1) and select count (column name)

gatuzo

select count (1) into: str2 from "tongji"
where "xfdate" =: tjrqd and "cardnum" =: str1;

select count (xftime) into: str2 from "tongji"
where "xfdate" =: tjrqd and "cardnum" =: str1;

If no data is retrieved, the return value of sqlcode is different.

The return value of count (1) is empty and sqlcode is 100
The return value of count (column name) is 0 and sqlcode is 0

Which one to talk about?

kori1984

count () in the sql statement is the total number of statistics records, () can be a field name or *

If you use 1, you don't have this field in your table, so it returns null

When the sqlcode value is 100, the executed SQL statement did not return any results

Ragina

Different databases have different effects, right?
I use Sybase ASE and Sybase ASA
Whether count (*) or count (1)
Return value when no data is retrieved: 0

gatuzo

I use ASA 8.0

gatuzo

Strange, I tried to interview in isql, and I also saw 0. Is my code wrong?

gatuzo

Found the reason, select count (), the column name needs group by, then the return code 100 will appear when the record is not found, count (1) count (column name)