Run SQL Script — Part II
his is part two in the use of the “Run keys then you will need to define the join
SQL Script” component within yourself. SQL Assist doesn’t use column
iSeries Navigator. Today we are names or definitions to generate suggestions
going to concentrate on the SQL for a join. Jackie Jansen
Assist feature. I am not going through every
screen and every option available. My plan The number one trick in the JOIN window a column. By default SQL Assist will show
is to give you enough of an introduction so is to use the standard windows control key you the first 100 values although you can
that you can follow through on your own. I to select two or more tables. This sounds override this parameter.
have highlighted a couple of the “gotcha’s” obvious but when you only see two tables
or tips that aren’t intuitively obvious. As listed using the control key to select both of The remaining statements INSERT,
a reminder to start “Run SQL Script” them doesn’t seem to come naturally. Once UPDATE and DELETE all work basically
navigate to the database area within iSeries you have chosen your tables the screens are the same way.
Navigator, underneath “Databases” select fairly self explanatory.
your system name. “Run SQL One of the biggest tips to leave
Script” is one of the tasks now you with, is what to do when the
enabled either via the “Database generated SQL statement isn’t
tasks” panel or via a right mouse exactly what you want. Speaking
click. from experience, putting your
cursor under the statement and
The SQL Assist feature is basically pressing F4 doesn’t import your
an enhanced PC version of our statement back into SQL Assist.
green screen prompt (cmd key Highlighting the statement
4) functionality. With SQL doesn’t do what you would want
Assist you choose the tables and either. Having more faith in our
columns you want to operate developers than this indicated, I
on, you get assistance building called them and found out the
expressions such as SUM and “trick” in the current release. If
CONCAT, and you can ask for you highlight your entire SQL
prompts showing all the possible statement in the “RUN SQL
values in a column. Script” window, excluding the
SQL Assist doesn’t use tabs to guide you semicolon, and then either press F4 or
To access SQL Assist from the “Run SQL through the process. Once you have selected choose Edit/SQL Assist, your statement
Scripts” window either use F4 or look under the tables you must select your next option will be imported as you would expect. The
the Edit menu. Let’s start by choosing the from the left panel. In this case you would trick here is to leave out the semicolon.
most common SQL statement, SELECT, select the “Result Columns” option. If you According to the developers this is a short
on the right panel. When we do this, the don’t select any columns the default is to term usability issue.
left panel changes to show the main clauses return them all.
within the SELECT statement. If you Stay tuned for the exciting conclusion to
select more than one table you will see a When you select a column the right panel “Run SQL Script Part 3” in next month’s
JOIN button. If you have defined foreign will then display the column name and in column.
and primary keys for your tables, when the same row a field with three dots. If you
you click on the JOIN button the system click on the three dots you are taken to the Jackie Jansen is a Senior
will attempt to create the join for you. If expression builder where you can build an Consulting IT Specialist. She
you haven’t specified primary and foreign expression using logic and functions such currently works in the IBM
as SUM and CONCAT. Americas Advanced Technical
Support Solutions Centre. Jackie
is a frequent speaker at iSeries
An additional handy technical conferences and users
function is the capability, group meetings. Contact her at
when you are filtering, to firstname.lastname@example.org.
ask to see all the values in
30 TORONTO USERS GROUP for iSeries – January 2006