BAQ Outer Joins

Yahoo Archive
1
Hello Len, Nancy.

I think you are right Len, on null values and links. I have not tried it, but you could leave the first link of PartClass as it is (Outer Joint and the standard field-join), and change your second link for PartRev to be Outer-Join, but only linking through "Company", then you set a criteria for PartRev table to select only when PartRev.PartNum is equal to Part.PartNum.

I'm guessing, that trick can relieve potential "null-linking-conflicts" by selecting only through the basic indexed field: PartNum.

I hope you can figure it out.

Roel Martinez
ERP Analyst

SABRE TUBULAR STRUCTURES (tm)
www.sabretubularstructures.com<http://www.sabretubularstructures.com/>
8653 E. Hwy 67 | Alvarado, TX 76009
Phone: 817-852-1700 ext. 11858 | Direct Line: 817-852-1858 |Fax: 817-852-1705

[cid:image002.jpg@01CB60A8.877A9100]

Sabre Industries, Inc. ("Sabre") E-MAIL NOTICE - This transmission and any attachments may be confidential or subject to a legally recognized privilege and is intended only to be received and read by the proper party having a reason to send communications to or from Sabre. If you do not believe that you are the proper recipient of this message because of its address, contents and/or otherwise believe that this message was sent to you in error, you should not disclose, print, copy or disseminate the information contained in this message. If you have received this in error, please reply and notify the sender (only) and delete the message. Unauthorized interception of this e-mail is a violation of federal criminal law and state laws.
The text in this communication does not reflect an intention by Sabre to conduct a transaction or make any agreement by electronic means. Only a document which bears a handwritten signature of an authorized signatory of Sabre shall be binding upon Sabre and such documents may be attached indicating a desire to conduct a transaction or make any agreement. Nothing contained in the text of this message shall satisfy the requirements for a writing, and nothing contained herein shall constitute a contract or electronic signature under the Electronic Signatures in Global and National Commerce Act, any version of the Uniform Electronic Transactions Act or any other statute governing electronic transactions or any statutory adoptions or equivalents of the aforementioned acts and convention, and any other laws or regulations of any state or country related to electronic contracts, electronic signatures, or electronic records shall not apply to Sabre or the recipient.

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf Of Len Hartka
Sent: Thursday, September 30, 2010 12:33 PM
To: vantage@yahoogroups.com
Subject: RE: [Vantage] BAQ Outer Joins



Good Day Nancy:

Problem may be the link and not the type of join.

Also is your join in the right direction - Part is parent table and
Partnnnn is child. Looks Ok, but I am not real good at ready code.

Parts with no revision can have a Null revision. Nulls can cause
problems in filtering and joining.

I suggest doing a new one with just the PartRev and see if you get
the records you expect, then one with just Part.Class and see if you get
what you expect. You can also flip from outer to normal join and see
what happens.




len.hartka@...<mailto:len.hartka%40sunautomation.com>

________________________________

From: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com> [mailto:vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>] On Behalf
Of Nancy
Sent: Thursday, September 30, 2010 10:31 AM
To: vantage@yahoogroups.com<mailto:vantage%40yahoogroups.com>
Subject: [Vantage] BAQ Outer Joins

Good Morning,

I am trying to get a query to work in the BAQ and it just doesn't seem
to want to allow me to do two outer joins.

I have Parts parent related to part classes, with outer join so that ALL
parts with or without part class are returned.

Then I also have Parts parent related to part rev, with out join, so
that ALL parts with or without part class are returned.

It is not returning parts that have no part rev. It seems like it is ok
with the first outer join, but doesn't allow another.

Has anyone else tried multiple outer joins in the BAQ?
We're on 9.04.506B

This is the "query phrase":
for each Part no-lock , each PartClass no-lock outer-join where
(Part.Company = PartClass.Company and Part.ClassID = PartClass.ClassID )
, each PartRev no-lock outer-join where (Part.Company = PartRev.Company
and Part.PartNum = PartRev.PartNum ).

Thanks,
Nancy

This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.

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



[Non-text portions of this message have been removed]
2
Good Morning,

I am trying to get a query to work in the BAQ and it just doesn't seem to want to allow me to do two outer joins.

I have Parts parent related to part classes, with outer join so that ALL parts with or without part class are returned.

Then I also have Parts parent related to part rev, with out join, so that ALL parts with or without part class are returned.

It is not returning parts that have no part rev. It seems like it is ok with the first outer join, but doesn't allow another.

Has anyone else tried multiple outer joins in the BAQ?
We're on 9.04.506B

This is the "query phrase":
for each Part no-lock , each PartClass no-lock outer-join where (Part.Company = PartClass.Company and Part.ClassID = PartClass.ClassID ) , each PartRev no-lock outer-join where (Part.Company = PartRev.Company and Part.PartNum = PartRev.PartNum ).


Thanks,
Nancy
3
Good Day Nancy:

Problem may be the link and not the type of join.

Also is your join in the right direction - Part is parent table and
Partnnnn is child. Looks Ok, but I am not real good at ready code.

Parts with no revision can have a Null revision. Nulls can cause
problems in filtering and joining.

I suggest doing a new one with just the PartRev and see if you get
the records you expect, then one with just Part.Class and see if you get
what you expect. You can also flip from outer to normal join and see
what happens.




len.hartka@...

________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Nancy
Sent: Thursday, September 30, 2010 10:31 AM
To: vantage@yahoogroups.com
Subject: [Vantage] BAQ Outer Joins




Good Morning,

I am trying to get a query to work in the BAQ and it just doesn't seem
to want to allow me to do two outer joins.

I have Parts parent related to part classes, with outer join so that ALL
parts with or without part class are returned.

Then I also have Parts parent related to part rev, with out join, so
that ALL parts with or without part class are returned.

It is not returning parts that have no part rev. It seems like it is ok
with the first outer join, but doesn't allow another.

Has anyone else tried multiple outer joins in the BAQ?
We're on 9.04.506B

This is the "query phrase":
for each Part no-lock , each PartClass no-lock outer-join where
(Part.Company = PartClass.Company and Part.ClassID = PartClass.ClassID )
, each PartRev no-lock outer-join where (Part.Company = PartRev.Company
and Part.PartNum = PartRev.PartNum ).

Thanks,
Nancy






This e-mail and any attachments may contain proprietary and/or confidential information. If you are not the intended recipient, please notify the sender immediately by reply e-mail or at 410-472-2900 and then delete the message without using, disseminating, or copying this message or any portion thereof. With e-mail communications you are urged to protect against viruses.


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