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
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"
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.