Mastering SQL Queries for SAP Business One

Mastering SQL Queries for SAP Business One
eBook: $32.99
Formats: PDF, PacktLib, ePub and Mobi formats
save 15%!
Print + free eBook + free PacktLib access to the book: $87.98    Print cover: $54.99
save 37%!
Free Shipping!
UK, US, Europe and selected countries in Asia.
Also available on:
Table of Contents
Sample Chapters
  • Practical SAP query examples from an SAP Business One expert
  • Detailed steps to create and troubleshoot SQL queries for Alerts, Approvals, Formatted Searches, and Crystal Reports
  • Understand the importance and benefit of keeping SQL queries simple and easy to understand
  • Benefit from special tips and tricks related directly to SQL queries within SAP Business One

Book Details

Language : English
Paperback : 352 pages [ 235mm x 191mm ]
Release Date : May 2011
ISBN : 1849682364
ISBN 13 : 9781849682367
Author(s) : Gordon Du
Topics and Technologies : All Books, Big Data and Business Intelligence, Enterprise, SAP

Table of Contents

Chapter 1: SAP Business One Query Users and Query Basics
Chapter 2: Query Generator and Query Wizard
Chapter 3: Query Manager and Query Statements
Chapter 4: Query Examples
Chapter 5: Securities and Approvals
Chapter 6: SQL Query for Formatted Search (FMS)
Chapter 7: SQL Query for Reporting Tools
Chapter 8: SQL Query for a Stored Procedure
Chapter 9: More Complicated SQL Query Topics
  • Chapter 1: SAP Business One Query Users and Query Basics
    • Who can benefit from using SQL Queries in SAP Business One?
      • Consultant
      • Developer
      • SAP Business One end user
      • Non-SAP Business One users
    • SQL query and related terms
      • RDBMS
      • Table
      • Field
      • SQL
      • T-SQL
      • Subsets of SQL
      • Query
    • Data dictionary
      • SAP Business One—Database tables reference
      • Naming convention of tables for SAP Business One
        • Three letter words
        • "O" tables
        • "A" tables
        • Document header tables
        • Document line tables
        • Important table examples
    • Table links—the key for the right query
      • Primary key
      • Foreign key
      • Example of table links within SAP Business One
    • Base tables versus target tables
    • Keeping it simple—The key to build a good query
    • Summary
    • Chapter 2: Query Generator and Query Wizard
      • Query Generator
        • Query Generator overview
        • Left part of Query Generator form
        • Middle and right parts of Query Generator form
        • Executing a query from Query Generator form
      • Query Wizard
        • Query Wizard overview
        • Step 1—Splash screen
        • Step 2—Select tables for the report
        • Step 3—Select fields and sort orders
        • Step 4—Conditions and relations
        • Step 5—Query Wizard completion
      • What is the difference between Query Generator and Query Wizard?
      • Benefitting from built-in system queries
      • Summary
      • Chapter 3: Query Manager and Query Statements
        • Query Manager user interface
          • Display all existing queries
          • Creating and saving user queries
          • Deleting user queries
          • Managing query categories
        • Commonly used statements
          • SELECT—first statement to retrieve data
            • The scope of the value that can be retrieved
            • The numbers of columns to be included
            • Column name descriptions
            • Clauses can follow this statement
          • DISTINCT—duplicated records can be removed
          • TOP—number of lines returned by ranking
          • FROM—data resource can be assigned
            • A single table
            • A group of linked tables
            • Multiple tables separated by commas
          • JOIN—addition table or tables can be linked
            • Inner Join
            • Outer Join
          • WHERE—query conditions to be defined
          • BETWEEN—ranges to be defined from lower to higher end
          • IN/EXISTS—the value list that may satisfy the condition
          • LIKE—similar records can be found
          • GROUP BY—summarizing the data according to the list
          • HAVING—conditions to be defined in summary report
          • ORDER BY—report result can be by your preferred order
          • UNION/UNION ALL—to put two or more queries together
        • Some important functions to return values
          • ISNULL() predicate
          • SUM() function
          • MAX() function
          • MIN() function
          • COUNT() function
          • DATEDIFF() function
          • DATEADD() function
          • DATEPART() function
          • CAST()/CONVERT() function
          • CASE expressions
          • IF expressions
        • Summary
        • Chapter 4: Query Examples
          • Why three categories have been chosen
          • Defining variables for queries
            • Case 4-R1: Four variables in one query
            • Case 4-R2: Variables first or last
          • Date function—where the most problems emerge
            • Case 4-D1: Balance of production for a month
            • Case 4-D2: How to input a fixed date range
          • Orange arrow—an excellent tool for drill down
            • Case 4-O1: Make it simple
            • Case 4-O2: Sales order updating alert with drill down
          • Getting a subtotal from the query
            • Case 4-T1: By Union ALL
            • Case 2: By running total
          • Query for marketing documents
            • Case 4-M1: Overview of BP with selection of realized balance
            • Case 4-M2: Top five items sold
            • Case 4-M3: A filter by notes from OCRD
            • Case 4-M4: Adding sales employees' names to a query
            • Case 4-M5: A case for solution just from deduction
            • Case 4-M6: Goods Receipt PO within 10 days
            • Case 4-M7: Quantity purchased, received, and returned
            • Case 4-M8: Customized sales analysis report
            • Case 4-M9: Average sales per month
            • Case 4-M10: Credit Memo user check
            • Case 4-M11: Delivery date on sales order
            • Case 4-M12: Reducing from two to one line for the sales summary
            • Case 4-M13: Tax code summary
            • Case 4-M14: Sales by states
            • Case 4-M15: Many linked tables in one query
            • Case 4-M16: Sales Order with PO
          • Query for inventory transactions
            • Case 4-I1: Adding stock total to the query
            • Case 4-I2: Adding a total to the query bottom
            • Case 4-I3: Items not delivered within 15 days
            • Case 4-I4: Active item list
            • Case 4-I5: How to find stock taking details
            • Case 4-I6: Query on price updates
            • Case 4-I7: Planned quantity versus in stock
            • Case 4-I8: Adding to the production orders list from a sales order
            • Case 4-I9: Complete item list with or without transactions
          • Query for financial transactions
            • Case 4-F1: Top five customers
            • Case 4-F2: Incoming payment
            • Case 4-F3: Linking an incoming payment with an invoice
            • Case 4-F4: Listing both types of payment transactions
            • Case 4-F5: Incoming payment filtering
          • User query for alert
            • Case 4-A1: Creating a right alert without duplicated lines
            • Case 4-A2: Alert for invoice without base document
            • Case 4-A3: A/R Invoice past due alert
            • Case 4-A4: Special ship to alert for Sales Order
            • Case 4-A5: Open Sales Opportunity alert
            • User query alert guide
          • Miscellaneous query examples
            • Case 4-X1: Query related to service call
            • Case 4-X2: Concatenating two text columns
          • Summary
          • Chapter 5: Securities and Approvals
            • How to handle securities for query usage
              • Giving only a few users the capability to build a query report
              • Creating queries under different categories
              • Query Groups: a tool to assign user permissions
            • How to use query for approval procedures
              • Creating approval stages
              • Creating approval templates
                • Originator
                • Documents
                • Stages
                • Terms
            • Examples of user queries for approval
              • Case 1—Approval for adding delivery document
              • Case 2—"On Account" outgoing payment approval
              • Case 3—Approval for invoice to special customer groups
              • Case 4—Approval for over booking sales order
              • Case 5—None cash outgoing payment approval
            • Summary
            • Chapter 6: SQL Query for Formatted Search (FMS)
              • Formatted Search and User-Defined Values
              • How to work with User-Defined Values
                • Search in existing User-Defined Values according to the saved queries
                  • Where do the $ values come from?
                  • How to get the value you need from, and for, the FMS query
                  • Can you run FMS queries directly?
                  • What is the negative sign's function in FMS query?
                • Search in existing User-Defined Values only
              • A typical FMS query application: auto code creation
                • BP code auto generation
                • Item code auto generation
                • Special code auto generation
              • General FMS query examples
                • Case 1—Double quotes should be avoided
                • Case 2—Price value validation on line level
                • Case 3—Populating a UDF from OITM in a UDF on quotation
                • Case 4—Difference between two UDFs into another UDF
                • Case 5—Displaying warehouse name beside warehouse code
                • Case 6—Showing purchase order due date on sales order
                • Case 7—Auto populating the profit center code
                • Case 8—Calculation by three user-defined fields
                • Case 9—Open order reminder in new order
                • Case 10—Commitment checks for warehouse in stock
                • Case 11—Multiplying a field from OITM with a field on order line
                • Case 12—Multiplying two UDF values from two tables
                • Case 13—Last sales price for a customer
                • Case 14—Calling a UDF value in the BOM to Production Order
                • Case 15—Multiplying a UDF value with a system field value
                • Case 16—Eliminating the duplicate lines returned by FMS query
                • Case 17—Getting the sales rep code assigned to an activity form
                • Case 18—FMS query for User-Defined Table (UDT)
              • Summary
              • Chapter 7: SQL Query for Reporting Tools
                • Query Print Layout Designer (QPLD) and its usage
                  • Simple query report printing
                  • Query Print Layout Designer
                    • Working with a QPLD report
                    • Creating a QPLD report
                    • Editing a QPLD report
                    • Saving a QPLD report
                    • Printing a QPLD report
                    • Deleting a QPLD report
                    • Recreating the QPLD report
                • Direct query for Crystal Reports (Command)
                  • Working with Standard Report Wizard
                    • Creating a new database connection
                    • Adding a Command to a report
                    • Working with a Command
                  • Basic formatting for a Crystal Report
                • Summary
                • Chapter 8: SQL Query for a Stored Procedure
                  • Why Stored Procedure is included in this book
                  • SBO_SP_TransactionNotification overview
                  • How to work with SBO_SP_TransactionNotification
                  • Some example queries for this SP
                    • Case 1—Blocking an outgoing payment for a specific BP
                    • Case 2—Restricting outgoing payments above 20,000
                    • Case 3—Blocking goods receipt entry
                    • Case 4—Blocking a sales quotation if no value in row level UDF
                    • Case 5—Blocking invoice based on GL account and project
                    • Case 6—Blocking GRPO if quantity is more than PO quantity
                    • Case 7—Blocking, adding, or updating an order for duplicated BP ref #
                    • Case 8—Blocking sales documents based on dates
                    • Case 9—Validation service type A/R credit memo
                    • Case 10—Blocking goods issue for none super user
                    • Case 11—Blocking Goods Receipt PO if no based PO
                  • Summary
                  • Chapter 9: More Complicated SQL Query Topics
                    • The Case expression usage
                      • Case 9-C1—Displaying Transtype as code instead of a number
                      • Case 9-C2—Combining two queries with a Case expression
                      • Case 9-C3—Showing discount percentage for each interval
                      • Case 9-C4—Item wise subtotal in a goods receipt
                      • Case 9-C5—Updating UDF with different dates
                    • Working with a subquery
                      • Case 9-S1—Item groups not in use
                      • Case 9-S2—YTD sales for two years
                      • Case 9-S3—Checking only the similar records
                      • Case 9-S4—Showing the last A/P invoice document date for items
                    • Using PIVOT to simplify a cross tab style queries
                      • Case 9-P1—Monthly sales by geography
                      • Case 9-P2—Complete list of all items with/without sales
                    • Database query for Excel
                      • Creating a new data source
                        • New data source added within Excel
                        • New data source added from the control panel
                      • Query wizard for database query in Excel
                      • Microsoft Query window
                    • Avoiding pitfalls while building queries
                      • Creating a query before knowing the data table structure
                      • Complicating the logic instead of simplifying it
                      • Trying to do too many things in one query
                      • Relying on others' help only
                    • Summary
                    • Appendix
                      • Original transaction list by code
                      • Original transaction list by name
                      • Object codes and names

                      Gordon Du

                      Gordon Du studied System Engineering & Computer Science at the Nankai University in Tianjin, China. He has more than 25 years of experience with diversified information technology fields. His experience with SQL goes back to 1987. He has passion for helping others whenever he can. He is the top contributors on the SAP Business One forum ever since August 2008. Over 25% of all his points are related to solving SQL questions and problems posted by other forum members. Gordon has worked and trained in China, Singapore, USA and Canada. In China, he is the first person to successfully implement an international software package in a domestic company. He is also a Microsoft Certified System Administrator, Microsoft Certified System Engineer and Microsoft Certified Database Administrator.
                      Sorry, we don't have any reviews for this title yet.

                      Code Downloads

                      Download the code and support files for this book.

                      Submit Errata

                      Please let us know if you have found any errors not listed on this list by completing our errata submission form. Our editors will check them and add them to this list. Thank you.

                      Sample chapters

                      You can view our sample chapters and prefaces of this title on PacktLib or download sample chapters in PDF format.

                      Frequently bought together

                      Mastering SQL Queries for SAP Business One +    Oracle SOA Suite 11g R1 Developer's Guide =
                      50% Off
                      the second eBook
                      Price for both: $47.10

                      Buy both these recommended eBooks together and get 50% off the cheapest eBook.

                      What you will learn from this book

                      • Quickly get up and running with SAP's Query Generator and Query Wizard
                      • Develop best practices for creating SQL queries for use with SAP Business One
                      • Discover how "keeping it simple" will lead to a better SQL query
                      • Assign SQL query security within your company
                      • Learn how to get user queries ready for approval
                      • Optimize user queries for alerts through step-by-step instruction
                      • Build special queries for Formatted Search
                      • Create the core part of the query to utilize the SBO Transaction Notification stored procedure
                      • Master Query Print Layout design
                      • Improve your Crystal Reports with SQL queries
                      • Employ expert-level query methods
                      • Reduce your learning curve by following real-world examples and expert advice
                      • Achieve advanced SQL query building and troubleshooting skills

                      In Detail

                      SAP Business One is an integrated Enterprise Resource Planning (ERP) software which offers an ideal solution for growing small to midsized businesses. For such companies, retrieving the most relevant information from their business data can be key to standing out from the competition. SAP Business One is a rapidly growing software package and this book is timely in giving those businesses an advantage in the area of Business Intelligence. Having SQL query skills in- house is the most important and cost-effective move you can make in this growing field.

                      This practical guide will provide you with the skills to gain more specific business information from SAP Business One by using SQL queries. It will provide you with solutions for solving complicated report related problems, covering basic tools like the Query Generator and Query Wizard. More advanced content like using queries with Crystal Reports will also be delved into.

                      SQL query is one of the advanced tools available in SAP Business One which is easily learned and quickly utilized. By referring back to and applying the many examples in this book, you will be able to create and run correct, and therefore effective, SQL queries to help your business.

                      The book begins by teaching a clear definition of the SQL query, and covers the data dictionary and table links. Coverage will then jump to a higher level of complex SQL queries, discussing features like FMS. Along the way more advanced SQL Query topics will be covered, such as extending the scope of basic SQL queries for more complicated cases. You will ultimately gain in depth query knowledge to bring more Business Intelligence into SAP Business One.

                      Gain the skills to provide much needed business information within your company with SQL queries for SAP Business One


                      This is a practical guide providing comprehensive solutions for SQL query problems, and is full of concrete real-world examples to help you create and troubleshoot your SQL queries in SAP Business One.

                      Who this book is for

                      If you are a system administrator who uses SQL query as your tool of choice for solving specific problems throughout SAP Business One, then this book is for you. It may also be useful if you are a developer or consultant using this technology, and can benefit end users by improving your search for important business information. A rudimentary knowledge of SAP Business One and SQL Server is required to use this book efficiently. Examples covered are relevant to SBO 2007A users, for which the 8.8 release is mostly compatible. All SQL query examples within the book are verified under SQL Server 2005, so they are guaranteed to run under this release, in addition to SQL Server 2008.

                      Non-SAP Business One users can also gain knowledge from the many examples throughout the book. It is hard to find another book with so many SQL query examples.

                      Code Download and Errata
                      Packt Anytime, Anywhere
                      Register Books
                      Print Upgrades
                      eBook Downloads
                      Video Support
                      Contact Us
                      Awards Voting Nominations Previous Winners
                      Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
                      Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software