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

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

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",