CCK | 2023-03-13 08:57:25 UTC | #1

Hello Everyone,

I'm trying to create a BAQ to retrieve PO number + Vendor Name from GLJrnDtl for AP Invoices but got stuck with Bad SQL statement and all sort of redundant results.

Appreciate if someone can show me my mistakes.

![image|690x380](upload://zrXQUuheEK4EruyfyjwHr8sRfYa.png)

select 
	[GLJrnDtl].[Description] as [GLJrnDtl_Description],
	[Vendor].[Name] as [Vendor_Name],
	[GLJrnDtl].[PostedDate] as [GLJrnDtl_PostedDate],
	[GLJrnDtl].[FiscalYear] as [GLJrnDtl_FiscalYear],
	[GLJrnDtl].[FiscalPeriod] as [GLJrnDtl_FiscalPeriod],
	[GLJrnDtl].[SourceModule] as [GLJrnDtl_SourceModule],
	[GLJrnDtl].[APInvoiceNum] as [GLJrnDtl_APInvoiceNum],
	[APInvDtl].[PONum] as [APInvDtl_PONum],
	[APInvDtl].[InvoiceNum] as [APInvDtl_InvoiceNum],
	[GLJrnDtl].[SegValue1] as [GLJrnDtl_SegValue1],
	[GLJrnDtl].[GLAccount] as [GLJrnDtl_GLAccount],
	[GLJrnDtl].[BookDebitAmount] as [GLJrnDtl_BookDebitAmount],
	[GLJrnDtl].[BookCreditAmount] as [GLJrnDtl_BookCreditAmount],
	[GLJrnDtl].[DebitAmount] as [GLJrnDtl_DebitAmount],
	[GLJrnDtl].[CreditAmount] as [GLJrnDtl_CreditAmount]
from Erp.GLJrnDtl as GLJrnDtl
inner join Erp.TranGLC as TranGLC on 
	GLJrnDtl.JournalCode = TranGLC.JournalCode
	and GLJrnDtl.JournalNum = TranGLC.JournalNum
	and GLJrnDtl.JournalLine = TranGLC.JournalLine
	and GLJrnDtl.FiscalYear = TranGLC.FiscalYear
	and GLJrnDtl.CorrAccUID = TranGLC.CorrAccUID
inner join Erp.APInvDtl as APInvDtl on 
	TranGLC.Company = APInvDtl.Company
	and TranGLC.Key2 = APInvDtl.InvoiceNum
	and TranGLC.Key3 = APInvDtl.InvoiceLine
	and TranGLC.Key1 = APInvDtl.VendorNum
inner join Erp.Vendor as Vendor on 
	APInvDtl.Company = Vendor.Company
	and APInvDtl.VendorNum = Vendor.VendorNum
where (GLJrnDtl.PostedDate >= '01/01/2022'  and GLJrnDtl.FiscalYear = 2022  and GLJrnDtl.SegValue1 = '51000')

Thanks!

-------------------------

CCK | 2023-03-13 09:12:57 UTC | #2

Sorted this out by myself, thanks everyone!

-------------------------