Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Copper Contributor
Posts: 33
Registered: ‎08-19-2009

Group SQL Error

I am currently investigating an issue on an 8.1 install in which a SQL parse error is being thrown when executing the SQL returned from the Application.BasicFunctions.GetGroupSQL function. The returned SQL was the following:

 

SELECT A1.CONTACTID, A1.NAMELF, A1.ACCOUNT, A2.CITY A2_CITY, A2.STATE A2_STATE, A1.WORKPHONE, A1.MOBILE, A1.EMAIL, A1.ACCOUNTMANAGERID, A1.SECCODEID FROM CONTACT A1 INNER JOIN ADDRESS A2 ON (A1.ADDRESSID=A2.ADDRESSID) WHERE (A1.ACCOUNT LIKE :ValueParam0) ORDER BY A1.NAMELF ASC

 

It would appear that the issue is caused by the function returning :ValueParam0 rather than the actual parameter value. Viewing the group's SQL from the Query Builder shows the same result, with :ValueParam0 being shown rather than the value. I am able to recreate this easily, for example by creating a brand new contact group that filters based on account name (which produced the SQL above).

 

I am not sure whether this is a bug or simply the behaviour of groups however is there a way to get GetGroupSQL to return the correct SQL with a value rather than the parameter placeholder?

 

Thanks

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

Re: Group SQL Error

I saw this happen out of the blue on a customer system the other day on an 8.0.x system.

 

The ONLY work-around I could do was to:

   A - Locate the ofending condition (we had about 6 or 7 conditions in an "OR") 

   B - Check the "literal" box

   C - Put in the following value:   "'%whatever%'" 

                double quote, single quote, precent, theWhatEverStringIwasLookingFor, percent, single quote, doublequote

 

I believe there's been a new bug introduced to thesystem at 8.0. This NEVER happened before and it's a group the customer has been using since 4.x days.

 

FYI - Attempting to destroy/re-create the group made no difference.. 

--
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
Bronze Super Contributor
Posts: 113
Registered: ‎07-27-2009

Re: Group SQL Error

Hi RJ,

 

i can confirm that this bug is since 8.0. We are on 8.0 and have a lot of problems with group builder. As far as i know, this is a known bug.

 

Another workaround is to change the operator from "contains" to "in". Click OK. Change Operator back from "in" to "contains". Then you can see that "ValueParam" is changed to the string you entered.

 

Hope that there will be a fix. It's the hell for us.

Regards
Rainer Raebiger
Copper Contributor
Posts: 33
Registered: ‎08-19-2009

Re: Group SQL Error

Another workaround that appears to work is to to check the Literal box, save the condition, and then go back in and uncheck it. This seems to force it to put the value in.

 

Thanks for all your help. 

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

Re: Group SQL Error

Agree.. it is a major pain for our customers.. Specifically.. the customer is asking:

 

   "WHY id this broken? It was working fine until you upgraded us!"

 

I can only say one thing.. Saleslogix broke it.

--
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
Copper Contributor
Posts: 33
Registered: ‎08-19-2009

Re: Group SQL Error

I have had that conversation more than once this week!

Employee
Posts: 629
Registered: ‎04-24-2009

Re: Group SQL Error

[ Edited ]

The following related defects were addressed in 8.1 Update 02:

 

  • 14094813: LAN - Deprecate Application.BasicFunctions.GetGroupSQL() and replace with Application.BasicFunctions.GetGroupSQLEx() that includes group parameter information.
  • 14094821: LAN - Modify Opportunity - SLX OnOpen Opportunity Statistics Report to handle scenarios where a group includes parameters.
  • 14094816: Deprecate Application.BasicFunctions.GetGroupSQLInfo() and replace with Application.BasicFunctions.GetGroupSQLInfoEx() that includes group parameter information.
  • 14094815: Update System to use Application.BasicFunctions.GetGroupSQLEx().

 

Thanks

 

Mike

Silver Super Contributor
Posts: 801
Registered: ‎03-24-2009

Re: Group SQL Error

Unfortunately, we found another today. If you create a contact group - base the condition on ModifyDate > 17/07/2014 (or any date that does not "fit" a UK/US date e.g. 12/12/12 would but 17/12/12 would not) then you get an error when you use this group in Campaign Manage Targets. Settings need to be UK of course.

 

It doesn't ISO the date - and gives you the attached error.

This happens with *any* date column.

 

This is w/ v8.1 SNC02 applied.

Employee
Posts: 629
Registered: ‎04-24-2009

Re: Group SQL Error

Thanks Mike. If you create a ticket please me know the ticket number.

 

Thanks

 

Mike

Silver Super Contributor
Posts: 801
Registered: ‎03-24-2009

Re: Group SQL Error

Yes, already reported to UK - they are testing/verifying at the moment. But, very easily reproduced