Table Of Contentwww.it-ebooks.info
Mastering SQL Queries for SAP
Business One
Utilize the power of SQL queries to bring Business
Intelligence to your small to medium-sized business
Gordon Du
o
D
professional expertise distilled
P U B L I S H I N G
BIRMINGHAM - MUMBAI
www.it-ebooks.info
Mastering SQL Queries for SAP Business One
Copyright © 2011 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval
system, or transmitted in any form or by any means, without the prior written
permission of the publisher, except in the case of brief quotations embedded in
critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy
of the information presented. However, the information contained in this book is
sold without warranty, either express or implied. Neither the author, nor Packt
Publishing, and its dealers and distributors will be held liable for any damages
caused or alleged to be caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the
companies and products mentioned in this book by the appropriate use of capitals.
However, Packt Publishing cannot guarantee the accuracy of this information.
First published: May 2011
Production Reference: 2190511
Published by Packt Publishing Ltd.
32 Lincoln Road
Olton
Birmingham, B27 6PA, UK.
ISBN 978-1-849682-36-7
www.packtpub.com
Cover Image by David Guettirrez ([email protected])
www.it-ebooks.info
Credits
Author Project Coordinator
Gordon Du Vishal Bodwani
Reviewers Proofreader
Zal Parchem Aaron Nash
Muddassar Imran
Wolfgang Niefert Indexer
Monica Ajmera
Acquisition Editor
Stephanie Moss Graphics
Geetanjali Sawant
Development Editor
Susmita Panda Production Coordinator
Arvindkumar Gupta
Technical Editor
Pallavi Kachare Cover Work
Arvindkumar Gupta
Copy Editor
Laxmi Subramanian
www.it-ebooks.info
About the Author
Gordon Du studied System Engineering and Computer Science at the Nankai
University in Tianjin, China. He has over 28 years of experience in diversified
information technology fields. His experience with SQL goes back to 1987.
Gordon was the first person in China to successfully implement an international
software package for a Chinese investment-only company in 1995.
Gordon has been the top contributor on the SAP Business One forum since August
2008. He has been awarded 45,000 points as of January 16, 2011. This is the highest
lifetime points recorded by SAP Community Network for a non-SAP employee
since 2004. Over 25 percent of Gordon's points are related to solving SQL query
questions and problems posted by other forum members. Here are some links
demonstrating this.
His SCN profile link for reference:
http://forums.sdn.sap.com/profile.jspa?userID=4130357
His first blog on SCN:
http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/17099
The congratulations and thanks thread to his 35,000 points from forum users:
http://forums.sdn.sap.com/thread.jspa?threadID=1718298&tstart=0
Gordon has worked and trained extensively in China, Singapore, USA, and Canada.
He also holds the following certificates: a Microsoft Certified System Administrator,
Microsoft Certified System Engineer, and a Microsoft Certified Database
Administrator.
Gordon is planning to write a series of books related to SAP Business One in
response to the demand from many SAP Business One users and consultants.
www.it-ebooks.info
Acknowledgement
Special thanks goes to my wife, Lisa, whose love and encouragement helped me
to complete this book. Thank you, my son Mason and his fiancée, Ava, for your
support and reviews. Thank you to Zal Parchem, who not only inspired me from the
beginning, but also provided a professional technical review. Thanks to the staff at
Packt Publishing and all my friends who supported me in writing this book. Among
all my friends, MS Christine Malone has given me the greatest support by her
excellent final proof reading and editing.
Last but not least, thank you to everyone who posted on the SAP Business One
forum providing me the opportunity to solve so many SQL query problems.
www.it-ebooks.info
About the Reviewers
Zal Parchem has been in the business world working in the Information Systems
area for over 31 years. For the past 10 years, he has been working as an Independent
Consultant, concentrating on helping small to midsize companies install and
customize their ERP (Enterprise Resource Planning) systems. He has restricted his
work efforts to the SAP Business One (SAP B1) ERP system and is actively involved
in many SAP Forums, SAP Blogging areas, and the Wiki sections for SAP B1. He
works with SAP, SAP B1 Partners, and Customers around the world. For the past
three years, Zal Parchem has been using SQL extensively in SAP B1 for reporting and
customization purposes in SAP B1.
He has been employed in a variety of industries, with company sizes ranging from
six personnel to over 250,000 employees. Having retired from The Home Depot in
2001, he started his career as an Independent Consultant.
He has also written a "guerrilla handbook" titled Project Methodology and
Documentation for SAP Business One Implementations. He actively reviews all SAP B1
books available in print; his reviews can be seen online at Amazon.
I would like to thank Gordon Du for this opportunity to do the
technical aspect of his work in SQL. Gordon is the most active
participant in the SAP B1 forums where he helps dozens of people
daily. I am honored to know Gordon as a Fellow Forum Friend (FFF)
and to have been asked to work with PacktPub on this publication,
which is certainly going to extend Gordon's ability to help even more
individuals to be productive and effective in the use of SQL inside
SAP Business One.
www.it-ebooks.info
Muddassar Imran is a passionate Web Developer. He is the Web Developer at
Page and Moy, UK. He is a First Class Graduate from DMU (De Montfort University,
Leicester UK) and was awarded the best final year project award from BCS (British
Computer Society). Moreover, he is a Professional Member of BCS. He is enthusiastic
about modern expert system and database administration. He has aesthetic skills in
writing complex SQL Queries, ASP.NET, VB.NET and AJAX.
Imran was born in Gujranwala, a small city famous for its peaceful atmosphere.
He attended Suffa Secondary School at 10th Grade and achieved the highest scores
in his city. He got admission at the Government College University, Lahore for
further education in Computer Science. He went to Malaysia for higher education
and finished a Higher Diploma in Computing Studies from KDU College Malaysia.
Then he went back to Pakistan and worked with Wateen Telecom Pvt. Ltd. and
Telenor for two years, primarily developing web-based applications and working on
automation projects.
Muddassar then traveled to the United Kingdom to attend the De Montfort
University. In 2010, he received his Bachelors in Computer Science. While studying
at DMU, he was working with Venus Packaging Solutions Ltd. As a VB.NET
Developer until March 2011 and then joined Page & Moy in April 2011.
Further, Imran writes on his blog (www.blog.mudasar.co.uk) and his personal
website is www.mudasar.com. For relaxation, he likes to workout at the gym and travel.
After passing B-Tech in Computer Science and Engineering Wolfgang Niefert
joined ITSL Technologies as a .Net developer in June 2007. He worked on ERSys
(ERP Systems) and internal CRM product of ITSL Technologies. He then moved to
SAP as TechnoFunctional consultant, and also provided training on TB1000, TB1100,
TB1200 books. After that he joined Sapphire systems in June 2010 as SAP SDK
Support consultant.
He is an active contributor to the SAP sdn community. He got gold contribution
status in the year 2010 in sdn community.
I would like to thank my family and my team for helping and
supporting me.
www.it-ebooks.info
www.PacktPub.com
Support files, eBooks, discount offers and more
You might want to visit www.PacktPub.com for support files and downloads related to
your book.
Did you know that Packt offers eBook versions of every book published, with PDF and ePub
files available? You can upgrade to the eBook version at www.PacktPub.com and as a print
book customer, you are entitled to a discount on the eBook copy. Get in touch with us at
[email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a
range of free newsletters and receive exclusive discounts and offers on Packt books and eBooks.
http://PacktLib.PacktPub.com
Do you need instant solutions to your IT questions? PacktLib is Packt's online digital book
library. Here, you can access, read and search across Packt's entire library of books.
Why Subscribe?
• Fully searchable across every book published by Packt
• Copy and paste, print and bookmark content
• On demand and accessible via web browser
Free Access for Packt account holders
If you have an account with Packt at www.PacktPub.com, you can use this to access
PacktLib today and view nine entirely free books. Simply use your login credentials for
immediate access.
Instant Updates on New Packt Books
Get notified! Find out when new books are published by following
@PacktEnterprise on Twitter, or the Packt Enterprise Facebook page.
www.it-ebooks.info
Table of Contents
Preface 1
Section 1 – SQL Query Basic
Chapter 1: SAP Business One Query Users and Query Basics 9
Who can benefit from using SQL Queries in SAP Business One? 9
Consultant 10
Developer 10
SAP Business One end user 10
Non-SAP Business One users 11
SQL query and related terms 11
RDBMS 11
Table 11
Field 12
SQL 12
T-SQL 13
Subsets of SQL 13
Query 13
Data dictionary 14
SAP Business One—Database tables reference 14
Naming convention of tables for SAP Business One 15
Three letter words 15
"O" tables 16
"A" tables 16
Document header tables 16
Document line tables 16
Important table examples 17
Table links—the key for the right query 17
Primary key 17
Foreign key 18
www.it-ebooks.info
