Need help with Export Utility

Yahoo Archive
1
Wayne,

I added "outer-join " to my code, which did the trick.
Thanks.

Mike Tonoyan / All American Products Co.
E-mail: miket@...

----- Original Message -----
From: "Wayne Cox" <wmc20@...>
To: <vantage@yahoogroups.com>
Sent: Thursday, October 03, 2002 10:45 AM
Subject: Re: [Vantage] Need help with Export Utility


> At 09:37 AM 10/3/2002 -0700, you wrote:
> > When I take the following statements out,
> > " each CustCnt Where CustCnt.Company = InvcDtl.Company
> > and CustCnt.conNum = InvcDtl.ShpConNum no-lock"
> > it exports correct number of records. Any ideas?
>
> You're trying to include the ones that have no contact, correct? I think
> you need to fore a left outer join. Try adding a outer-join keyword,
like:
>
> each CustCnt Where CustCnt.Company = InvcDtl.Company
> and CustCnt.conNum = InvcDtl.ShpConNum outer-join no-lock
>
> I think that option is only in 4.0 and up.
>
> -Wayne Cox
> Twenty Three -- Information Technology Consulting
> 828-685-2338
>
>
>
> [Non-text portions of this message have been removed]
>
>
>
> 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/
>
2
Hi everybody,

I need to export invoice detail information to a DBF. The following is the
Export Utility Query which exports only the invoices which have Customer
Contacts.
for each InvcHead Where InvcHead.Company = cur-comp
and InvcHead.InvoiceDate >= (TODAY - 5)
and InvcHead.InvoiceType = "Shp" no-lock,
each InvcDtl Where InvcDtl.Company = InvcHead.Company
and InvcDtl.InvoiceNum = InvcHead.InvoiceNum no-lock,
each Customer Where Customer.Company = InvcDtl.Company
and Customer.CustNum = InvcHead.CustNum
and Customer.CustNum = InvcDtl.CustNum no-lock,
each ShipTo Where ShipTo.Company = InvcDtl.Company
and ShipTo.CustNum = InvcHead.CustNum
and ShipTo.CustNum = InvcDtl.CustNum
and ShipTo.ShipToNum = InvcDtl.ShipToNum
and ShipTo.Country = 'CANADA' no-lock,
each ShipVia Where ShipVia.Company = InvcDtl.Company
and ShipVia.ShipViaCode = InvcDtl.ShipViaCode no-lock,
each CustCnt Where CustCnt.Company = InvcDtl.Company
and CustCnt.conNum = InvcDtl.ShpConNum no-lock
BY ShipTo.Name
BY InvcHead.InvoiceNum
BY InvcDtl.InvoiceLine:

When I take the following statements out,
" each CustCnt Where CustCnt.Company = InvcDtl.Company
and CustCnt.conNum = InvcDtl.ShpConNum no-lock"
it exports correct number of records. Any ideas?
Thank you in advance.

Mike Tonoyan / MIS Manager
All American Products Co.
1135 Aviation Place
San Fernando, CA 91340
USA
Tel.: 818-361-0059 Ext.: 246
Fax 818-898-2236
E-mail: miket@...
Web: www.allamericanproducts.com
3
At 09:37 AM 10/3/2002 -0700, you wrote:
> When I take the following statements out,
> " each CustCnt Where CustCnt.Company = InvcDtl.Company
> and CustCnt.conNum = InvcDtl.ShpConNum no-lock"
> it exports correct number of records. Any ideas?

You're trying to include the ones that have no contact, correct? I think
you need to fore a left outer join. Try adding a outer-join keyword, like:

each CustCnt Where CustCnt.Company = InvcDtl.Company
and CustCnt.conNum = InvcDtl.ShpConNum outer-join no-lock

I think that option is only in 4.0 and up.

-Wayne Cox
Twenty Three -- Information Technology Consulting
828-685-2338



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

Unfortunately in the Invoice Detail table, the ShpConNum may be left blank.
Since there are no blank records in the CustCnt table, the join is only
getting
records where the Invoice Detail has a ShpConNum entered.

You have got a couple options here.

Option 1:
Have the person who is responsible for the customer master file set up an
entry
for the PrimSCon and/or verify that the orders are being entered with
complete
information.

Option 2:
Exclude the link in the query and get all the data regardless if contact
information
is available.

Option 3:
Utilize an outer join in the query to pull the all the Invoice Detail
records and only
the CustCnt records when available.

HTH,
Calvin Dekker
Misha 1, Ltd.
www.misha1.net



-----Original Message-----
From: Mike Tonoyan [mailto:miket@...]
Sent: Thursday, October 03, 2002 11:37 AM
To: vantage@yahoogroups.com
Subject: [Vantage] Need help with Export Utility


Hi everybody,

I need to export invoice detail information to a DBF. The following is the
Export Utility Query which exports only the invoices which have Customer
Contacts.
for each InvcHead Where InvcHead.Company = cur-comp
and InvcHead.InvoiceDate >= (TODAY - 5)
and InvcHead.InvoiceType = "Shp" no-lock,
each InvcDtl Where InvcDtl.Company = InvcHead.Company
and InvcDtl.InvoiceNum = InvcHead.InvoiceNum no-lock,
each Customer Where Customer.Company = InvcDtl.Company
and Customer.CustNum = InvcHead.CustNum
and Customer.CustNum = InvcDtl.CustNum no-lock,
each ShipTo Where ShipTo.Company = InvcDtl.Company
and ShipTo.CustNum = InvcHead.CustNum
and ShipTo.CustNum = InvcDtl.CustNum
and ShipTo.ShipToNum = InvcDtl.ShipToNum
and ShipTo.Country = 'CANADA' no-lock,
each ShipVia Where ShipVia.Company = InvcDtl.Company
and ShipVia.ShipViaCode = InvcDtl.ShipViaCode no-lock,
each CustCnt Where CustCnt.Company = InvcDtl.Company
and CustCnt.conNum = InvcDtl.ShpConNum no-lock
BY ShipTo.Name
BY InvcHead.InvoiceNum
BY InvcDtl.InvoiceLine:

When I take the following statements out,
" each CustCnt Where CustCnt.Company = InvcDtl.Company
and CustCnt.conNum = InvcDtl.ShpConNum no-lock"
it exports correct number of records. Any ideas?
Thank you in advance.

Mike Tonoyan / MIS Manager
All American Products Co.
1135 Aviation Place
San Fernando, CA 91340
USA
Tel.: 818-361-0059 Ext.: 246
Fax 818-898-2236
E-mail: miket@...
Web: www.allamericanproducts.com


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]