BAQ results on two lines rather than on 1 line

I’ve written a BAQ that is to:
Report a parts last MFG-STK date and STK-CUS date transaction

What I am receiving is shown below -

Receiving this, the dates are showing up on two different lines:

Part       Last Date Stocked        Last Date to Cust	     On Hand
10012A        12/10/2016	                               1321
10012A                                 5/22/2017	       1321

Want it to do this, have the dates show up on one line:

Part       Last Date Stocked        Last Date to Cust	     On Hand
10012A        12/10/2016	       5/22/2017               1321

The dates are calculated fields:
Example:
Calculated_StkMfg
case when PartTran.TranType = ‘MFG-STK’ then MAX(PartTran.SysDate) end

Calculated_StkCust
case when PartTran.TranType = ‘STK-CUS’ then MAX(PartTran.SysDate) end

I’m taking an online SQL course so that I can write these better, but I guess I’m not there yet!

Any ideas on what I’m doing wrong, and what I can do to correct this will be MUCH appreciated!

You should be able to accomplish this with a group by or by using subqueries.

Post your BAQ we can get you these.

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

Unfortunately I’m SubQuery deficient…

can you post the baq? or the query phase.

thanks,
Ken

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

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.

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.

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

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]

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.

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!

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)

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

1 Like

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

4 Likes

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!

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

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!

I got it - never mind! Thank you again!

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.

1 Like

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?