JACKIE's Forum Run SQL Script — Part II

Document Sample
JACKIE's Forum Run SQL Script — Part II Powered By Docstoc
					JACKIE's Forum
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       jjansen@ca.ibm.com.
                                                                 ask to see all the values in

30                                                                                    TORONTO USERS GROUP for iSeries – January 2006