The Anatomy of a SQL Statement ? SQL Statement Breakdown (Part 2)

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

In the previous discussion I introduced a simple SQL query that selected all of the customer numbers and names from the SAP Customer/Vendor/Lead Master file. The query actually selects all three types (customers, vendors, and leads) because SBO uses one table/file to store this information. How you will know which type you are looking at is controlled by a type field within the table (CardType). We will add that field so that you can see the differences between the different types of records.

Some more advanced users will note that there is no semicolon at the end of this statement. Many database management systems require a semicolon to indicate the end of the statement, but Microsoft SQL server does not. If fact, if you attempt to use a semicolon with this query within SBO, you will receive an error (and not very informative one at that) at the bottom of your screen.

SELECT CardCode, CardName, CardType FROM OCRD

This basic query will work in SBO as is. As long as you are using the SELECT keyword, you do not have to fear that you are going to change or corrupt the SBO data. The SELECT statement only reads data, it does not change data. It may be a good idea to make several smaller queries, like this one, as starter templates for other queries. The statement can appear on one line or multiple lines, it does not matter. The following statement produces the same result:

SELECT CardCode,Card NameFROM OCRD

Note that the CardCode (Customer Number) is listed before the CardName (Customer Name). The order in which you list your fields is important because that is the same order the system will display them in. Imagine a spreadsheet, the customer number will appear in column one and the customer name in column two.

Let?s take a look at the components of the statement:

• SELECT = Tells the system that you are about to select values from one or more tables (files) in SBO.
• CardCode, CardName, CardType = Are the fields or values that you want to display in your report. List each field name separated by a comma (remember, no comma after the last field).
• FROM = in a simple query, the FROM keyword only appears once for each SELECT. It indicates from what tables or files are the source for the fields that you listed above. It is possible to have a query inside another query, but that is another discussion.
• OCRD = The official name for the SAP Business One Customer/Vendor/Lead table.

The same concept can be applied to other tables in SBO. Let?s look at a basic query for the Item Master:

SELECT ItemCode, ItemName FROM OITM

Note that some of the elements changed, but not all of them. The SELECT and FROM do not change, but the field names (values) and the table/file name did. Apply this same formatting to other tables and you will be on your way to creating more advanced queries.