
Question:
When I try to execute the following sql in script i get the error failed to parse sql.
However, if i run this sql statment in the server management studio it runs fine. Any ideas why SLX doesnt like it? Im guesiing its to do with the use of the
[sysdba].[udf_GetRecursiveChildren] function?
strSQL = “SELECT Z_ACCOUNT_NL.Z_REVYEAR as Year, Z_ACCOUNT_NL.Z_REVTYPE as Product, sum(Z_ACCOUNT_NL.Z_REVAMNT) as Spend ” & _
“FROM ACCOUNT INNER JOIN Z_ACCOUNT_NL ON ACCOUNT.ACCOUNTID = Z_ACCOUNT_NL.ACCOUNTID ” & _
“WHERE ACCOUNT.ACCOUNTID = ‘” & AccID & “‘ or ACCOUNT.ACCOUNTID ” & _
“IN (select childid from [sysdba].[udf_GetRecursiveChildren](‘” & AccID & “‘)) ” & _
“GROUP BY Z_ACCOUNT_NL.Z_REVYEAR, Z_ACCOUNT_NL.Z_REVTYPE”
Answer:
Keep in mind that the Parser will attempt to add Security to this query (unless you are running as Admin).
That being said, why don’t you build a View with this statement, enable it on SLX and then just query your view?
E.G. SELECT * FROM MY_VIEW
That simplifies the statement for the Parser by hidding the complexity on the View.
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!