Forgive my ignorance, but I’m relatively novice when it comes to actually creating a BAQ from scratch. I have five tables, each a filtered set of parts from an external datasource. I’m creating a BAQ that can pull these tables into a single dataset. I assume that pulling five separate tables without a join is a no go, especially since my testing shows errors and loooong waits. However, I’m not sure how to join the tables appropriately. Do I call a single table, then use a subquery union or something like that for the other four?
In my experience you should be able to join multiple tables together in a BAQ.
It would be helpful to know what your error is as well as what the joins look like that are causing long query times.
Part of the issue is that I want to join them all with an inner join but there’s “no top level query” when I have all my tables in a query and select “inner join” type.
If you’re novice, start small to get a grasp of the logic.
A field needs to exist on each table otherwise you’re pulling all the data from each table and attaching it every result from the other table.
Take Part and PartPlant, if you don’t join them, your results will show you every PartPlant record for every PartNum beside every Part.PartNum record (boy that’s confusing to simplify)
This is what you’d see, and this would be much worse when you have 1000s of Part.PartNum records
| Part.PartNum | PartPlant.PartNum | PartPlant.Plant |
|---|---|---|
| Part 1 | Part 1 | Plant 1 |
| Part 1 | Part 1 | Plant 2 |
| Part 1 | Part 1 | Plant 3 |
| Part 1 | Part 2 | Plant 1 |
| Part 1 | Part 2 | Plant 2 |
| Part 1 | Part 3 | Plant 2 |
| Part 1 | Part 3 | Plant 3 |
Instead you’d join these tables on PartNum, because each table has that field this would show you only the records where those 2 tables are matched
| Part.PartNum | PartPlant.PartNum | PartPlant.Plant |
|---|---|---|
| Part 1 | Part 1 | Plant 1 |
| Part 1 | Part 1 | Plant 2 |
| Part 1 | Part 1 | Plant 3 |
The only “joins” I see are creating links on the rows of each table. If I do a join in a subquery it wants a top level query.
I think… if you’re pulling the same data (columns) from each source, you may be able to set each table up in its own union type subquery and see if you can pull them all together that way.
This should union the results of each source into one final data set.
I get an error when attempting to do that:
The column types must match.
So for the first error, you need Part Number from both subqueries to both be nvarchar or both be varchar. What you might want to do is plan on them being nvarchar, and then if you bring a varchar field, use a calculated field of type nvarchar in your Display Columns, instead of adding the varchar field to the Display Columns. The calculated field could probably just say “convert(nvarchar, PNfield)” where “PNfield” is the field in varchar type.
1 Like
Check out the tools user guide on the help about BAQs. They have some good use cases to start learning BAQs. Also, check out this video I did. I think it will explain some things to you.
1 Like