Vantage 6.1 - Business-Activity-Query Question

Yahoo Archive
1
General linking suggestions - not sure if they will help but you do have a pretty complicated string of links:
1. Start with smallest table possible and link towards larger tables (so company first)
2. Try not to have branched links when possible
3. Use every field possible in which ever index you are using - use primary index when possible
4. Use fields in linking in the order they occur within the index

All of these will help. Another technique is, if you have it, to use Report Builder and create a simple report in it then the SQL it creates by doing Ctrl-Alt-P and then F12. Not the same syntax exactly as BAQ but it can give you a good head start and (when using the rules above) it will do a decent job of optimizing the SQL part.

-Todd C.

-----Original Message-----
From: Jim Feetterer [mailto:jfeetterer@...]
Sent: Friday, September 09, 2005 4:49 PM
To: vantage@yahoogroups.com
Subject: [Vantage] Vantage 6.1 - Business-Activity-Query Question


Help,



I am relatively new to SQL/Business-Activity-Query and need to create a
spreadsheet containing all quotes received on the current day.



I created the following SQL which ran in 2 - 3 minutes but then the user
requested the contact name be added. I added the additional logic
(highlighted in red below) to get the contact-name and now it runs for
45 minutes (ouch).



Does any one have suggestions on how to improve the run time or
streamline this query?



Thanks,



Jim Feetterer

Morgan Bronze



________________________________________________________________________
_____________________________







for each QuoteHed Where QuoteHed.Company = cur-comp

and QuoteHed.QuoteClosed <> yes no-lock,

each QuoteDtl Where QuoteDtl.Company = QuoteHed.Company

and QuoteHed.DateQuoted = 09/08/05

and QuoteDtl.QuoteNum = QuoteHed.QuoteNum no-lock,

each QuoteQty Where QuoteQty.Company = QuoteHed.Company

and QuoteQty.QuoteNum = QuoteHed.QuoteNum

and QuoteDtl.QuoteLine = QuoteQty.quoteline no-lock,

each Customer Where Customer.Company = QuoteHed.Company

and Customer.CustNum = QuoteHed.CustNum no-lock,





each QuoteCnt Where QuoteCnt.Company = cur-comp no-lock,

each Company Where Company.Company = QuoteCnt.Company no-lock,



each CustCnt Where CustCnt.Company = cur-comp

and Customer.CustNum = CustCnt.CustNum

and QuoteHed.QuoteNum = QuoteCnt.QuoteNum

and QuoteCnt.ConNum = CustCnt.ConNum

no-lock,





each SalesRep Where SalesRep.Company = cur-comp

and Customer.SalesRepCode = SalesRep.SalesRepCode

and SalesRep.RepReportsTo = 'SSmith'



no-lock



BY Customer.Name:

________________________________________________________________________
_________________________







[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




SPONSORED LINKS
Membership <http://groups.yahoo.com/gads?t=ms&k=Membership+database+software&w1=Membership+database+software&w2=Database+mortgage+software&w3=Pda+database+software&w4=Database+management+software&w5=Database+marketing+software&w6=Online+database+software&c=6&s=190&.sig=zumlzfMugtt6gdHbPv4SEw> database software Database <http://groups.yahoo.com/gads?t=ms&k=Database+mortgage+software&w1=Membership+database+software&w2=Database+mortgage+software&w3=Pda+database+software&w4=Database+management+software&w5=Database+marketing+software&w6=Online+database+software&c=6&s=190&.sig=gjZta7wG6hZqdrJXzK2Teg> mortgage software Pda <http://groups.yahoo.com/gads?t=ms&k=Pda+database+software&w1=Membership+database+software&w2=Database+mortgage+software&w3=Pda+database+software&w4=Database+management+software&w5=Database+marketing+software&w6=Online+database+software&c=6&s=190&.sig=3Hn54KU1wFHab_pC8mcmQw> database software
Database <http://groups.yahoo.com/gads?t=ms&k=Database+management+software&w1=Membership+database+software&w2=Database+mortgage+software&w3=Pda+database+software&w4=Database+management+software&w5=Database+marketing+software&w6=Online+database+software&c=6&s=190&.sig=36Lz5dDq6L3dzupiJwAffg> management software Database <http://groups.yahoo.com/gads?t=ms&k=Database+marketing+software&w1=Membership+database+software&w2=Database+mortgage+software&w3=Pda+database+software&w4=Database+management+software&w5=Database+marketing+software&w6=Online+database+software&c=6&s=190&.sig=k5h2EoDyGba8G2MtsW5WSQ> marketing software Online <http://groups.yahoo.com/gads?t=ms&k=Online+database+software&w1=Membership+database+software&w2=Database+mortgage+software&w3=Pda+database+software&w4=Database+management+software&w5=Database+marketing+software&w6=Online+database+software&c=6&s=190&.sig=-puLLcDG0gUTIJTiT0fjEw> database software

_____

YAHOO! GROUPS LINKS



* Visit your group " vantage <http://groups.yahoo.com/group/vantage> " on the web.


* To unsubscribe from this group, send an email to:
vantage-unsubscribe@yahoogroups.com <mailto:vantage-unsubscribe@yahoogroups.com?subject=Unsubscribe>


* Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service <http://docs.yahoo.com/info/terms/> .


_____




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



I am relatively new to SQL/Business-Activity-Query and need to create a
spreadsheet containing all quotes received on the current day.



I created the following SQL which ran in 2 - 3 minutes but then the user
requested the contact name be added. I added the additional logic
(highlighted in red below) to get the contact-name and now it runs for
45 minutes (ouch).



Does any one have suggestions on how to improve the run time or
streamline this query?



Thanks,



Jim Feetterer

Morgan Bronze



________________________________________________________________________
_____________________________







for each QuoteHed Where QuoteHed.Company = cur-comp

and QuoteHed.QuoteClosed <> yes no-lock,

each QuoteDtl Where QuoteDtl.Company = QuoteHed.Company

and QuoteHed.DateQuoted = 09/08/05

and QuoteDtl.QuoteNum = QuoteHed.QuoteNum no-lock,

each QuoteQty Where QuoteQty.Company = QuoteHed.Company

and QuoteQty.QuoteNum = QuoteHed.QuoteNum

and QuoteDtl.QuoteLine = QuoteQty.quoteline no-lock,

each Customer Where Customer.Company = QuoteHed.Company

and Customer.CustNum = QuoteHed.CustNum no-lock,





each QuoteCnt Where QuoteCnt.Company = cur-comp no-lock,

each Company Where Company.Company = QuoteCnt.Company no-lock,



each CustCnt Where CustCnt.Company = cur-comp

and Customer.CustNum = CustCnt.CustNum

and QuoteHed.QuoteNum = QuoteCnt.QuoteNum

and QuoteCnt.ConNum = CustCnt.ConNum

no-lock,





each SalesRep Where SalesRep.Company = cur-comp

and Customer.SalesRepCode = SalesRep.SalesRepCode

and SalesRep.RepReportsTo = 'SSmith'



no-lock



BY Customer.Name:

________________________________________________________________________
_________________________







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