I am trying to do a BAQ that covers Vendor and Vendor GL CTRL code. I can see the Vendor table and EntityGLC table but can’t seem to get info from the BAQ just 0 rows. Is it because they are linked via Vendor Number which is an integer in Entity but string in Vendor?
VendorNum is definitely an integer. You might have mistakenly linked Key1 to VendorID. Make sure your relationships are:
- EntityGLC.Company = Vendor.Company
- EntityGLC.Key1 = Vendor.VendorNum
Wouldn’t hurt to add a table criteria on EntityGLC of
- RelatedToFile = constant
Vendor
thanks for the info @ckrusen
I am still not getting anything though. My sql
select
[EntityGLC].[Key1] as [EntityGLC_Key1],
[EntityGLC].[BusinessEntity] as [EntityGLC_BusinessEntity],
[EntityGLC].[Company] as [EntityGLC_Company],
[EntityGLC].[ExtCompanyID] as [EntityGLC_ExtCompanyID],
[EntityGLC].[GLControlCode] as [EntityGLC_GLControlCode],
[EntityGLC].[GLControlType] as [EntityGLC_GLControlType]
from Erp.Vendor as Vendor
inner join Erp.EntityGLC as EntityGLC on
EntityGLC.Company = Vendor.Company
and EntityGLC.Key1 = Vendor.VendorNum
My results
Severity: Error, Table: , Field: , RowID: , Text: Bad SQL statement.
Review the server event logs for details.
Query returned 0 row(s).
Query has no more records to return.
Query execution total time: 28.4009 ms.
The error is probably generated when an EntityGLC record has a non-number as Key1.
Add a table criteria of EntityGLC.RelatedToFile = Vendor (constant). That way Key will only ever be an integer.
Like you said in your 1st post - add vendor to it…
Now works, thanks