BAQ results on two lines rather than on 1 line

jpol · June 2, 2017, 6:13pm

I’m grouping the following using the Advanced Group By Clause
PartTran.PartNum
PartQty.OnHandQty
PartTran.TranType

Unfortunately I’m SubQuery deficient…

knash · June 2, 2017, 6:16pm

can you post the baq? or the query phase.

thanks,
Ken

jpol · June 2, 2017, 6:37pm

select 
	[PartTran].[PartNum] as [PartTran_PartNum],
	(case when PartTran.TranType = 'MFG-STK' then MAX(PartTran.SysDate) end) as [Calculated_StkMfg],
	(case when PartTran.TranType = 'STK-CUS' then MAX(PartTran.SysDate) end) as [Calculated_StkCust],
	[PartQty].[OnHandQty] as [PartQty_OnHandQty]
from Erp.PartTran as PartTran
inner join Erp.PartCost as PartCost on 
	PartTran.Company = PartCost.Company
And
	PartTran.PartNum = PartCost.PartNum

inner join Erp.PartQty as PartQty on 
	PartCost.Company = PartQty.Company
And
	PartCost.PartNum = PartQty.PartNum

 where (PartTran.TranType = 'MFG-STK'  or PartTran.TranType = 'STK-CUS')
group by PartTran.PartNum,
	PartQty.OnHandQty,
	PartTran.TranType
 order by  PartTran.PartNum

Banderson · June 2, 2017, 6:37pm

It’s the tran type in your grouping that’s messing it up. If you have it to group by that, it will have a line for each type. If you want them on the same line, you can’t group by that.

jpol · June 2, 2017, 6:40pm

When I tried leaving it out, I received the following error:

Column ‘Erp.PartTran.TranType’ is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Banderson · June 2, 2017, 6:41pm

yeah I’m getting the same thing. It might have to be a sub query… Let me see what I can figure out.

knash · June 2, 2017, 6:48pm

The quick and dirty way. Make the query you have a innerquery. Add a new top query and reference it. Then you need to select the partNum field of the subquery. Then you will need to add the other fields as calculated fields.

You could also create two separate queries and then join by partnum. That would be three total queries instead of two. Your choice.

select NewList.[PartTran_PartNum], Max([Calculated_StkMfg]) AS [Calculated_StkMfg], MAX([Calculated_StkCust]) as [Calculated_StkCust], SUM([PartQty_OnHandQty])
from (select
[PartTran].[PartNum] as [PartTran_PartNum],
(case when PartTran.TranType = ‘MFG-STK’ then MAX(PartTran.SysDate) end) as [Calculated_StkMfg],
(case when PartTran.TranType = ‘STK-CUS’ then MAX(PartTran.SysDate) end) as [Calculated_StkCust],
[PartQty].[OnHandQty] as [PartQty_OnHandQty]
from Erp.PartTran as PartTran
inner join Erp.PartCost as PartCost on
PartTran.Company = PartCost.Company
And
PartTran.PartNum = PartCost.PartNum

inner join Erp.PartQty as PartQty on
PartCost.Company = PartQty.Company
And
PartCost.PartNum = PartQty.PartNum

where (PartTran.TranType = ‘MFG-STK’ or PartTran.TranType = ‘STK-CUS’)
group by PartTran.PartNum,
PartQty.OnHandQty,
PartTran.TranType
) as NewList
group by NewList.[PartTran_PartNum]
order by NewList.[PartTran_PartNum]

Banderson · June 2, 2017, 6:51pm

I made the three queries, filter the 2 subs by tran-type and just did max(date) for each sub, group by part number. Then brought in the sub queries to the top level and joined the three tables (like Ken suggested).

The case statements were throwing the group by error with the tran type, so it’s easier (for me) just to filter the parttran table by tran type and do a simple max date there.

Sub queries of this type really are pretty easy to use, and very powerful.

jpol · June 2, 2017, 6:54pm

Thank you gentlemen, I’ll give these a try, like I said, I’m not good with subqueries so it might take me a bit longer than you two did!

Thank you so much!

Banderson · June 2, 2017, 7:12pm

Let us know if you need a step by step walk through. It’s really very simple in the BAQ wizard. Don’t think of them as scary sub queries. Just think of them like another table that you’ve formatted/filtered to what you want. (until you get to union and CTE’s, those can be tricky)

jpol · June 2, 2017, 7:31pm

Alright, now you’ve done it - where is this “BAQ Wizard” you speak of?

Banderson · June 2, 2017, 8:20pm

Below is a pictorial step by step. I didn’t put in every single click, but it should get you close enough to see what’s going on.

Here is the menu location. It exists in a couple of other places too.

Once you open it, create a new query

<img src="/uploads/default/original/2X/c/c0280b67c34d3d538a5e35f3da171738043590ac.png" width=“690”

height=“422”>

Now the scary part, we are going to turn this into a sub query,

Repeat with the second sub query, just like the first one we did, but filter by STK-CUS instead

jpol · June 5, 2017, 11:51am

This was awesome, and I’m almost there…

The third subquery is giving me trouble. You say, “Repeat with the second subquery, just like the first one we did, but filter by STK-CUS instead.”

I’m unclear how to create the third subquery. If you could go over what steps I need to do and the order in which to do them that would be most helpful. Oh, and by the way I am extremely familiar with the BAQ Wizard/Designer as this is how I have been creating all my BAQ’s. I just wasn’t familiar with calling it a Wizard.

When I try to create the third subquery I’m trying to put the PartTran table in it, but I get the error that it has already been used.

Hope you had a great weekend…and thank you for all your help!

knash · June 5, 2017, 12:23pm

Just give the PartTran table a new alias, you should be good to go. The Alias is the popup when you add the table.

jpol · June 5, 2017, 12:51pm

I’m getting so close…I’m getting an error, “The multi-part identifier “PartTran.SysDate” could not be bound”. Could you tell me what this error means and what I’m doing wrong.

Otherwise, I feel that I have a better understanding of the Subqueries…THANK YOU SO MUCH!

jpol · June 5, 2017, 12:56pm

I got it - never mind! Thank you again!

Banderson · June 5, 2017, 1:36pm

Sorry, I didn’t get the notifications on the responses for this so I didn’t see this till now. Did you get everything figured out then?

Sometimes a little struggle helps you learn it better! At least that’s what I’ve found out learning this stuff.

jpol · June 5, 2017, 4:43pm

I pretty much have it, and I agree that 'a little struggle" helps!

I am having trouble now…Well, I added another subquery, that looks for the ‘STK-MTL’ transaction. When I look at the dates of the actual transactions in Part Transaction History Tracker, I am not getting the correct dates. So I decided I would add to the calculations a check for the correct TranType:
Example: (CASE when PartTran.TranType = ‘MFG-STK’ then max( PartTran.SysDate )END

Now I’m getting the following error:
Severity: Error, Table: , Field: , RowID: , Text: The multi-part identifier “PartTran.TranType” could not be bound.
The multi-part identifier “PartTran.SysDate” could not be bound.

What am I missing?

jpol · June 5, 2017, 5:24pm

Alright, I’ve moved on once again after reading your “wizard” document. I get it now about the TranType’s and have fixed that issue. But I am now receiving another error:
Severity: Error, Table: , Field: , RowID: , Text: The multi-part identifier “PartTran.SysDate” could not be bound.
The multi-part identifier “PartTran.SysDate” could not be bound.

I’m including the code I have thus far:
select
[PartQty].[PartNum] as [PartQty_PartNum],
[PartQty].[OnHandQty] as [PartQty_OnHandQty],
[SubQuery1].[Calculated_LastProdDate] as [Calculated_LastProdDate],
[SubQuery3].[Calculated_LastDateCust] as [Calculated_LastDateCust],
[SubQuery4].[Calculated_LastStk2Mtl] as [Calculated_LastStk2Mtl]
from Erp.PartQty as PartQty
inner join (select
[PartTran].[PartNum] as [PartTran_PartNum],
[PartTran].[TranType] as [PartTran_TranType],
(max( PartTran.SysDate )) as [Calculated_LastProdDate]
from Erp.PartTran as PartTran
where (PartTran.TranType = ‘STK-CUS’)
group by [PartTran].[PartNum],
[PartTran].[TranType]) as SubQuery1 on
SubQuery1.PartTran_PartNum = PartQty.PartNum

inner join  (select 
	[PartTran1].[PartNum] as [PartTran1_PartNum],
	[PartTran1].[TranType] as [PartTran1_TranType],
	(max( PartTran.SysDate )) as [Calculated_LastDateCust]
from Erp.PartTran as PartTran1
 where (PartTran1.TranType = 'MFG-STK')
group by [PartTran1].[PartNum],
	[PartTran1].[TranType])  as SubQuery3 on 
	SubQuery3.PartTran1_PartNum = PartQty.PartNum

inner join  (select 
	[PartTran2].[PartNum] as [PartTran2_PartNum],
	[PartTran2].[TranType] as [PartTran2_TranType],
	(max( PartTran.SysDate )) as [Calculated_LastStk2Mtl]
from Erp.PartTran as PartTran2
 where (PartTran2.TranType = 'STK-MTL')
group by [PartTran2].[PartNum],
	[PartTran2].[TranType])  as SubQuery4 on 
	SubQuery4.PartTran2_PartNum = PartQty.PartNum

inner join Erp.PartCost as PartCost on 
	PartCost.Company = PartQty.Company
And
	PartCost.PartNum = PartQty.PartNum

Banderson · June 5, 2017, 6:41pm

The max lines in the section below should match the tables that are selected for the sub query. See where [PartTran2].[PartNum] as [PartTran2_PartNum] say PartTran2 (that’s that alias thing you had trouble with earlier.) The Max() needs to have PartTran2 as well so it’s looking at the same table. Change that to match and see if it works. You’ll have to watch out for that on copy and paste because if you are moving from one sub to a different one, different tables on the sheets need to have unique aliases. See the screen shot below to see how I make sure to only use available fields.

inner join  (select 
	[PartTran2].[PartNum] as [PartTran2_PartNum],
	[PartTran2].[TranType] as [PartTran2_TranType],
	(max( PartTran.SysDate )) as [Calculated_LastStk2Mtl]

You’ll have to watch out for that on copy and paste because if you are moving from one sub to a different one, different tables on the sheets need to have unique aliases. See the screen shot below to see how I make sure to only use available fields.