I think I need a Union, but I’m not sure.
If I put a bunch of “N/A” in calculated fields on my second query, I don’t have to retrieve anything from the table? Is that how I get to the same number of columns?
Ben
Correct. For example, I do a lot of unionizing the JobAssmbl and JobMtl tables, because they are basically a BOM and person searching may not know if it’s and assembly or material. So I put them into a union. The Assembly doesn’t have a material seq, so I make a calculated field an put in a 0 there. So all of the assemblies show a 0 in that field.
The other thing that you can do is a left join. So something only exists on one side of the join, it will just show a null when it isn’t on the right side. (or vice versa)
Thanks.
I’m familiar with Left Join. I tried to adjust the primary query to include the the stragglers the second query is getting me by changing the Joins. It wasn’t giving me any results I needed.
I will dig into the Union with what you have shown about the Calculated Fields.
Ben
I’m getting the two records I needed but everything else is getting doubled.
select
[CashHead].[TranDate] as [CashHead_TranDate],
[CashHead].[CheckRef] as [CashHead_CheckRef],
[Customer].[Name] as [Customer_Name],
[ShipTo].[Name] as [ShipTo_Name],
[InvcHead].[OrderNum] as [InvcHead_OrderNum],
[CashDtl].[InvoiceNum] as [CashDtl_InvoiceNum],
[CashHead].[Reference] as [CashHead_Reference],
[CashHead].[TranAmt] as [CashHead_TranAmt],
[CashDtl].[TranAmt] as [CashDtl_TranAmt],
[SalesRep].[Name] as [SalesRep_Name]
from Erp.CashHead as CashHead
inner join Erp.CashDtl as CashDtl on
CashHead.Company = CashDtl.Company
And
CashHead.GroupID = CashDtl.GroupID
And
CashHead.HeadNum = CashDtl.HeadNum
and ( CashDtl.TranAmt <> 0 )
inner join Erp.InvcHead as InvcHead on
CashDtl.Company = InvcHead.Company
And
CashDtl.InvoiceNum = InvcHead.InvoiceNum
inner join Erp.SalesRep as SalesRep on
InvcHead.Company = SalesRep.Company
And
Left(SalesRepList,4) = SalesRep.SalesRepCode
inner join Erp.Customer as Customer on
InvcHead.Company = Customer.Company
And
InvcHead.CustNum = Customer.CustNum
inner join Erp.ShipTo as ShipTo on
Customer.Company = ShipTo.Company
And
Customer.CustNum = ShipTo.CustNum
inner join Erp.OrderHed as OrderHed on
InvcHead.Company = OrderHed.Company
And
InvcHead.OrderNum = OrderHed.OrderNum
inner join Erp.OrderHed as OrderHed and
OrderHed.ShipToNum = ShipTo.ShipToNum
And
OrderHed.Company = ShipTo.Company
where (CashHead.TranDate = '3/22/2019')
UNION
select
[CashHead1].[TranDate] as [CashHead1_TranDate],
[CashHead1].[CheckRef] as [CashHead1_CheckRef],
('N/A') as [Calculated_BillTo],
('N/A') as [Calculated_ShipTo],
(0) as [Calculated_Order],
(0) as [Calculated_Invoice],
[CashHead1].[Reference] as [CashHead1_Reference],
[CashHead1].[DocTranAmt] as [CashHead1_DocTranAmt],
(0) as [Calculated_Applied],
('N/A') as [Calculated_FirstRep]
from Erp.CashHead as CashHead1
left outer join Erp.CashDtl as CashDtl1 on
CashHead1.Company = CashDtl1.Company
And
CashHead1.GroupID = CashDtl1.GroupID
And
CashHead1.HeadNum = CashDtl1.HeadNum
and ( CashDtl1.InvoiceNum is null )
where (CashHead1.TranDate = '3/22/2019' and CashHead1.TranAmt <> 0)
order by CashHead.CheckRef
So why didn’t the left joins work?
So, joins like these are usually problematic. If you can avoid them, you should. You should be able to make a link through the tables without having to go around like that. That helps with the join structure.
And if you are doing a left join, you don’t need this.
I appreciate you taking the time to look at this.
I’m not thrilled with the circular thing your red arrows are showing. I tried many different linkings before getting result of one record of one Sales Order #, one ShipTo and one Salesperson. I tried various left, right and full joins in that process. This was the only working combination.
I don’t know where to left join, for your other point.
If it is here, I still get too many results:
It might be that you just should make them separate queries and put them on 2 different tabs on a dashboard.
I don’t fully follow what you are trying to do, and I suck at reading straight SQL, since I use BAQ’s exclusively. So I’m not going to be too much help in that exact specifics.
Brandon,
When payment comes into the Financial Dept. people are manually filling in an Excel spreadsheet and emailing to Sales each day. I find that cringe-worthy since it is all system information. I’m trying to make a dashboard with all the info at one stop. Sorted by date, they need to see the Customer Name, Ship-to Name, Order #, Invoice #, Amount and the (first) salesperson.
I’ve told them that having the second query information thrown into this is making it difficult from the data side. Deaf ears…
Perhaps the separate tabs gets me there, per your suggestion.
I’ve been typing out SQL long before I heard of Epicor. So, I think in SQL. The BAQ limitation are maddening at times. Especially when I can move the one filter in SQL window and I get what I want. Yet I can’t alter that in the BAQ system.
Thank you,
Ben
So if these records are not related to an invoice, how would they be related to any order data?
They aren’t.
So I had to do the Calc. Field on Customer, Ship-To, OrderNum, InvoiceNum, and SalesRep.
There is no CashDtl Record.
ok, I think that the union is not going to be the way to make this work. So that means the calc fields are irrelevant. Start with the table(s) that have information for all of the transactions you are looking for. To start with, only include those tables and those fields that you need on those tables. Then when you have all of your transactions showing up, start adding your other informational tables one at a time. This will be where you’re left joins start coming in.
That’s where I started, not know these other, disconnected records were getting added. I had the first part done weeks ago. I was ready to roll the Dashboard out. Then I was shown the Reimbursement and other random bits they tack on to the Excel. That led me here, for a quick answer.
Starting over might be for the best.
Thanks,
Ben
I would agree with starting over.
I think that you have too many filters that are conflicting. And your joins could be run through a single stream of tables instead of around like you showed them. I think you are just trying to do more than you need to.
Do you just need one field from each of those tables? If so I have a cool trick for you. This is nice because it doesn’t puke if it can’t find one.
I read through that but I don’t follow.
My issue is that I’m expecting ShipTo to relate easily to OrderHed, but it doesn’t.
This is where I got all the circular linking you disapprove of.
Okay I’m good with that