Information about amount of read records returned by SELECT, SELECT SINGLE, SELECT COUNT, etc.

The values of system variables after various versions of SELECT can be sometimes misleading.

Here the returned values after SELECT / END SELECT vs. SELECT SINGE:

SELECT SINGLE	sy-subrc	sy-dbcnt
exactly one record was found	0	1
no records were found	4	0
many records were found (N)	0	1

SELECT / END SELECT	sy-subrc	sy-dbcnt
exactly one record was found	0	1
no records were found	4	0
many records were found (N)	0	N

Conclusion: SELECT SINGLE can't be used to check if your select was ambiguous.

To check the amount of records you can use SELECT with COUNT and without INTO:

SELECT COUNT( * )
FROM usr02
WHERE bname LIKE 'S%'.

WRITE: / sy-dbcnt.

The returned values could be very misleading, when SELECT with aggregate function hasn't found any records:

DATA:
  lv_date TYPE d.

SELECT MAX( trdat )
  INTO lv_date
  FROM usr02
  WHERE bname = 'DUMMY'. " non-existing user

WRITE: / sy-subrc.  " 0 <- though nothing was found
WRITE: / sy-dbcnt.  " 1 <- though nothing was found

See other related notes in my infodepot:

Commit Work in SELECT statement Package processing of mass data with database commit and SELECT statement SQL inner join vs. join of internal tables Select-Options in dynamic WHERE condition called per RFC Package processing of mass data with COMMIT WORK and SELECT statement

Full list of examples in my infodepot

If you have a question, have found an error or just want to contact me, please use this form.

Copyright (C) 2010 http://www.kerum.pl/infodepot/

Disclaimer: I am not affiliated or related to any division or subsidiary of SAP AG.
Trademarks or registered trademarks of any products or companies referred to on this site belong to those companies.
Anyone using the given solutions, is doing it under his/her own responsibility and at own risk.