8/24/2025 5:39:36 PM
|
| |
| slxdeveloper.com Community Forums |
|
| |
|
|
The Forums on slxdeveloper.com are now retired. The forum archive will remain available for the time being. Thank you for your participation on slxdeveloper.com!
Forum to discuss writing script in Architect plugins for SalesLogix & general SalesLogix customization topics (for Windows client only). View the code of conduct for posting guidelines.
|
|
|
|
Incorrect syntax near ')'. : Incorrect syntax near the keyword 'SELECT'.
Posted: 28 Jul 08 11:53 AM
|
Hello,
I have a user with an account group that returns accounts that have sceduled activities for the next 1000 days. This group is the default account group that opens on startup of SLX. Sometimes, when the group is trying to load upon startup, he gets this error: "Incorrect syntax near ')'. : Incorrect syntax near the keyword 'SELECT'.". I was testing something logged into as him today and received the error and captured the offending SQL statement. I've also received similar reports for other users using the "My Activities For Today" contact group, I suspect they are related, but I'm not 100% certain.
The problematic statement is below. The offending part is in the "Executed SQL" section in all upper case. It's like the provider is just injecting this code, but at the wrong place and time. When the group works properly, the offending SQL is completely omitted from the Executed SQL
SLX Version: 7.2.2, Upgraded recently from 6.2.6
---------- Client SQL ---------
SELECT DISTINCT A1.CONTACTID, A1.NAMELF, A1.ACCOUNT, A2.CITY A2_CITY, A2.STATE A2_STATE, A1.WORKPHONE, A1.MOBILE, A1.EMAIL, A1.NAME, A1.LASTNAME_UC, A1.FIRSTNAME FROM CONTACT A1 INNER JOIN ADDRESS A2 ON (A1.ADDRESSID=A2.ADDRESSID) LEFT OUTER JOIN ACTIVITY A3 ON (A1.CONTACTID=A3.CONTACTID) WHERE (A3.STARTDATE>=[DBTYPE_VARIANT,"7/28/2008 4:00:00 AM"] AND A3.STARTDATE<[DBTYPE_VARIANT,"7/30/2008 4:00:00 AM"]) AND (A3.TYPE=[DBTYPE_STR | DBTYPE_BYREF,"262146"]) AND (A3.USERID=[DBTYPE_STR | DBTYPE_BYREF,"U6UJ9A00000A"]) ORDER BY A1.LASTNAME_UC ASC, A1.FIRSTNAME ASC
---------- Executed SQL ----------
select distinct a1.contactid, (isnull(a1.lastname,'') + ', ' + isnull(a1.firstname,'')) namelf2, a1.account, a2.city a2_city, a2.state a2_state, a1.workphone, a1.mobile, a1.email, (isnull(a1.firstname,'') + ' ' + isnull(a1.lastname,'')) name9, a1.lastname_uc, a1.firstname from contact a1 inner join secrights s_aa on (s_aa.accessid = 'u6uj9a00000a' and a1.seccodeid = s_aa.seccodeid ) inner join address a2 on (a1.addressid=a2.addressid) left outer join activity(SELECT * FROM ACTIVITY WHERE (ACTIVITY.ACTIVITYID IN (SELECT uax.ACTIVITYID FROM USER_ACTIVITY uax WHERE uax.USERID = 'U6UJ9A00000A')) OR (ACTIVITY.ACCOUNTID IN (SELECT acx.ACCOUNTID FROM ACCOUNT acx INNER JOIN SECRIGHTS sr ON (sr.SECCODEID = acx.SECCODEID AND sr.ACCESSID = 'U6UJ9A00000A'))) OR (ACTIVITY.USERID IN (SELECT ucx.CALUSERID FROM USERCALENDAR ucx WHERE ucx.USERID = 'U6UJ9A00000A'))) A3 ON (A1.CONTACTID=A3.CONTACTID) AND A3.ACCESSID_='U6UJ9A00000A' where (a3.startdate>=? and a3.startdate
Has anyone else experience a similar issue? I'm wondering if it's a wide scale issue of if it's something related to our installation.
Thank you.
...Rob
|
|
|
|
Re: Incorrect syntax near ')'. : Incorrect syntax near the keyword 'SELECT'.
Posted: 31 Jul 08 1:29 PM
|
We're not having that issue but the executed sql is broken at
...
left outer join activity (
...
The activity should not be there. The provider is not parsing the sql in the client sql correctly.
You may be able to rewrite your group list to avoid this issue. It looks to me like your asking to see only accounts for contacts with an activity in the next 1000 days. Try a left join on activities instead of an outer join and see if it fixes the executed sql.
|
|
|
|
Re: Incorrect syntax near ')'. : Incorrect syntax near the keyword 'SELECT'.
Posted: 31 Jul 08 1:43 PM
|
Thanks for the reply Jim.
I don't see any place in Saleslogix to distinguish between a "Left Join" and a "Left Outer Join". I just see the ability to specify "Inner","Left" and "Right" joins. The linkage between the Account and Activity tables is set to "Left", which is being translated to "Left Outer Join" by Saleslogix.
I agree that the uppercase part of the executed query should not be there. Something whacky is going on and it is happening randomly for a couple users.
I need to find out where the injected part of the statement is being executed from, maybe that will shed some light on the problem.
Thanks again for your reply. |
|
|
| |
|
You can
subscribe to receive a daily forum digest in your
user profile. View the site code
of conduct for posting guidelines.
 Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!
|
|
|
|
|
|
|
|