Question: I have created the following SQL statement in SalesLogix:
string strSQL = “”;
strSQL = “SELECT A3.CONTACTID, A3.NAMELF, A3.ACCOUNT, A3.WORKPHONE, A3.EMAIL, A2.CONTACTID, A2.MODIFYDATE, A2.SPEHISTORYID, A2.SPEASSIGNEDUSERNAME, A2.SPESUBJECT, A2.SPETEMPLATE, A2.SPEQUERYTAG, A2.SPESTATUS, A2.SPESCORE, A2.SPECLICKS, A2.SPEACTION, A2.SPEOPENS, A4.ADDRESS1, A4.CITY, A4.STATE, A4.POSTALCODE “;
strSQL += “FROM SPEHISTORY A2 “;
strSQL += ” INNER JOIN CONTACT A3 ON (A2.CONTACTID=A3.CONTACTID) “;
strSQL += “INNER JOIN ADDRESS A4 ON (A3.ADDRESSID=A4.ADDRESSID) “;
if (SpeEmailTagsLB.SelectedIndex > 0)
strSQL += “AND (A2.SPEQUERYTAG = ‘” + SpeEmailTagsLB.SelectedItem.Text + “‘) “;
for (int s = 0; s < SpeEmailStatusFilterLB.Items.Count; s++)
{
if ((s > 0) && (SpeEmailStatusFilterLB.Items[s].Selected))
strSQL += “AND (A2.SPESTATUS = ‘” + SpeEmailStatusFilterLB.SelectedValue + “‘) “;
}
// filter senders
for (int s = 0; s < SpeUsersLB.Items.Count; s++)
{
if ((s > 0) && (SpeUsersLB.Items[s].Selected))
strSQL += “AND (A2.SPESENDER = ‘” + SpeUsersLB.SelectedValue + “‘) “;
}
if (SpeScoreFiltersLB.SelectedIndex > 0)
strSQL += “AND (A2.SPESCORE ” + SpeScoreFiltersLB.SelectedValue + “) “;
After creating the SQL statement I created a command and execute query. I would like to populate only 15 rows per query, and I set my page size to 15, but I keep getting a set of 21. I took a look at the use of row_number()’ but I can’t figure out how to get a working SQL statement with row_number(). “TOP” is only working for the first page. Can you give me some advice for how to combine/add a SELECT statement to retrieve X rows that qualify the filters I created?
Answer: It seems like you are trying to handle custom paging. In the SLX provider the syntax for paging is:
SELECT TOP 15 * FROM CONTACT WITH FIRSTROW 16
SLX Profiler is visable when SLX pulls a group.
