Create ODBC Dataset From Two Tables
When creating a new dataset, I am having trouble using any kind of join in the SQL. I am using ODBC and want to create a dataset that, for example, combines Invoice Header and Invoice Detail information. Do I need to create each of those as their own dataset and then do a transform, or can I do it all at once in one dataset?
Best Answer
-
It had to do with the ODBC driver we have. The join had to be written this way:
FROM { IJ SA_INVOICE_HEADER LEFT JOIN SA_INVOICE_LINE ON SA_INVOICE_HEADER.invoice_num = SA_INVOICE_LINE.invoice_num}
The query works now but unfortunately, it takes forever to run. It is a really old driver, but the newer version won't work with our ERP system, so the best solution seems to be just connecting the tables as is and then using ETL to do all the joins.
1
Answers
-
Do that all in one dataset if you can, unless you'd have a reason to have these as individual datasets for separate use in Domo.
Most of our Workbench ODBC queries have multiple table joins. It's just passing the query to the server to process, so it should be able to use whatever logic you provide it. For example, we have some custom function calls in our queries, and they pass through and run just fine (for us that's on Oracle).
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
I keep getting the following error. The query runs just fine in Access, so I am not sure what the problem is.
ERROR [37000] [ProvideX][ODBC Driver]Unexpected extra token: LEFT
Here is my query:
SELECT SA_INVOICE_HEADER.invoice_date, SA_INVOICE_HEADER.doc_num, SA_INVOICE_HEADER.invoice_num, SA_INVOICE_HEADER.ship_via, SA_INVOICE_HEADER.salesperson_terr, SA_INVOICE_HEADER.cust_num, SA_INVOICE_LINE.ship_to_num, SA_INVOICE_LINE.item_num, SA_INVOICE_LINE.item_class, SA_INVOICE_LINE.gl_post_table, SA_INVOICE_LINE.um_selling, SA_INVOICE_LINE.amount_shipped
FROM SA_INVOICE_HEADER LEFT JOIN SA_INVOICE_LINE ON SA_INVOICE_HEADER.invoice_num = SA_INVOICE_LINE.invoice_num0 -
That looks just fine to me. Does it change if you did LEFT OUTER JOIN instead?
Aaron
MajorDomo @ Merit Medical
**Say "Thanks" by clicking the heart in the post that helped you.
**Please mark the post that solves your problem by clicking on "Accept as Solution"0 -
It had to do with the ODBC driver we have. The join had to be written this way:
FROM { IJ SA_INVOICE_HEADER LEFT JOIN SA_INVOICE_LINE ON SA_INVOICE_HEADER.invoice_num = SA_INVOICE_LINE.invoice_num}
The query works now but unfortunately, it takes forever to run. It is a really old driver, but the newer version won't work with our ERP system, so the best solution seems to be just connecting the tables as is and then using ETL to do all the joins.
1
Categories
- All Categories
- 1.8K Product Ideas
- 1.8K Ideas Exchange
- 1.5K Connect
- 1.2K Connectors
- 297 Workbench
- 6 Cloud Amplifier
- 8 Federated
- 2.9K Transform
- 100 SQL DataFlows
- 614 Datasets
- 2.2K Magic ETL
- 3.8K Visualize
- 2.5K Charting
- 729 Beast Mode
- 53 App Studio
- 40 Variables
- 678 Automate
- 173 Apps
- 451 APIs & Domo Developer
- 46 Workflows
- 8 DomoAI
- 34 Predict
- 14 Jupyter Workspaces
- 20 R & Python Tiles
- 394 Distribute
- 113 Domo Everywhere
- 275 Scheduled Reports
- 6 Software Integrations
- 121 Manage
- 118 Governance & Security
- Domo Community Gallery
- 32 Product Releases
- 10 Domo University
- 5.4K Community Forums
- 40 Getting Started
- 30 Community Member Introductions
- 108 Community Announcements
- 4.8K Archive