Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
New Member
Posts: 4
Registered: ‎08-15-2012

Joins in Query Builder - SQL Error

I have added some custom entities to SalesLogix Web.  To allow the users to build Contact groups that include my entities I have created a global join from Contact into the "first layer" of my new entities (I have added more than one entity) and global joins between my entities.

 

When I buidl a Contact group that includes my first new entity, everything works OK.  When I include the next level I get no data returned and the event log on the server shows a "Failed to Parse SQL" error.  I have included the error details below.  For some reason it seems to have messed up the SQL in the second join by including a ">" and missing off the field name after "=A3.".

 

The same group works absolutely fine in the LAN client.  Has anyone seen this before and knows a work-around or fix?

 

2012-08-14 17:27:15,002 ERROR Sage.SalesLogix.Client.GroupBuilder.GroupInfo - GetGroupDataReader Failed
SqlSmiley FrustratedELECT TOP 50  A1.CONTACTID   FROM CONTACT A1 INNER JOIN ADDRESS A2 ON (A1.ADDRESSID=A2.ADDRESSID) LEFT OUTER JOIN EMADDRESSBOOKMEMBER A3 ON (A1.CONTACTID>SLXCONTACTID.EMADDRESSBOOKMEMBER!EMADDRESSBOOKID=A3.) LEFT OUTER JOIN EMADDRESSBOOK A4 ON (A3.EMADDRESSBOOKID=A4.EMADDRESSBOOKID) LEFT OUTER JOIN EMEMAILACCOUNT A5 ON (A4.EMEMAILACCOUNTID=A5.EMEMAILACCOUNTID) WITH FIRSTROW 0 ORDER BY A1.LASTNAME ASC, A1.FIRSTNAME ASC,  A1.CONTACTID  ASC
GroupId: pDEMOA0000R2
Group Name: DDDDDDD
Current User: ADMIN
Error Message: Failed to parse SQL.
Stack Trace:    at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
   at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
   at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.OleDb.OleDbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)
   at Sage.SalesLogix.Client.GroupBuilder.GroupInfo.GetGroupDataReader(Boolean DoPaging, Int32 FirstRow, Int32 PageSize, String SortField, Boolean JustIDs, Boolean invertOrder)

New Member
Posts: 4
Registered: ‎08-15-2012

Re: Joins in Query Builder - SQL Error

Update:

Interestingly, if you create the group through the LAN client, it works OK in both LAN and Web.  If you create it in the Web Client, it works in LAN but is broken in Web.

 

It looks as if there is a problem somewhere in the web client code that creates the group.  Unfortunately we can't really expect our users to run the LAN client when they are web users.

Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: Joins in Query Builder - SQL Error

What Version/build SLX?:

  7.5.4.xxx?

--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html
Highlighted
New Member
Posts: 4
Registered: ‎08-15-2012

Re: Joins in Query Builder - SQL Error

It's 7.5.3.4227 with web hot fixes up to HF8 installed