Mastering SQL Queries for SAP Business One

Gordon Du

• Editorial: Packt Publishing
• Año: 2011
• ISBN:
  9781849682367
• Edición: 1
• Categoría: Ciencia de la computación
• Categoría: Bases de datos

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

Approach

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.

Mastering SQL Queries for SAP Business One

---

Table of Contents

Mastering SQL Queries for SAP Business One

Credits

About the Author

Acknowledgement

About the Reviewers

www.PacktPub.com

Support files, eBooks, discount offers and more

Why Subscribe?

Free Access for Packt account holders

Instant Updates on New Packt Books

Preface

Business Intelligence (BI)

What this book covers

Section 1: SQL Query Basic

Section 2: SQL Query in Action

What you need for this book

Who this book is for

Conventions

Reader feedback

Customer support

Downloading the example code

Errata

Piracy

Questions

1. SQL Query Basic

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

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

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

A single value

A group of values

Return a single database table column

Return a group of database table columns

Return complete database table columns

Used in a subquery

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

Left Outer Join

Right Outer Join

Full Outer Join

Self-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

2. SQL Query in Action

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 f...