The Anatomy of a SQL Statement: Rename your columns and tables (Part 3)

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

The fields ItemCode and ItemName will appear as the column names for the data when the query is executed. Many times the field names (the ones from the actual table) do not accurately express what the field means. To rename the field to something else you can specify the new name right after the field name.

SELECT ItemCode AS Item, ItemName AS Description FROM OITM

The ?AS? keyword is not required, but aids in readability, so this syntax is also valid:

SELECT ItemCode Item, ItemName Description FROM OITM

If you attempt to use a special character (such as ?#?) you may encounter issues. Enclose the new name in quotes and SBO will accept the name.

As your queries become more complicated, you will have the desire to rename the files used within the query to make them easier to reference to. Additionally, when you have two files that have fields with the same field name, you must specify the file name and the field name every time you refer to the field in the query. For example, there is an ItemCode field in the Item Master (OITM) and the Order Detail (RDR1). Since both fields have the same name, you will have to specify the file name then the field name to tell the system which field you are referring to. If two files were being used in the previous query, then I would need to specify the file name too. The query below produces the same results as the previous examples:

SELECT OITM.ItemCode AS Item, OITM.ItemName AS Description FROM OITM

If you had a large number of fields that you were selecting from one or more files it may become cumbersome to type ?OITM? over and over again. You can rename it to something shorter. When you use the query tools from SBO, the system will rename the files for you. SBO renames the files to ?Tn? (i.e., T0 for table one, T1 for table two). You can rename the files by specifying the new name
right after the file name (in this case the file name is OITM for the item master file).

SELECT it.ItemCode AS Item, it.ItemName AS Description FROM OITM it

As you can see above, the shorter file name will make it a little easier to add lots of fields.