ODBC Connection MySQL input data error
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I am getting an error when running an input from ODBC connection. Alteryx cannot open a query that runs successfully in MySQL Workbench. I have been successful using the same ODBC connection to query the same MySQL server. Does the syntax requirement vary in Alteryx SQL editor from what MySQL workbench requires? Are there restrictions or limitations to query?
Solved! Go to Solution.
- Labels:
- Database Connection
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Can you share your error? or screenshot?
Did you establish and test your connection ? Is ur connection running successfully ?
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Yes, connection works for generic queries . The error message lists the sql code, followed by "no columns returned."
The SQL i'm attempting to run does include nested queries.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I see my screenshot did not post correctly, so I"m trying again
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Just saw ur error,
could u also post the entire sql query for ref.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
The following is the code entered into SQL Editor in input tool that results in the error. Thanks for your help!
SELECT DISTINCT
cus.customer,
cus.registrationId,
cus.joined,
ic.industry_code AS industry_code_1,
class.title AS class
FROM precompile_accounts AS cus
LEFT JOIN (
SELECT DISTINCT rel.fid AS customer, rel.company AS company ,
IFNULL((SELECT 1 FROM tblCompanyRelationships AS mktg WHERE mktg.fid=rel.fid AND mktg.fid_type=2 AND mktg.company=rel.company AND mktg.relationship=2),0) AS relationship
FROM tblCompanyRelationships AS rel
WHERE rel.fid_type=2 AND (rel.relationship=5) AND rel.primary_id =1
GROUP BY rel.fid, rel.company, rel.relationship)
AS rel ON rel.customer = cus.customer
LEFT JOIN tblCompanies as com on rel.company = com.id
LEFT JOIN company_industry_code AS comind ON comind.company_id=rel.company AND comind.priority = 1
LEFT JOIN industry_code AS ic ON ic.industry_code = comind.industry_code
LEFT JOIN tblCompanyClasses AS class ON class.id=com.class
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I was able to resolve by adding the database name to the table name before each from and join clause. Thanks to r4upadhye for your assistance with this issue. Still not sure why I did not have to specify database name on previous queries using same ODBC connection.
SELECT DISTINCT
cus.customer,
cus.registrationId,
cus.joined,
ic.industry_code AS industry_code_1,
class.title AS class
FROM DatabaseName.precompile_accounts AS cus
LEFT JOIN (
SELECT DISTINCT rel.fid AS customer, rel.company AS company ,
IFNULL((SELECT 1 FROM DatabaseName.tblCompanyRelationships AS mktg WHERE mktg.fid=rel.fid AND mktg.fid_type=2 AND mktg.company=rel.company AND mktg.relationship=2),0) AS relationship
FROM DatabaseName.tblCompanyRelationships AS rel
WHERE rel.fid_type=2 AND (rel.relationship=5) AND rel.primary_id =1
GROUP BY rel.fid, rel.company, rel.relationship)
AS rel ON rel.customer = cus.customer
LEFT JOIN DatabaseName.tblCompanies as com on rel.company = com.id
LEFT JOIN DatabaseName.company_industry_code AS comind ON comind.company_id=rel.company AND comind.priority = 1
LEFT JOIN DatabaseName.industry_code AS ic ON ic.industry_code = comind.industry_code
LEFT JOIN DatabaseName.tblCompanyClasses AS class ON class.id=com.class