Test if a dynamic query returns 0 results

Question

Tom Bruce · May 30, 2022

Hi, what’s the best way to check if your query return 0 records?

If $$$ISOK(sc) 	
    		{
   	   		 	Set tResult = sqlStatement.%Execute()
   	    
    			WHILE tResult.%Next()
    			{
					set tRecords = tResult.%ROWCOUNT
   		        }

				if (tRecords = "")
				{
					write "no records" 
				}
			}

Product version: IRIS 2021.2

Discussion (1)0

David Hockenbroch · May 31, 2022

Your tResult will have a property called %SQLCODE that gets set when the query is run. If %SQLCODE = 100, that means that the query ran successfully but returned no results or that you've reached the end of the result set. If %SQLCODE = 0, you have some results. If %SQLCODE is less than zero, that's an error code.

if tResult.%SQLCODE = 100{

//whatever you want to do for no results here

}

2 0