I’m struggling with the BAQ query format.
I have to show Cash Receipts by date in a Dashboard. My first Query works fine for all the data I need to show on normal items:
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].[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
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' and CashHead.TranAmt <> 0)
But there a re a few additional records I have to show that don’t have InvoiceNum. They seem to be Reimbursment checks that are not related to an InvoiceNum so the InvoiceNum is Null and there isn’t any link to the Ship-To and Order data without the Invoice.
The Query that gives me these specific record is this
select
[CashHead].[TranDate] as [CashHead_TranDate],
[CashHead].[CheckRef] as [CashHead_CheckRef],
[CashDtl].[InvoiceNum] as [CashDtl_InvoiceNum],
[CashHead].[Reference] as [CashHead_Reference],
[CashHead].[DocTranAmt] as [CashHead_DocTranAmt]
from Erp.CashHead as CashHead
full outer join Erp.CashDtl as CashDtl on
CashHead.Company = CashDtl.Company
And
CashHead.GroupID = CashDtl.GroupID
And
CashHead.HeadNum = CashDtl.HeadNum
where (CashHead.TranDate = '3/22/2019' and CashHead.TranAmt <> 0 and CashDtl.InvoiceNum is null)
I don’t know how to combine these results into one. I think I need Union, but I don’t have the same columns on the second query. The lack of InvoiceNum is negating so many of the columns.
Please help,
Thanks,
Ben
If you want to unionize the two, you just need the same number of columns. It doesn’t matter what’s in it, as long as they are the same type. So if you are missing one, make a calculated field, and just fill in a string or number or whatever you need to communicate that there isn’t one. Then place that one in the same spot as the other one that has it.
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
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, 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’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.
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.
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 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.
