Thanks Thad. I dug through the handbook for a couple hours last night and didn't see that one. I did figure out how to do a left outer join by using the Open Query statement.
I'll check this out. My 'export' requests are getting more and more complex. :-)
Troy Funte
Liberty Electronics
I'll check this out. My 'export' requests are getting more and more complex. :-)
Troy Funte
Liberty Electronics
----- Original Message -----
From: Thad Jacobs
To: 'vantage@yahoogroups.com'
Sent: Monday, April 08, 2002 11:27 AM
Subject: RE: [Vantage] SQL Query Question
you would use a CREATE BUFFER statement.
DEFINE BUFFER PartRev_1 for partrev.
You could use the PartRev_1 table reference without messing with your
current PartRev record.
HTH,
Thaddeus Jacobs
Information Solutions Developer
Kinematic Automation, Inc.
mailto:tjacobs@...
-----Original Message-----
From: Troy Funte [mailto:tfunte@...]
Sent: Sunday, April 07, 2002 7:21 PM
To: Vantage
Subject: [Vantage] SQL Query Question
My SQL knowledge is limited to reading an occasional Crystal SQL query and
fiddling with a Vantage Export Query. Can anyone tell me how to do the
following so that I don't have to dig through the 5.5 MB pdf file
(programmers handbook) that I'm downloading from progress?
I need to know how to do two things:
1. Add an Alias table (PartRev_1). I need to use to the PartRev table
AGAIN, this time linking the PartMtl.MtlPartNum --> PartRev.PartNum.
2. Use a Left Outer Join
I have the following query in my export:
for each PartMtl Where PartMtl.Company = 'lei' no-lock,
each PartRev Where PartRev.Company = PartMtl.Company
and PartRev.PartNum = PartMtl.PartNum
and PartRev.RevisionNum = PartMtl.RevisionNum
and PartRev.Approved = yes no-lock,
each PartPlant Where PartPlant.Company = PartMtl.Company
and PartPlant.PartNum = PartMtl.MtlPartNum no-lock,
each Part Where Part.Company = PartMtl.Company
and Part.PartNum = PartMtl.PartNum
and Part.TypeCode = 'm' no-lock:
ALSO, I have the link I'm looking for in Crystal here, but I need it in the
Progress language:
SELECT
PartMtl.PartNum, PartMtl.RevisionNum, PartMtl.MtlPartNum,
PartMtl.QtyPer,
PartRev.Approved,
PartRev_1.RevisionNum, PartRev_1.Approved,
PartPlant.LeadTime
FROM
vantage.PartMtl PartMtl LEFT JOIN vantage.PartRev PartRev_1 ON
PartMtl.Company = PartRev_1.Company AND
PartMtl.MtlPartNum = PartRev_1.PartNum INNER JOIN vantage.PartPlant
PartPlant ON
PartMtl.Company = PartPlant.Company AND
PartMtl.PartNum = PartPlant.PartNum LEFT JOIN vantage.PartRev PartRev ON
PartMtl.Company = PartRev.Company AND
PartMtl.PartNum = PartRev.PartNum AND
PartMtl.RevisionNum = PartRev.RevisionNum
WHERE
PartRev.Approved = 1 AND
PartRev_1.Approved = 1
Troy Funte
Liberty Electronics
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have
already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and
Crystal Reports and other 'goodies', please goto:
http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto:
http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto:
http://groups.yahoo.com/group/vantage/links
Your use of Yahoo! Groups is subject to http://docs.yahoo.com/info/terms/
Yahoo! Groups Sponsor
ADVERTISEMENT
Useful links for the Yahoo!Groups Vantage Board are: ( Note: You must have already linked your email address to a yahoo id to enable access. )
(1) To access the Files Section of our Yahoo!Group for Report Builder and Crystal Reports and other 'goodies', please goto: http://groups.yahoo.com/group/vantage/files/.
(2) To search through old msg's goto: http://groups.yahoo.com/group/vantage/messages
(3) To view links to Vendors that provide Vantage services goto: http://groups.yahoo.com/group/vantage/links
Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.
[Non-text portions of this message have been removed]