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!
|
|
failed to parse sql
Posted: 10 Dec 09 8:31 AM
|
when it 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" |
|
|
|
Re: failed to parse sql
Posted: 10 Dec 09 8:33 AM
|
| sorry forgot to include ive tried it with and without the [sysdba]. in front. |
|
|
|
Re: failed to parse sql
Posted: 10 Dec 09 10:52 AM
|
make sure AcctID doesn't return a NULL value. Print your SQL to the clipboard so you can paste EXACTLY what is being returned.
Clipboard works with Application.clipboard.AsText = "Whatever"
|
|
|
|
Re: failed to parse sql
Posted: 11 Dec 09 4:04 AM
|
Hi LeVar, thanks for your reply. This is one of the first things i did (although i just msgbox'ed the sql out) and the syntax is perfect. Below is the sql that originally worked fine:
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.PARENTID = '" & AccID & "'" & _
"GROUP BY Z_ACCOUNT_NL.Z_REVYEAR, Z_ACCOUNT_NL.Z_REVTYPE"
and all i have done is replace or ACCOUNT.PARENTID = '" & AccID & "'" with or ACCOUNT.ACCOUNTID IN (select childid from [sysdba].[udf_GetRecursiveChildren]('" & AccID & "'))"
this is why i was thinking it must be a problem calling the function from script as it works fine in server management studio.
so we were wondering if there may be an issue trying to do a call to the function in script |
|
|
|
Re: failed to parse sql
Posted: 11 Dec 09 8:38 AM
|
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. |
|
|
|
Re: failed to parse sql
Posted: 11 Dec 09 8:48 AM
|
| Thanks raul, i also tried executing it in client as admin but it didnt work. Could you point me in the right direction as to how to build a view with the statement please. |
|
|
|
Re: failed to parse sql
Posted: 11 Dec 09 9:08 AM
|
Looking at it closer, indeed this could be a big headache as well, and cause some performance issues.
Why don't you do the following:
- Create a Native Connection to the Database and query your UDF. (Ref: SalesLogix Stored Procedures - slx_GetNativeConnInfo)
- Create a Comma Separated list of ID from the UDF returned recordset into a String variable.
- Modify your SQL Statement and give it the list of ChildIDs.
- Execute your new Statement (do this on your regular SLX connection)
|
|
|
|
Re: failed to parse sql
Posted: 12 Dec 09 6:31 AM
|
Originally posted by Raul A. Chavez
Looking at it closer, indeed this could be a big headache as well, and cause some performance issues.
Why don't you do the following:
- Create a Native Connection to the Database and query your UDF. (Ref: SalesLogix Stored Procedures - slx_GetNativeConnInfo)
- Create a Comma Separated list of ID from the UDF returned recordset into a String variable.
- Modify your SQL Statement and give it the list of ChildIDs.
- Execute your new Statement (do this on your regular SLX connection)
|
|
Nice idea. |
|
|
|
Re: failed to parse sql
Posted: 14 Dec 09 2:22 AM
|
| Sounds like a great idea Raul, could you give me a little help on how to start please this is a completely new area to me. |
|
|
|
Re: failed to parse sql
Posted: 14 Dec 09 6:18 AM
|
Never mind guys i found the following in another thread and have adapted it so my code now works. Thanks All!
Function
GetDirectConnection()
Dim strConn, theDirectConnection
Set gdcRS = Application.GetNewConnection.Execute("slx_getNativeConnInfo()")
strConn = "Provider=SQLOLEDB.1assword=(MyPassword)ersist Security Info=True;" & _
"User ID=(DBUserName);Initial Catalog=(DatabaseName);Data Source=(YourServerName)"
Set theDirectConnection = CreateObject("ADODB.Connection")
theDirectConnection.Open strConn
Set GetDirectConnection = theDirectConnection
End Function
|
|
|
|
Re: failed to parse sql
Posted: 14 Dec 09 1:09 PM
|
Originally posted by Andrew Grandin
when it 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?
"IN (select childid from [sysdba].[udf_GetRecursiveChildren]('" & AccID & "')) " & _
|
|
Correct, UDF's don't get along with the SLX OLE DB Provider (or vice versa).....
|
|
|
|