Join problem in BAQ 8.03.403D

Yahoo Archive
1
> I guess part of my problem is that I could do these in my sleep in Access.
> FWIW, here is what the SQL would look like in Access:

OK, this was bothering me. There had to be some bass-ackwards way to get
what you want. I think I got it.

I created a BAQ that links OrderHed to InvcHead. In the Phrase Builder, I
clicked on the Summarize box for InvcHead and made the link an OuterJoin.
Save the phrase.

Now here's the trick. Create a calculated field called numInvoices of data
type Integer. The Expression will be:

TOTAL(InvcHead.Number01)

You can use any numeric field in the calculation.

In the Display Tab, show the OrderNum and the calculated field.

When you go to the Analyze tab and click "Test...", you'll get a numeric
value for each order that has at least one invoice header record and a null
value for each order that does not have an Invoice Header. You can use a BAQ
report to select out the records that have a numeric value in Crystal's
Record Selection criteria.

Not the most straight forward path, but I think it gets you what you wanted.

Mark W.
2
I want a BAQ listing sales orders that have not been invoiced. I outer join the OrderHed table to the InvcHead table on Company and OrderNum (the default). I then set a criteria that the InvcHead.InvNum equals 0. What I get is a list of ALL the sales orders, not just those without an invoice. Why does my BAQ select all records instead of only uninvoiced? Here is the query phrase:

for each OrderHed no-lock , each InvcHead outer-join where ( InvcHead.InvoiceNum = 0) and (OrderHed.Company = InvcHead.Company and OrderHed.OrderNum = InvcHead.OrderNum) no-lock .


Thom Rose
Controller
Electric Mirror, LLC
T 425 776-4946 ext. 1024
A 11831 Beverly Park Road, Building D, Everett, WA 98204 USA
www.electricmirror.com<http://www.electricmirror.com/>
Note: The information contained in this e-mail, including any attachments, is legally privileged and confidential. If you are not the intended recipient you are hereby notified that any reading, use or dissemination of this message is strictly prohibited. If you have received this message in error, please immediately notify us by telephone at 425-776-4946 and delete this message from your system. Even though this e-mail and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free, and no responsibility is accepted by Electric Mirror LLC for any loss or damage arising in any way from its use. www.electricmirror.com<http://www.electricmirror.com>



[Non-text portions of this message have been removed]
3
> I want a BAQ listing sales orders that have not been invoiced. I outer
> join the OrderHed table to the InvcHead table on Company and OrderNum (the
> default). I then set a criteria that the InvcHead.InvNum equals 0. What I
> get is a list of ALL the sales orders, not just those without an invoice.
> Why does my BAQ select all records instead of only uninvoiced? Here is the
> query phrase:
>
> for each OrderHed no-lock , each InvcHead outer-join where (
> InvcHead.InvoiceNum = 0) and (OrderHed.Company = InvcHead.Company and
> OrderHed.OrderNum = InvcHead.OrderNum) no-lock .

The Outer-Join is creating invoice headers for non-matching sales orders and
the default value for for InvoiceNum is zero. Try approaching it the other
way: find all invoices not invoiced and then match them to the sales orders.

Mark W.
4
Thank you for the response. I'm not quite following you. If I have an invoice, presumably it is invoiced. So, how would I find any invoices not invoiced?

Thom Rose
Controller
Electric Mirror, LLC
T 425 776-4946 ext. 1024
A 11831 Beverly Park Road, Building D, Everett, WA 98204 USA
www.electricmirror.com<http://www.electricmirror.com/>

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Mark Wonsil
Sent: Tuesday, July 08, 2008 10:58 AM
To: vantage@yahoogroups.com
Subject: {Disarmed} RE: [Vantage] Join problem in BAQ 8.03.403D


> I want a BAQ listing sales orders that have not been invoiced. I outer
> join the OrderHed table to the InvcHead table on Company and OrderNum (the
> default). I then set a criteria that the InvcHead.InvNum equals 0. What I
> get is a list of ALL the sales orders, not just those without an invoice.
> Why does my BAQ select all records instead of only uninvoiced? Here is the
> query phrase:
>
> for each OrderHed no-lock , each InvcHead outer-join where (
> InvcHead.InvoiceNum = 0) and (OrderHed.Company = InvcHead.Company and
> OrderHed.OrderNum = InvcHead.OrderNum) no-lock .

The Outer-Join is creating invoice headers for non-matching sales orders and
the default value for for InvoiceNum is zero. Try approaching it the other
way: find all invoices not invoiced and then match them to the sales orders.

Mark W.



[Non-text portions of this message have been removed]
5
> Thank you for the response. I'm not quite following you. If I have an
> invoice, presumably it is invoiced. So, how would I find any invoices not
> invoiced?

Sorry about that. In Vantage, an Invoice Header record is created at the
time of shipment, so that record will exist before it's actually an invoice.

Alternately, you could check for an Invoice Date to see if it has been
invoiced.

Mark W.
6
In the Main-frame world this would be called an "Unmatched Query". I have never found an effective way to do this type of query using SQL on any PC database. I have also asked several "experts" and none have ever given me an answer. In the past, we imported the data into Excel and then used filters to get the desired results.

If you wanted receipts versus orders you could just run the received-not-invoiced report.


From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Mark Wonsil
Sent: Tuesday, July 08, 2008 12:58 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Join problem in BAQ 8.03.403D


> I want a BAQ listing sales orders that have not been invoiced. I outer
> join the OrderHed table to the InvcHead table on Company and OrderNum (the
> default). I then set a criteria that the InvcHead.InvNum equals 0. What I
> get is a list of ALL the sales orders, not just those without an invoice.
> Why does my BAQ select all records instead of only uninvoiced? Here is the
> query phrase:
>
> for each OrderHed no-lock , each InvcHead outer-join where (
> InvcHead.InvoiceNum = 0) and (OrderHed.Company = InvcHead.Company and
> OrderHed.OrderNum = InvcHead.OrderNum) no-lock .

The Outer-Join is creating invoice headers for non-matching sales orders and
the default value for for InvoiceNum is zero. Try approaching it the other
way: find all invoices not invoiced and then match them to the sales orders.

Mark W.


________________________________
DISCLAIMER:
This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately.


[Non-text portions of this message have been removed]
7
That would work well if I was always waiting until shipment to invoice. We do a lot of deposit and advance billing invoices which happen after the order is created but before the order ships.

Thom Rose
Controller
Electric Mirror, LLC
T 425 776-4946 ext. 1024
A 11831 Beverly Park Road, Building D, Everett, WA 98204 USA
www.electricmirror.com<http://www.electricmirror.com/>

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Mark Wonsil
Sent: Tuesday, July 08, 2008 11:42 AM
To: vantage@yahoogroups.com
Subject: {Disarmed} RE: {Disarmed} RE: [Vantage] Join problem in BAQ 8.03.403D


> Thank you for the response. I'm not quite following you. If I have an
> invoice, presumably it is invoiced. So, how would I find any invoices not
> invoiced?

Sorry about that. In Vantage, an Invoice Header record is created at the
time of shipment, so that record will exist before it's actually an invoice.

Alternately, you could check for an Invoice Date to see if it has been
invoiced.

Mark W.



[Non-text portions of this message have been removed]
8
I guess part of my problem is that I could do these in my sleep in Access. FWIW, here is what the SQL would look like in Access:

SELECT
InvcHead.InvoiceNum,
OrderHed.OrderNum
FROM
OrderHed LEFT JOIN InvcHead
ON OrderHed.Company = InvcHead.Company AND OrderHed.OrderNum = InvcHead.OrderNum
WHERE
InvcHead.InvoiceNum Is Null;

So, now you have an answer, at least for Access. If it were only this easy in Vista/Vantage...

Thom Rose
Controller
Electric Mirror, LLC
T 425 776-4946 ext. 1024
A 11831 Beverly Park Road, Building D, Everett, WA 98204 USA
www.electricmirror.com<http://www.electricmirror.com/>

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Charles Carden
Sent: Tuesday, July 08, 2008 11:54 AM
To: vantage@yahoogroups.com
Subject: {Disarmed} RE: [Vantage] Join problem in BAQ 8.03.403D


In the Main-frame world this would be called an "Unmatched Query". I have never found an effective way to do this type of query using SQL on any PC database. I have also asked several "experts" and none have ever given me an answer. In the past, we imported the data into Excel and then used filters to get the desired results.

If you wanted receipts versus orders you could just run the received-not-invoiced report.

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of Mark Wonsil
Sent: Tuesday, July 08, 2008 12:58 PM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Join problem in BAQ 8.03.403D

> I want a BAQ listing sales orders that have not been invoiced. I outer
> join the OrderHed table to the InvcHead table on Company and OrderNum (the
> default). I then set a criteria that the InvcHead.InvNum equals 0. What I
> get is a list of ALL the sales orders, not just those without an invoice.
> Why does my BAQ select all records instead of only uninvoiced? Here is the
> query phrase:
>
> for each OrderHed no-lock , each InvcHead outer-join where (
> InvcHead.InvoiceNum = 0) and (OrderHed.Company = InvcHead.Company and
> OrderHed.OrderNum = InvcHead.OrderNum) no-lock .

The Outer-Join is creating invoice headers for non-matching sales orders and
the default value for for InvoiceNum is zero. Try approaching it the other
way: find all invoices not invoiced and then match them to the sales orders.

Mark W.

________________________________
DISCLAIMER:
This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately.

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



[Non-text portions of this message have been removed]
9
Can't do a null for any field in Vantage except a date field.

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Thomas Rose
Sent: Tuesday, July 08, 2008 2:05 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] Join problem in BAQ 8.03.403D


I guess part of my problem is that I could do these in my sleep in Access. FWIW, here is what the SQL would look like in Access:

SELECT
InvcHead.InvoiceNum,
OrderHed.OrderNum
FROM
OrderHed LEFT JOIN InvcHead
ON OrderHed.Company = InvcHead.Company AND OrderHed.OrderNum = InvcHead.OrderNum
WHERE
InvcHead.InvoiceNum Is Null;

So, now you have an answer, at least for Access. If it were only this easy in Vista/Vantage...

Thom Rose
Controller
Electric Mirror, LLC
T 425 776-4946 ext. 1024
A 11831 Beverly Park Road, Building D, Everett, WA 98204 USA
www.electricmirror.com<http://www.electricmirror.com/>

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf Of Charles Carden
Sent: Tuesday, July 08, 2008 11:54 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: {Disarmed} RE: [Vantage] Join problem in BAQ 8.03.403D

In the Main-frame world this would be called an "Unmatched Query". I have never found an effective way to do this type of query using SQL on any PC database. I have also asked several "experts" and none have ever given me an answer. In the past, we imported the data into Excel and then used filters to get the desired results.

If you wanted receipts versus orders you could just run the received-not-invoiced report.

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>] On Behalf Of Mark Wonsil
Sent: Tuesday, July 08, 2008 12:58 PM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com><mailto:vantage%40yahoogroups.com>
Subject: RE: [Vantage] Join problem in BAQ 8.03.403D

> I want a BAQ listing sales orders that have not been invoiced. I outer
> join the OrderHed table to the InvcHead table on Company and OrderNum (the
> default). I then set a criteria that the InvcHead.InvNum equals 0. What I
> get is a list of ALL the sales orders, not just those without an invoice.
> Why does my BAQ select all records instead of only uninvoiced? Here is the
> query phrase:
>
> for each OrderHed no-lock , each InvcHead outer-join where (
> InvcHead.InvoiceNum = 0) and (OrderHed.Company = InvcHead.Company and
> OrderHed.OrderNum = InvcHead.OrderNum) no-lock .

The Outer-Join is creating invoice headers for non-matching sales orders and
the default value for for InvoiceNum is zero. Try approaching it the other
way: find all invoices not invoiced and then match them to the sales orders.

Mark W.

________________________________
DISCLAIMER:
This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately.

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

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


________________________________
DISCLAIMER:
This e-mail, including attachments, may include confidential and/or proprietary information, and may be used only by the person or entity to which it is addressed. If the reader of this e-mail is not the intended recipient or his or her authorized agent, the reader is hereby notified that any dissemination, distribution or copying of this e-mail is prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and delete this e-mail immediately.


[Non-text portions of this message have been removed]
10
All the outer join to InvcHead is doing is limiting what InvcHead data may be returned (in your case - only when no invoice exists - which also means no InvcHead record exists - so the join is useless).

That BAQ SHOULD return all open orders.

BAQs don't support exclusionary joins (like SQL does).

Rob Brown

--- On Tue, 7/8/08, Thomas Rose <t.rose@...> wrote:

From: Thomas Rose <t.rose@...>
Subject: [Vantage] Join problem in BAQ 8.03.403D
To: "vantage@yahoogroups.com" <vantage@yahoogroups.com>
Date: Tuesday, July 8, 2008, 1:47 PM






I want a BAQ listing sales orders that have not been invoiced. I outer join the OrderHed table to the InvcHead table on Company and OrderNum (the default). I then set a criteria that the InvcHead.InvNum equals 0. What I get is a list of ALL the sales orders, not just those without an invoice. Why does my BAQ select all records instead of only uninvoiced? Here is the query phrase:

for each OrderHed no-lock , each InvcHead outer-join where ( InvcHead.InvoiceNum = 0) and (OrderHed.Company = InvcHead.Company and OrderHed.OrderNum = InvcHead.OrderNum) no-lock .

Thom Rose
Controller
Electric Mirror, LLC
T 425 776-4946 ext. 1024
A 11831 Beverly Park Road, Building D, Everett, WA 98204 USA
www.electricmirror. com<http://www.electric mirror.com/>
Note: The information contained in this e-mail, including any attachments, is legally privileged and confidential. If you are not the intended recipient you are hereby notified that any reading, use or dissemination of this message is strictly prohibited. If you have received this message in error, please immediately notify us by telephone at 425-776-4946 and delete this message from your system. Even though this e-mail and any attachments are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free, and no responsibility is accepted by Electric Mirror LLC for any loss or damage arising in any way from its use. www.electricmirror. com<http://www.electric mirror.com>

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