Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Highlighted
New Member
Posts: 10
Registered: ‎08-28-2009

SLX OLEDB SQL Parser

just tried to execute the following SQL in Admin, Execute SQL:

 

select top 1 firstname, left(firstname,2) from contact order by firstname desc

 

If the last firstname I have is "Victor", I would expect the query to return "Victor", "Vi" as it did in SQL 2005.

Instead I get "Victor", "0" (that is a zero)

 

Is there a list somewhere of  "allowed SQL Functions" for the SLX OLEDB? As my t-sql skills get better, I find my frustration with the SLX parser growing by the same scale. Smiley Happy

 

Copper Elite Contributor
Posts: 40
Registered: ‎03-31-2010

Re: SLX OLEDB SQL Parser

Hi There,

 

  I've definitely used many SQL functions before and I thought this should work.  I used another database query tool and connected through the SLX OLDDB provider and your SQL worked exactly as expected.  I got "ZsaZsa", "Zs".  I'm not sure if there's a difference in running through the Administrator but typically I use a third party OLE db tool, like ADO Explorer to test my SQL statements going through the provider.

 

  In general I've found the provider allows all the normal SQL functions to pass straight through to the SQL server.  I've usually run into difficulties with Sub queries or window functions but that's about it.

 

Regards,

 

James.

James Sutton
Gold Super Contributor
Posts: 3,087
Registered: ‎03-19-2009

Re: SLX OLEDB SQL Parser

[ Edited ]

Although the ExecuteSQL in the Admin is supposed to be the Like James, for may years I used ADO Explorer (and still do) to connect via the SalesLogix OLEDB provider to test SQL. A little while ago Walter Shpuntoff (http://www.radishtools.com) tipped me off to a free/open source tool called Query Express - http://www.albahari.com/queryexpress.aspx. Something slightly better is Query Express Plus (a derivative of QE) -  http://sourceforge.net/projects/queryexplus/.

 

Running your Select statement thru ExecuteSQL should produce the desired results (if valid).  I just ran it thru QE plus as well as ExecuteSQL on a 7.5.3 system w/SQL2008 as the db and it worked fine in both cases. 

--
RJ Ledger - rjledger@rjlSystems.net +1 603.369.3047 x101

".. Innovators in Mobility - Experts in Workflow Automation..."
http://www.rjlSystems.net - blog: www.rjlSystems.net/blog.html