Caché iSql query vs. CSP/COS: The latter is missing the most recent cms.ID.

query1.txt

SELECT DISTINCT cms.ID 
FROM dt_cms_schema.CMSStory cms, dbo.AdDbPageInfo adbpi, dbo.StoryPageElements spe, dbo.Story s, dbo.SubCategory sc, dbo.status st, dbo.Desk d 
WHERE (adbpi.runDate BETWEEN '2011-04-27' AND '2011-08-05') 
AND cms.story->storyId = s.storyId 
AND sc.subCategoryId = 191173
AND s.storyId = spe.storyId 
AND spe.logicalPagesId = adbpi.logicalPageId 
AND s.subCategoryId = sc.subCategoryId 
AND s.StatusId = st.StatusId 
AND s.deskId = d.deskId 
AND s.words > 10 
AND d.deskName <> 'trash' 
AND st.statusName IN ('ready', 'output', 'web') 
AND NOT (s.subCategoryId in (0, 2, 51, 52, 54, 101, 200, 300, 1000, 1023, 176447, 330788, 1905902)) 
AND NOT (s.storyname LIKE '%ol.wed%' OR s.storyname LIKE '%ol.gold%') 
AND NOT (s.origin LIKE '%Wire%' OR s.origin LIKE '%NYT') 
ORDER BY adbpi.runDate DESC, adbpi.letter ASC, adbpi.pagenum ASC, s.words DESC

test.csp

<script language="cache" method="catsIndexTest" arguments='cats:%String="", days:%Integer=0, max:%Integer=100' returntype="%ListOfObjects" procedureblock="1">
	
	//----------------------------------
	// Initialize:
	//----------------------------------
	
	set (cmsStory, sql, now, then) = ""
	set listObj = ##class(%ListOfObjects).%New()
	set rs = ##class(%ResultSet).%New()
	
	//----------------------------------
	// Setup:
	//----------------------------------
	
	//set:((max < 1) || (max > 100)) max = 30
	set:((days < 1) || (days > max)) days = max
	
	// Create list from area argument and trim each value:
	set catList = ""
	for i=1:1:$length(cats, ",") {
		set catList = catList _ $listbuild($zstrip($piece(cats, ",", i), "<>W"))
	}
	
	// More info: http://snipurl.com/retpj
	set now = $zdate(+$h, 3) // The + will truncate the time part & $zd(,3) converts to odbc format.
	set then = $piece($SYSTEM.SQL.DATEADD("d", -days, now), " ") // Subtract days from date.
	
	//----------------------------------
	// "NOT" reference:
	//----------------------------------
	
	/*
	** 
	** 0       NULL
	** 1       A-Section
	** 2       Advice and Amusement
	** 51      Wire International
	** 52      Wire National
	** 54      Wire Washington
	** 101     Television
	** 200     Agate Business
	** 300     Commentary
	** 1000    Agate
	** 1023    Wire Sports
	** 176447  Wire Columnist
	** 330788  AM Auto QA
	** 1905902 Lottery
	** 
	*/
	
	//----------------------------------
	// Query:
	//----------------------------------
	
	set sql = 
		"SELECT DISTINCT cms.ID " _ 
		"FROM dt_cms_schema.CMSStory cms, dbo.AdDbPageInfo adbpi, dbo.SubCategory sc, dbo.Story s, dbo.StoryPageElements spe, dbo.status st, dbo.Desk d " _ 
		"WHERE (adbpi.runDate BETWEEN ? AND ?) " _ 
		"AND cms.story->storyId = s.storyId " _ 
		"AND sc.subCategoryId %INLIST ? " _ 
		"AND s.storyId = spe.storyId " _ 
		"AND spe.logicalPagesId = adbpi.logicalPageId " _ 
		"AND s.subCategoryId = sc.subCategoryId " _ 
		"AND s.StatusId = st.StatusId " _ 
		"AND s.deskId = d.deskId " _ 
		"AND s.words > 10 " _ 
		"AND d.deskName <> 'trash' " _ 
		"AND st.statusName IN ('ready', 'output', 'web') " _ 
		"AND NOT (s.subCategoryId in (0, 2, 51, 52, 54, 101, 200, 300, 1000, 1023, 176447, 330788, 1905902)) " _ 
		"AND NOT (s.storyname LIKE '%ol.wed%' OR s.storyname LIKE '%ol.gold%') " _ 
		"AND NOT (s.origin LIKE '%Wire%' OR s.origin LIKE '%NYT') " _ 
		"ORDER BY adbpi.runDate DESC, adbpi.letter ASC, adbpi.pagenum ASC, s.words DESC"
	
	//----------------------------------
	// Get results:
	//----------------------------------
	
	set sc = rs.Prepare(sql)
	
	;w catList _ "<br>"
		
	do rs.Execute(then, now, catList)
	
	;set rsmeta = rs.%GetMetaData()
	;do rsmeta.print()
	
	;w """" _ rs.GetData(1) _ """" _ "<br>"
	
	while (rs.Next()) {
		
		;w """" _ rs.GetData(1) _ """" _ "<br>"
		set cmsStory = ##class(dt.cms.schema.CMSStory).%OpenId(rs.GetData(1)) // CMS Story Object.
		;w cmsStory.%Id() _ "<br>"
		do:($isobject(cmsStory)) listObj.Insert(cmsStory) // Add cmsStoryObject to the beginning of the object list.
		
	}
	
	do rs.Close()
	
	//----------------------------------
	// Return the results:
	//----------------------------------
	
	quit listObj
	
</script>

<hr>

#[ set stories = ..catsIndexTest(191173, 100) ]#

<ol>
	<csp:loop counter="x" from="1" to="#(stories.Count())#">
		
		<li>#(stories.GetAt(x).%Id())#</li>
		
	</csp:loop>
</ol>

Fix and discussion here:
https://groups.google.com/d/topic/intersystems-public-cache/wxKodYp76AQ/discussion