BAQ open join vs.ODBC

I'm not sure if this is a bug or a feature.
But now I'm assuming it has to do with the security that is built into BAQs.

I can reproduce it with a simple BAQ and two tables.


- Invoice table only, returns 20 rows.
Add Customer (open join) and drop 8 rows.
These turned out to be caused when a customer record did not have a territory selected.

- Invoice table only, returns 20 rows.
Add ShipTo table (open join) drop 4 rows.
The ShipTo table doesn't have a record with matching key fields (anymore)? This one is proving a little harder to track down.


I was using the BAQs on large month end summary reports, so when the numbers were off a little it wasn't obvious that I was missing rows.

--- In vantage@yahoogroups.com, "Bethany Rye" <brye@...> wrote:
>
> Sometimes this can be because of the links, sometimes it has to do with
> which table is the parent.
>
>
>
> Beth Rye
>
> IT Director
>
> CIGNYS
> 68 Williamson Street
> Saginaw, Michigan 48601
>
> Phone: (989) 753-1411 ext. 1114
>
> Direct Line: (989) 393-0108
>
> Fax: (989) 393-0208
>
> Email: <mailto:brye@...> brye@...
>
>
>
> ***ITAR NOTICE***
>
> This e-mail and/or the attached documents may contain technical data within
> the definition of the International Traffic in Arms regulations, and are
> subject to the export control laws of the US Government. Transfer of this
> data by any means to a foreign person, whether in the US or abroad, without
> an export license or other approval from the US Department of State, is
> prohibited. No portion of this e-mail or its attachment(s) may be reproduced
> without written consent of CIGNYS. If you are not the intended recipient or
> believe that you may have received this document in error, please notify the
> sender and delete this e-mail and any attachments immediately.
>
>
>
> From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
> cooner_55421
> Sent: Monday, August 01, 2011 3:16 PM
> To: vantage@yahoogroups.com
> Subject: [Vantage] BAQ open join vs.ODBC
>
>
>
>
>
> E9.04.506
>
> Hi,
>
> I wonder if anybody is familiar with BAQs sometimes not returning as many
> records as the same query in ODBC?
>
> The first time I noticed was a couple of weeks ago in a BAQ for Invoices.
> When I added the Customer table with an Open join, I started receiving fewer
> records returned. I called Epicor & was told this happens because of
> Territories.
> I made a few changes & thought I had it cleared up but...
>
> Now I have another BAQ that is dropping records.
> This time it is when I add the ShipTo table, even though I'm using an open
> join.
>
> The same query using ODBC returns more the records.
>
> I'd prefer to use BAQ for these if I can clear this up.
> I have a new call with Epicor, no news yet though.
>
> Thanks,
>
>
>
>
>
> [Non-text portions of this message have been removed]
>
Sometimes this can be because of the links, sometimes it has to do with
which table is the parent.



Beth Rye

IT Director

CIGNYS
68 Williamson Street
Saginaw, Michigan 48601

Phone: (989) 753-1411 ext. 1114

Direct Line: (989) 393-0108

Fax: (989) 393-0208

Email: <mailto:brye@...> brye@...



***ITAR NOTICE***

This e-mail and/or the attached documents may contain technical data within
the definition of the International Traffic in Arms regulations, and are
subject to the export control laws of the US Government. Transfer of this
data by any means to a foreign person, whether in the US or abroad, without
an export license or other approval from the US Department of State, is
prohibited. No portion of this e-mail or its attachment(s) may be reproduced
without written consent of CIGNYS. If you are not the intended recipient or
believe that you may have received this document in error, please notify the
sender and delete this e-mail and any attachments immediately.



From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of
cooner_55421
Sent: Monday, August 01, 2011 3:16 PM
To: vantage@yahoogroups.com
Subject: [Vantage] BAQ open join vs.ODBC





E9.04.506

Hi,

I wonder if anybody is familiar with BAQs sometimes not returning as many
records as the same query in ODBC?

The first time I noticed was a couple of weeks ago in a BAQ for Invoices.
When I added the Customer table with an Open join, I started receiving fewer
records returned. I called Epicor & was told this happens because of
Territories.
I made a few changes & thought I had it cleared up but...

Now I have another BAQ that is dropping records.
This time it is when I add the ShipTo table, even though I'm using an open
join.

The same query using ODBC returns more the records.

I'd prefer to use BAQ for these if I can clear this up.
I have a new call with Epicor, no news yet though.

Thanks,





[Non-text portions of this message have been removed]
I've had this happen before. BAQ's in 8.03 exhibit the same issues when
getting complex. BAQ's are good, but not great.



If you are lucky enough to be a SQL customer, I'd suggest creating a SQL
Query (with nolock), turning it into a View and safely hit it via ODBC.



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of cooner_55421
Sent: Monday, August 01, 2011 3:16 PM
To: vantage@yahoogroups.com
Subject: [Vantage] BAQ open join vs.ODBC





E9.04.506

Hi,

I wonder if anybody is familiar with BAQs sometimes not returning as
many records as the same query in ODBC?

The first time I noticed was a couple of weeks ago in a BAQ for
Invoices. When I added the Customer table with an Open join, I started
receiving fewer records returned. I called Epicor & was told this
happens because of Territories.
I made a few changes & thought I had it cleared up but...

Now I have another BAQ that is dropping records.
This time it is when I add the ShipTo table, even though I'm using an
open join.

The same query using ODBC returns more the records.

I'd prefer to use BAQ for these if I can clear this up.
I have a new call with Epicor, no news yet though.

Thanks,





[Non-text portions of this message have been removed]