slxdeveloper.com Community Forums

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!

Architect Forums - ADO General
Forum to discuss ADO specific questions for use with SalesLogix. View the code of conduct for posting guidelines.

Forums RSS Feed

Back to Forum List | Back to ADO General | New Thread

View: Search:

Author

Thread: Problem with subquery in 7.2

Rick Smith
Posts: 96

Problem with subquery in 7.2 Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 11 Dec 07 3:32 PM

I wrote a custom import tool that creates Contacts from an Excel file. In the tool I have SQL that includes a JOIN to a subquery - working just great in 6.2.3. Now in the process of upgrading to 7.2, the same code causes an error: "Subquery is not supported". Really? Did the provider de-evolve? Is there another approach anyone can recommend? Here's the entire statement:

SELECT A1.C_CONTACTIMPORTID, A1._ACCOUNT, A1._CITY, A1._STATE, A4.ACCOUNTID, A4.ACCOUNT, A4.CITY, A4.STATE FROM C_CONTACTIMPORT A1 JOIN (SELECT A2.ACCOUNTID, A2.ACCOUNT, A3.CITY, A3.STATE, LEFT(A2.ACCOUNT,6) + LEFT(A3.CITY,3) + LEFT(A3.STATE,2) AS 'matchcode' FROM ACCOUNT A2 JOIN ADDRESS A3 ON A2.ADDRESSID = A3.ADDRESSID ) A4 ON LEFT(A1._ACCOUNT,6) + LEFT(A1._CITY,3) + LEFT(A1._STATE,2) = A4.matchcode ORDER BY (LEN(A1._ACCOUNT) - LEN(A4.ACCOUNT)) ASC, A4.ACCOUNT ASC

[Reply][Quote]

Scott Sommerfeldt
Posts: 5

Re: Problem with subquery in 7.2 Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 12 Dec 07 10:49 AM

I feel your pain. I just went through an upgrade from 6.2.3. The 7.2 Provider does not support derived Tables.
(This is the problem)
(SELECT A2.ACCOUNTID, A2.ACCOUNT, A3.CITY, A3.STATE, LEFT(A2.ACCOUNT,6) + LEFT(A3.CITY,3) + LEFT(A3.STATE,2) AS 'matchcode' FROM ACCOUNT A2 JOIN ADDRESS A3 ON A2.ADDRESSID = A3.ADDRESSID ) A4

However Views are supported in 7.2 so you can create a view instead of the derived table, and use that in your query.

[Reply][Quote]

Rick Smith
Posts: 96

Re: Problem with subquery in 7.2 Your last visit to this thread was on 1/1/1970 12:00:00 AM

Posted: 12 Dec 07 8:30 PM

I've had to utilize a stored procedure because the parameters in the LEFT commands are variables.
I also had a similar problem with a recordset that I was binding to a DataGrid using Ryan's method for dynamically creating datagrid columns: http://www.slxdeveloper.com/page.aspx?action=viewarticle&articleid=26&
...for that one, a View solved the subquery issue since the statement .SQL.Text = objRS.SQL won't work with a sproc.

[Reply][Quote]

Page 1 of 1

You can subscribe to receive a daily forum digest in your user profile. View the site code of conduct for posting guidelines.

Forum RSS Feed - Subscribe to the forum RSS feed to keep on top of the latest forum activity!

slxdeveloper.com is brought to you courtesy of Ryan Farley & Customer FX Corporation. This site, and all contents herein, are Copyright © 2024 Customer FX Corporation. The information and opinions expressed here are not endorsed by Sage Software.	code of conduct \|

page cache (param): 5/17/2024 4:10:24 AM