What is wrong with the following Saleslogix SQL script?

Question:

I have the following script in SalesLogix :

strSQL2 = “SELECT s.SALES_STAGE AS STAGE_PROSPECT, SUM(CASE WHEN r.USERFIELD10 IS NULL THEN 0 ELSE 1 END) AS SCOUNT_PROSPECT”
strSQL2 = strSQL2 & ” FROM sysdba.C_ACCOUNT_LEAD_SOURCE t RIGHT OUTER JOIN sysdba.ACCOUNT r ON t.ACCOUNTID = r.ACCOUNTID RIGHT OUTER JOIN sysdba.C_PM_SALES_STAGES s ON ISNULL(r.USERFIELD10, 0) = s.SALES_STAGE AND ISNULL(r.USERFIELD3, 0) = s.ZONE_NUMBER AND r.SUBTYPE = ‘Vendor'”
strSQL2 = strSQL2 & ” WHERE (s.SALES_STAGE = ‘Prospect’) and (t.ACCOUNTID IS NULL)”
strSQL2 = strSQL2 & ” GROUP BY s.SALES_STAGE, t.ACCOUNTID”

 

When I’m signed in as ADMIN, it does not give me an error. But when I sign in as any other user I get a Failed to Parse SQL

What is wrong with this script that works with the ADMIN but nobody else?

Answer:

You have to keep in mind that when anyone other than ADMIN is running an SQL Statement, SLX will parse it and add the Security Joins.
I would suggest you rewrite the statement. Instead of Using Right Joins to get back ot the Account, why don’t you base the query on the Account table instead.

You can simplify your query and then work in tandem with the SLX Profiler so that you can capture how SLX is parsing your queries. It may help you build the query in a way that is more friendly with the Parser.

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!