Solutions For A Smarter Planet

Getting started with SQL

Say, you've done SQL before: then just skip this chapter and start using iQuery/NET. If you never used SQL or typed STRSQL into the IBM i green screen command line, this will give you an idea. If you've done SQL before and didn't like it, here's the tool that will change your mind.

SQL stands for Structured Query Language. To get a better understanding of what it's all about, take some time and a short lesson on SQL from wikipedia. In our opinion, it's one of the best things ever developed in computer technology.

First, let's get the terminology right:
SQL doesn't know about physical files and fields and logical files and all these funny things we are used to so much. SQL knows columns, rows, tables, indexes, views, collections (or schemas) and databases. There are some more entities, but they don't get us anywhere right now.

Here are some samples to get you going:
Say, you have a table CUSTOMERS, where the customer number is stored in a column named CUST#(yes, that's valid). To retrieve all information for customer number 12345, you would simply type

SELECT * FROM CUSTOMERS WHERE CUST#=12345

Now, this might work pretty sure if you do it from an interactive green screen session, since the library list is set correctly and table CUSTOMERS can be found by the SQL engine. When you sign on from a PC, that's not necessarily the case. To make sure you get the data, simply add the collection name of the table to the SQL command. Let's say, the table is stored in library MYDATA, the command looks like

SELECT * FROM MYDATA.CUSTOMERS WHERE CUST#=12345

Sounds good? Got the message? Continue with the next chapter ...