FHG Volume: 16 Archives - Page 3 of 8

Generate SELECT For All Columns

September 13, 2016 Hey, Mike

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';

…

Determine The State Of The Error

September 13, 2016 Ted Holt

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

…

How Do You Do That With RDi? Part 1: Copy A Source Member

September 13, 2016 Susan Gantner

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

…

Getting Started With IBM i, .Net, and XMLSERVICE Remote Commands

August 30, 2016 Richard Schoen

Running CL commands and submitting batch jobs are great ways to use existing program functionality from a .Net desktop, web, or web service application. In this article we’ll focus on the XMLSERVICE remote command functionality. You’ll see just how easy it is to use XMLSERVICE to run programs or submit batch jobs on your IBM i systems.

In this installment we’ll begin to tour the XMLSERVICEi .Net application code and samples, starting with the remote command call example. If you haven’t installed and set up the XMLSERVICE application code and created an Apache web server instance yet, please check out

…

Message Data Data Structures

August 30, 2016 Hey, Ted

Sending Escape Messages from RPG is a great article. Your program defines the message data parameter as 80 bytes of character data, but the IBM Knowledge Center defines MSGDTA as char(*) with notes saying it can be up to 32,767 bytes. I would like a variable longer than 80 bytes, but instead of coding 100 today, 120 next project, and so on, how could I code it to take full advantage of the API?

–Glenn

You’re right that the API can handle up to 32K of data, but you need only define MsgDta as large as you need it to

…

Easy Printing from CL, Take Two

August 30, 2016 Hey, Ted

When I read your statement that CL cannot write to printer files, I immediately thought, “It can’t? Then what have I been doing for the last couple of decades?” I looked through the article and saw that it didn’t mention my most used method, and I wondered how it was missed.

–Tom Liotta

Tom takes advantage of one of the best features of ILE, namely the ability to bind routines written in different languages to form one program. The non-CL procedure that he included is the printf function, which is well-known to C programmers. Even though printf produces stream

…

Overlaid Packed Data In Data Structures

August 16, 2016 Jon Paris

Note: The code accompanying this article is available for download here.

Sometimes things that should be simple give us difficulty. Jon Paris recently helped a Four Hundred Guru reader to solve a problem with overlaid packed-decimal data in data structures. We’re sharing their conversation for the benefit of others.

Hi, Jon:

My question has to do with overlaid packed-decimal data in a data structure. I store a date in CYMD format in a seven-digit, packed-decimal field. I am trying to extract the two-digit year from that.

Dcl-Ds cymdDate;
   currentDate  packed(7);
   currentYear  packed(2) overlay(currentDate:2);
End-Ds;

When I set

…

How To Insert With A Common Table Expression

August 16, 2016 Hey, Ted

I have to merge some new item numbers into our item master file. The problem I’ve run into is that there is more than one record for some items. None of the examples I found on the Web work for me. How do I write an SQL statement to copy all records from one file to another avoiding duplicates on part number?

–Trent

This is a new wrinkle on a challenge about which I have written before, namely the need to select only one row (record) to represent a group. (See the Related Stories below.) The wrinkle is that not

…

Assorted DB2 for i Questions

August 16, 2016 Michael Sansoterra

Shown below are assorted questions related to DB2 for i. If you have a question you’d like Four Hundred Guru to try to answer, be sure to visit the IT Jungle Contact page. Note that some of these questions were modified from their original form for clarity and simplicity.

Adding an attribute to an element based XML

Q: The FHG tips on composing XML documents from DB2 data (Part 1 and Part 2) do not describe how to add a custom attribute to element based XML. Consider the following query:

SELECT XMLAGG(
       XMLROW(NAME as "Name",
              Color as "Color",

…

Using Lateral Correlation To Define Expressions In DB2 For i

August 2, 2016 Michael Sansoterra

The SQL implementation in DB2 for i is second to none. However, one irritating thing common to various SQL dialects is the need to repeat expressions in query. As SQL matured over the years, techniques such as nested table and common table expressions became available to, among other things, reduce repetitive expressions. This tip illustrates the use of the LATERAL correlation as another way to avoid repetition.

The Problem

Say you’re writing a report for a grocery wholesaler, where markup on food items is small and discounts are even smaller. Looking for orders that were not priced correctly, you’re tasked

…

Previous Articles Next Articles

Search for:

Content archive

The Four Hundred
Four Hundred Stuff
Four Hundred Guru

Content archive

Recent Posts

Subscribe

Pages

Search