Combine/Add a Select Statement to Retrieve Rows in SalesLogix

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.

Submit a Comment

Your email address will not be published. Required fields are marked *

Subscribe To Our Newsletter

Join our mailing list to receive the latest Infor CRM (Saleslogix) and Creatio (bpm'online) news and product updates!

You have Successfully Subscribed!