I’m an RDi fan. I make no secret about that. Even so I occasionally find things that I need to do that just seem easier or faster to do with PDM in the green screen. I don’t really mind that. I nearly always have a green screen session sitting just next to my RDi workbench anyway. But when I can find a way to do a function as easily in RDi, I prefer to do that so that I don’t need to switch modes as much.

This tip is the first in a series on things that I find some

The SQL Procedures Language (SQL PL) has an effective error-handling mechanism–condition handlers. When a statement returns a certain SQL state or a certain type of exception, the condition handler takes control. However, determining which SQL state to test for can be problematic. Here are two ways.

Let’s start with a simple stored procedure.

Create or replace procedure CreatePlants
begin
   create table plants
   ( ID dec(3),  Location  varchar(16),
   primary key (ID));

   label on table plants is 'Plant master';

   insert into plants values
   (  1,  'Lost Angeles'),
   (  2,  'New Yolk'),
   (  3,  'Last Vegas');
end

What could go wrong? Well, the

I have a table with a long list of column names and I want to build a SELECT statement from the catalog metadata. If I supply schema (library) and table (physical file) names, is there a way to generate a SELECT statement?

—Four Hundred Guru Reader

Thanks to dynamic compound statements and global variables, the answer is yes, it’s fairly easy to construct a SQL statement for a table or view as shown below:

/* Build Select Statement and stuff it in QGPL.TEMPSQL variable */
BEGIN
    -- Fill in your table/schema name
    DECLARE @TABLE_NAME   VARCHAR(128) NOT NULL 
                          DEFAULT 'SYSCOLUMNS';