Now that we've created a library and saw some basic AS/400 features let's have a look at how we can create and manipulate tables using SQL.
You must create the DEMO library and define it has your current library to proceed with this chapter.
Type the following Start SQL command on the system prompt:
STRSQL
This way you begin a SQL session in AS/400. This application accepts most of the common SQL syntax (CREATE,INSERT, DELETE, SELECT, DROP, etc).
Creating a table with SQL
From the SQL command line we're going to create the CLIENTS table and then insert some rows. For this tutorial let's assume that all phone numbers have, at most, 9 digits.
Press F3 to leave the table view and type the following commands. Type:
CREATE LIBRARY DEMO/CLIENTS( id_cli numeric(10) PRIMARY KEY, name_cli char(50), birth_cli date, phone_cli numeric(9) )Note: You can place the code in separate lines (like in the example) or in a continued fashion (without indentation) as long as you don't press the Enter key before you finish the command.
A message will appear saying that the table was created.
If you want to see more empty command lines just press Page Down or move the mouse scroll down. Now Type:
SELECT * FROM CLIENTSThis way you can see that the table was created and it has no information in it. So let's insert some new rows.
Press F3 to leave the table view and type the following commands.
INSERT INTO CLIENTS (ID_CLI, NAME_CLI, BIRTH_CLI, PHONE_CLI) VALUES (1, 'Mary', '12/09/1967', '999999999')
INSERT INTO CLIENTS (ID_CLI, NAME_CLI, BIRTH_CLI, PHONE_CLI) VALUES (2, 'Tom', '09/01/1979', '123456789')Do the SELECT command again to check if the rows were correctly inserted.
Press F3 to leave the SQL command line. A set of options will appear. On the field you should write the option number. Try typing a number outside the option range (for instance 5) and then press Enter. As one would expect, an error message appears on the bottom of the screen. Try pressing F3 or inserting another value. It won't work because the screen is blocked. You will probably find some blocking errors and when you do press Esc the screen will go back to normal.
Now select the option 1 to exit the application, saving the session.
SQL Built-In Functions
There are some very useful SQL built-in functions on the AS/400 system.
Basic Functions
If you know SQL you may already be familiar with these functions.
| Function | Description |
|---|---|
| MAX | Returns the maximum value from a set of pre-defined values. |
| MIN | Returns the minimum value from a set of pre-defined values. |
| AVG | Returns the average value of a set of pre-defined values. |
| SUM | Returns the sum of a set of pre-defined values. |
| COUNT | Returns the number of elements in a set of pre-defined values. |
Example (returns the maximum ID_CLI value from all the rows in the table CLIENTS):
SELECT MAX(ID_CLI) FROM CLIENTSNumeric Functions
| Function | Description |
|---|---|
| ABS(N) | Returns the absolute value of N. |
| COS(A) / ACOS(A) | Basic trigonometric functions. |
| SIN(A) / ASIN(A) | Basic trigonometric functions. |
| TAN(A) / ATAN(A) | Basic trigonometric functions. |
| CEILING(N) | Returns the rounding of N to the unit above. |
| FLOOR(N) | Returns the rounding of N to the unit below. |
| DEGREES(R) | Converts a value in radians to degrees. |
| LN(N) | Returns the natural logarithm of N. |
| LOG10(N) | Returns the base 10 logarithm of N. |
String Functions
| Function | Description |
|---|---|
| CHAR(N) | Returns the the string representation of the number N. |
| CHAR_LENGTH(S) | Returns the length of a string. |
| CONCAT(S1, S2) | Concatenates S1 with S2. |
| SUBSTR(S, I, L) | Returns a substring of S, starting at index I of lenght L. |
| LOWER(S) | Returns the lowercase representation of S. |
| UPPER(S) | Returns the uppercase representation of S. |
| TRIM(S) | Removes spaces from the beggining and and of S. |
| RTRIM(S) | Removes spaces at the begging (right) of S. |
| LTRIM(S) | Removes spaces at the end (left) of S. |
Date and Time Functions
| Function | Description |
|---|---|
|
CURDATE() CURTIME() |
Returns the system's current date/time. |
|
DATE(D) DATE(T) |
Converts a string representation of a date/time into into a date/time value. |
| DAY(D) | Returns the day(1-31) from the date D. |
| WEEK(D) | Returns the week (1-54) from the date D. |
| MONTH(D) | Returns the month (1-12) from the date D. |
| YEAR(D) | Returns the year from the date D. |
| DAYOFWEEK(D) | Returns the week day (1-7) from the date D where 1 is Sunday. |
| DAYOFWEEK_ISO(D) | Returns the week day (1-7) from the date D where 1 is Monday. |
| DAYOFYEAR(D) | Returns the number of the day, in a year (1-366). |
| HOUR(T) | Returns the hour (0-24) from the time T. |
| MINUTE(T) | Returns the minute from the time T. |
| SECOND(T) | Returns the second from the time T. |
| MICROSECOND(T) | Returns the microsecond from the time |