SQL Class

For those who would like to try their hand at doing some queries for formatted searches, alerts, or reports, but do not know what the common table names are, here is a short list. Check back and I will update the list from time to time.

Master Files:

OITM = Item Master
OCRD = Customer, Vendor, and Lead Master
CRD1 = Customer Master Address
OCRG = Customer Master Group Code

Follow these steps:

Using the example from the previous lesson, lets look at how we can make a SQL query more user friendly.

SELECT ItemCode, ItemName FROM OITM

Previous Discussion: The Anatomy of a SQL Statement ? The Basics

The Basics

Writing your own query for SAP (or any system) can be very satisfying and cost effective, if you are a user of SBO. Once you understand the basics, you will be able to direct your computer to give you exactly what information you are looking for from the system.

There are two tools for creating custom queries in SAP Business One, both of which will walk you through making your own query:

• From the drop-down menus at the top of the screen, go to: Tools -> Queries -> Query Manager
• Click on the Query Manager icon located on the icon bar at the top of the screen.

 A List of Common Table Names and Queries:

 Note: This is not a comprehensive description of the capabilities of either of these SQL Functions.