We are attempting to set up a BAQ to pull parts ordered by customer, while also displaying on hand quantities for said parts. We would like to be able to input the customer ID as a parameter so it doesn’t try and pull in all customers at one time.
What is the best approach to this?
We believe customer would be the top level. Then we would have 2 subqueries.
for parts ordered (using order.hed & order.dtl tables) and
for parts (using part & part.bin)
We believe subquery 1 would be joined with the customer at the top level and subquery 2 would be joined with items in subquery 1.
We are struggling with the join types in the table relations piece.
Example: Matching rows from Part and PartBin, all rows from Part, all rows from PartBin, all rows from Part and PartBin.
It is a preference. I prefer CTEs because I can map out what I want and create my own tables to do the joins. That is basically all a CTE is, an in-memory table that you create.
If it were me, I would create a CTE to get the Customer, OrderHead, and OrderDtl information I wanted into a result set.
Then I would create a Top Level that uses the CTE as Table 1 and join to the part tables you want to use.
I guess it depends on what information you need… but I don’t see why you can do this all in one query:
Customer <> OrderHed <> OrderDtl < PartBin
Add your parameter on Customer.CustID (or whatever you want to use).
You could filter OrderDtl on OpenLine = True (if you only want to see open demand).
Left Outer Join PartBin to OrderDtl where PartNum = PartNum… this can give you On Hand Qty.
Unless you’re trying to sum things I guess… in that case, yes, subqueries would be necessary.
Are you looking for individual rows of the parts they order… or sums (how many of each part they’ve ordered historically or over a certain period, etc.)?
We have some customers that require we hold finished good stock for them in our warehouse. So the project manager is looking for a BAQ he can run each month that shows which parts are in inventory and how many of each … but only for that specific customer.
I’m just asking because you might be giving some false positives in your report if customers take the same part numbers. If you are supposed to have 10 on hand for custA and 12 for CustB, if inventory is 15, it will look like you have enough for each customer when in actuality, you do not. This may not be possible in your environment, just something I would be careful of.
We are slowly learning about the types of subqueries, which is why I think we are struggling in this case. My other question - when approaching a BAQ, what question should we ask ourselves to know if we should use a subquery… I’m assuming if something needs to be summed??
We will definitely watch for the false positives, but we typically don’t sell the same part to multiple customers.
Complexity is my number 1. How difficult is it to get to the dataset you want. If you want to do aggregates. Really just comes down to experience and understanding the data structure to know you will not be able to get from A to B without issues. I still even start some BAQs thinking it will have 1 subquery and end up adding CTEs after I run into issues.
They are all rather complex right now in the learning phase
We can manage simple ones, but subqueries is another level and adding calculated fields is yet another level.
If you have multiple sites/warehouses you may need or want to do some changes but seems to me this is all you need.
Personally i use subqueries if i’m doing multiple complex things on a specific table, instead of trying to work that all out with the other tables at the top level i’ll use a subquery and then join it to the top, this also helps make sure each subquery is operating properly. It can be a pain to troubleshoot where you have an improper join, or 2 joins are conflicting with each other when you have multiple tables at the top.
As for CTE vs InnerSubQuery…i have no idea how to use CTE i’ve always stuck with inner…so far so good.
Thank you for your input and for sharing the BAQ with me - really appreciate that.
I am now trying to set a parameter on the customer so when someone runs the query, they can run for 1 specific customer if they wish or all customers. We would set this parameter on the Customer or on the OrderHed??
Because it’s an inner join from Customer to OrderHed the only customers that will exist in the results are customers found with orders. Not sure if it makes a difference, if a customer has never placed an order it may error out either way, or it may return 0 results. I would personally put it on the Customer and not OrderHed in case i wanted to return customer information even if they never placed an order. If you place it on OrderHed and a customer never placed an order you would never get any information from the Customer table. If that’s the case you would also need to make the Customer to OrderHed a left outer join to return all Customer data even if it doesn’t exist in OrderHed.
I’m completely missing something in our BAQ but cannot figure out what. The parameter set is intended to be used by whatever customer someone chooses. Problem is - it still returns all customers even when applying the parameter. What am I missing??
