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: 24 Jan 07 7:31 PM
|
fiogf49gjkf0d Hi,
I am trying to run the following query through the SLX OLE DB Provider (and also through the ‘Execute SQL…’ from the Administrator) but getting the “Failed to parse SQL” error:
Update t1 set t1.colum1 = ‘blah’ from table_1 t1 inner join table_2 t2 on t1.accountid = t2.accountid where t1.accountid = ‘xxx’
Can anyone advice the solution?
I’m using SLX 6.2
Thanks. |
|
|
|
Re: Failed to parse SQL
Posted: 25 Jan 07 12:16 AM
|
fiogf49gjkf0d Update commands do not use join statements.
Update table1
set column1 = 'blah'
from table1, table2
where table1.accountid = table2.accountid
AND table1.accountid = 'xxx'
c |
|
|
|
Re: Failed to parse SQL
Posted: 25 Jan 07 8:12 AM
|
fiogf49gjkf0d I've found that the SLX OLE simply can't handle complex update statements. Even though this is a very simple join (and works fine in SQL) the ole is very tempermental. I use 6.2.4 and have had the same problem and have yet to find a workaround. I've been stuck writing vb scripts to handle what should be a very simple update statement. |
|
|
|
Re: Failed to parse SQL
Posted: 25 Jan 07 9:04 AM
|
fiogf49gjkf0d One thing to note, when using SQL statements filtered through SLX: The SLX application will transform the SQL statement by pulling it appart then reassembleing it with some of it's own additions. (i.e. WHERE USERID ... SECCODE ...)
If you have never done so; I would highly recommend throwing a trace on SLX for 5-10 minutes then cruising through the SQL statements. It will give you a really good feel for the SLX translation / transformation of SQL statements!
However, regarding the Update statement itself, the SQL help documentation will reflect the 'From Table1, Table2 WHERE' format as well. I can't count how many times I have started to use a join with an update statement and had to go back and change it! 
c |
|
|
|
Re: Failed to parse SQL
Posted: 25 Jan 07 9:08 AM
|
fiogf49gjkf0d I'm sure that someone else here will have the specifics, but my understanding is that the SLX OLEDB Provider supports SQL statements only up to a certain (and quite dated) level of ANSI SQL.
The type of update you are trying to perform, while appearing straightforward, was introduced in a later release of the ANSI SQL standard. |
|
|
|
Re: Failed to parse SQL
Posted: 29 Jan 07 1:50 PM
|
fiogf49gjkf0d It's certainly true that the SalesLogix interface (e.g., Execute SQL) doesn't implement all of standard SQL. However, the join option on the Update statement isn't part of the ANSI standard. It is an extension in MS SQL Server and possibly other vendor's offerings.
Remember that SalesLogix works with both Oracle and MS SQL Server. I don't know that any vendor fully implements the ASNI standard, and they all have their own quirks and extensions. So if Sage wants to be database agnostic, they have to restrict their product to a universally accepted subset of SQL. (Of course, like any development organization, they also never have enough resources to do everything they'd like to do.)
Regardless, it's frustrating to have to deal with constraints like this.
|
|
|
|