I am trying to modify a BAQ that has a CTE (Reciepts) and that query has 3 tables with multiple joins that look circular and incorrect to me - however, the BAQ appears to be working.
At first i thought maybe this is a recursive CTE - but in my reading and testing the recursive CTE’s have a Union All table and this BAQ does not have a Union All.
Please see attached:
Any advice greatly appreciated.
DaveO
Can we see the generated SQL syntax for the subquery? I’ve done this before, with join TableA on TableA.X = TableB.X and TableA.Y = TableC.Y, but I’m curious how the designer handled it when one is an outer join.
The joins represent criteria, not processing order. This is a way of further restricting the result set. Keep in mind that the designer is a visual representation to make creating queries easier, and does not necessarily imply any limitations of what a SQL query can do. When you’re writing syntax, you can write join criteria referring to any table joined before it, or you can skip referring to another table at all and “join” it on constants or expressions.
The join between RcvMisc and PartTran is probably not needed and/or creating invalid results. I can’t think of a valid relationship between the two.
Can you post the generated sql statement on the main screen and give some insight as to what information you looking to get from this query?
*While CTEs can be used for recursive purposes, they can be used to create more efficient/faster queries. Since a CTE is executed prior tot the main query, it allows you to create A custom dataview that can be used in the top level (or other sub queries).
Here is the SQL for the full baq. There are two CTE’s and one TopLevel.
with [Receipts] as
(select
[PartTran].[PONum] as [PartTran_PONum],
[PartTran].[POLine] as [PartTran_POLine],
[PartTran].[PackSlip] as [PartTran_PackSlip],
[RcvHead].[PackSlip] as [RcvHead_PackSlip],
[PartTran].[PackLine] as [PartTran_PackLine],
[PartTran].[PartNum] as [PartTran_PartNum],
[RcvHead].[LandedCost] as [RcvHead_LandedCost],
[RcvMisc].[DocActualAmt] as [RcvMisc_DocActualAmt],
(PartTran.MtlBurUnitCost * PartTran.TranQty) as [Calculated_EstFreight],
[PartTran].[PostedToGL] as [PartTran_PostedToGL],
[RcvMisc].[InvoiceNum] as [RcvMisc_InvoiceNum],
[RcvHead].[ReceiptDate] as [RcvHead_ReceiptDate],
[RcvHead].[VendorNum] as [RcvHead_VendorNum],
[RcvMisc].[ActualAmt] as [RcvMisc_ActualAmt],
[RcvMisc].[InvoiceLine] as [RcvMisc_InvoiceLine],
[RcvMisc].[ApplyDate] as [RcvMisc_ApplyDate],
[RcvHead].[EntryDate] as [RcvHead_EntryDate],
[PartTran].[TranQty] as [PartTran_TranQty]
from Erp.RcvHead as RcvHead
inner join Erp.PartTran as PartTran on
RcvHead.Company = PartTran.Company
and RcvHead.VendorNum = PartTran.VendorNum
and RcvHead.PurPoint = PartTran.PurPoint
and RcvHead.PackSlip = PartTran.PackSlip
and ( (PartTran.TranType = ‘PUR-STK’ or PartTran.TranType = ‘PUR-INS’ ) )
left outer join Erp.RcvMisc as RcvMisc on
RcvHead.Company = RcvMisc.Company
and RcvHead.VendorNum = RcvMisc.VendorNum
and RcvHead.PurPoint = RcvMisc.PurPoint
and RcvHead.PackSlip = RcvMisc.PackSlip
inner join Erp.RcvMisc as RcvMisc
and
PartTran.Company = RcvMisc.Company
and PartTran.VendorNum = RcvMisc.VendorNum
and PartTran.PackSlip = RcvMisc.PackSlip
and PartTran.PackLine = RcvMisc.PackLine)
,[Invoices] as
(select
[GLJrnDtl].[Description] as [GLJrnDtl_Description],
[APInvHed].[InvoiceDate] as [APInvHed_InvoiceDate],
[APInvHed].[ApplyDate] as [APInvHed_ApplyDate],
[APInvHed].[Description] as [APInvHed_Description],
[APInvHed].[InvoiceNum] as [APInvHed_InvoiceNum],
[APInvHed].[VendorNum] as [APInvHed_VendorNum],
[GLJrnDtl].[BookDebitAmount] as [GLJrnDtl_BookDebitAmount],
[GLJrnDtl].[BookCreditAmount] as [GLJrnDtl_BookCreditAmount]
from Erp.GLJrnDtl as GLJrnDtl
inner join Erp.APInvHed as APInvHed on
GLJrnDtl.Company = APInvHed.Company
and GLJrnDtl.APInvoiceNum = APInvHed.InvoiceNum
and GLJrnDtl.VendorNum = APInvHed.VendorNum
inner join Erp.APInvMsc as APInvMsc on
GLJrnDtl.Company = APInvMsc.Company
and GLJrnDtl.VendorNum = APInvMsc.VendorNum
and GLJrnDtl.BookDebitAmount = APInvMsc.MiscAmt
inner join Erp.APInvMsc as APInvMsc
and
APInvHed.Company = APInvMsc.Company
and APInvHed.VendorNum = APInvMsc.VendorNum
and APInvHed.InvoiceNum = APInvMsc.InvoiceNum
where (GLJrnDtl.BalanceAcct = ‘21030|000|300’))
select
[Receipts].[PartTran_PONum] as [PartTran_PONum],
[Receipts].[PartTran_POLine] as [PartTran_POLine],
[Receipts].[PartTran_PackSlip] as [PartTran_PackSlip],
[Receipts].[PartTran_PackLine] as [PartTran_PackLine],
[Receipts].[PartTran_PartNum] as [PartTran_PartNum],
[Receipts].[RcvHead_LandedCost] as [RcvHead_LandedCost],
[Receipts].[PartTran_TranQty] as [PartTran_TranQty],
[Receipts].[RcvHead_EntryDate] as [RcvHead_EntryDate],
[Receipts].[RcvMisc_DocActualAmt] as [RcvMisc_DocActualAmt],
[Receipts].[Calculated_EstFreight] as [Calculated_EstFreight],
[Receipts].[PartTran_PostedToGL] as [PartTran_PostedToGL],
[Receipts].[RcvMisc_InvoiceNum] as [RcvMisc_InvoiceNum],
[Invoices].[GLJrnDtl_Description] as [GLJrnDtl_Description],
[Invoices].[APInvHed_InvoiceDate] as [APInvHed_InvoiceDate],
[Invoices].[APInvHed_Description] as [APInvHed_Description],
[Invoices].[APInvHed_InvoiceNum] as [APInvHed_InvoiceNum]
from Receipts as Receipts
left outer join Invoices as Invoices on
Receipts.RcvMisc_ActualAmt = Invoices.GLJrnDtl_BookDebitAmount
and Receipts.RcvMisc_InvoiceNum = Invoices.APInvHed_InvoiceNum
and Receipts.RcvMisc_ActualAmt = Invoices.GLJrnDtl_BookDebitAmount
This might work but the data i am not sure is correct probably the best way to fix it is to get tableA conected to TableB and tableC conected to TableA and then add another TableC called C1 connected to tableA and then add the fields needed this will duplicate what they are trying to do therew with that loop connection which it can cause all type of issues if you use that BAQ for a report or Dashbaord
Well, the generated SQL isn’t valid. I suspect making too many changes will break the BAQ and you’ll have to decouple the triangle anyway.
I would remove one of the joins and redo the join criteria as table criteria. For instance, delete the join between PartTran and RcvMisc, and on RcvMisc’s Table Criteria, add:
RcvMisc.Company = PartTran.Company
and RcvMisc.VendorNum = PartTran.VendorNum
and RcvMisc.PackSlip = PartTran.PackSlip
and RcvMisc.PackLine = PartTran.PackLine
I don’t know if that will give you the exact same results, since I’m not sure how the inner join and the outer join are working at the moment, so you might have to play.
You’ll have to make a tweak like this on the Invoices subquery, too–the SQL makes me think you have another triangle join there.
Thank you all for the input.
I am not a SQL guru so when I saw what I would call a circular reference I was confused. However since it was part of a CTE - I did not know if that circular reference was “normal” for CTE’s. It sounds like the consensus is that the BAQ should be updated to remove the circular or “double” reference.
Thank you again to all.
DaveO