Ted Holt
-
SQL Finds a Delta
December 7, 2011 Hey, Ted
… Read moreIs there a way using SQL to calculate the amount a numeric column changes from one row to the next?
–Ken
Yes. Here’s how it’s done.
First, you need a table to use for an example.
create table mydata ( Name char(8), Qty dec(3,0)) insert into mydata values ('Bob', 25), ('Bill', 85), ('Sue', 17), ('Bob', 28), ('Bob', 32), ('Bill', 105), ('Sue', 45)To pull this off, the data must be stored in the order in which it is to be retrieved. If necessary, create a temporary table to hold the sorted data.
declare global temporary table SortedData as (select name,
-
VALUES: A Truly Useless DDS Keyword
October 26, 2011 Hey, Ted
… Read moreWe had a recent crisis in our shop. When we dug to the root of the problem, we found that one of our files had an invalid value in one of the fields. I don’t understand why this was the case, because the programmer who wrote the DDS used the VALUES keyword on the field in question. Does the VALUES keyword not prevent invalid data from entering the database?
–Paul
No, Paul. One would think it does, but use of the VALUES keyword only applies to the workstation controller. That is, only the green-screen interface is restricted by the VALUES
-
Asserting the Ostensibly Unassertable
May 25, 2011 Hey, Ted
… Read moreAssertions are great for validating conditions that can be tested with IF, DOW, SELECT, etc., but what about other types of conditions? For instance, can I use an assertion to test that a variable contains valid decimal data?
–A Nice Lady
(I’m sorry. A nice lady asked me this question in person and I forgot her name.)
Yes, it is possible to check for conditions that one cannot test with conditional OPCODES such as IF, but you must use a different method.
When something goes amiss, the system loads a five-digit, zoned-decimal status code with a non-zero value. You can
-
Updating Through a Join with SQL, Take Two
April 27, 2011 Hey, Ted
… Read moreI am studying your tip Updating through a Join with SQL, and something escapes me. I do not understand the redundancy in the first code example. Do all updates that involve joins have to have redundant code?
–Ron
I know it’s confusing, Ron. I can go into more detail for you.
This is the example to which you refer:
update customer as c set region = (select newregion from fixes where oldregion = c.region) where exists (select * from fixes where oldregion = c.region)You’re right that there is some redundancy. Both the SET and WHERE clauses of the
-
SQL Bar Charts
April 13, 2011 Hey, Ted
… Read moreA while back I had a problem that appears to be quite common, but just rare enough that it’s a little challenging when it hits you. Often in our roles as database administrators we need to determine various figures, such as how many key values in a table are used up in case we need to extend the field. Or sometimes we just to provide a statistical representation of data. What I needed was to produce a histogram using SQL.
Let’s say you have an array of sample statistics and you want to break them into groups of an arbitrary
-
No Truncate Table? No Problem!
March 23, 2011 Hey, Ted
… Read moreI am working on a project using DB2 for i, but my experience is with other database management systems. I can’t find the SQL TRUNCATE TABLE statement. Does DB2 cover this functionality some other way?
–Brad
Even in 7.1, the latest release, IBM does not implement the TRUNCATE TABLE statement. However, since this statement is included in other DB2 products, as well as in Informix, I expect we’ll see it eventually as part of our world.
For the benefit of readers who are not familiar with TRUNCATE TABLE, this statement removes all rows of a table (records of a physical
-
Circumventing Integer Division
March 16, 2011 Hey, Ted
… Read moreMy computer seems to have forgotten how to divide. When I divide one value by another, using SQL, the answer is always exactly one or zero. What gives?
–RPG Professional
This took a little while, but we finally found his problem. First, the background.
Assume a database file with three fields: customer ID, the number of orders from that customer, and the number of shipments to the customer.
select CustID, Orders, Shipments from Summary CUSTID ORDERS SHIPMENTS 1 75 25 2 10 16 3 20 0 4 8 8Now let’s add a ratio to the query to gauge order
-
Avoid Division by Zero in Query/400
February 23, 2011 Hey, Ted
… Read moreI want to build a temporary database file to send to a user, but records with a zero divisor do not get written to the output file. Can I prevent Query for i from dividing by zero?
–Bill
Yes, Bill, and it’s not difficult. Here’s how it’s done. But first, some test data.
create table mylib/divbyzero (Extended dec(5,2), Qty dec(3,0)) insert into mylib/divbyzero values (500, 2), (0, 12), (6, 0), (0, 0), (100, 3)
Now I have five rows of data to play with, two of which have zero in the Qty field. Here’s my first attempt to create an
-
OUTQ vs. PRTDEV
February 16, 2011 Hey, Ted
… Read moreSeveral CL commands–Create User Profile (CRTUSRPRF) is a good example–have two parameters that control the destination of printed output. Where is the need for both print device and output queue? Can you explain the role of these parameters in determining the destination of output?
-Tim
I know of no need to use both print device and output queue (“outq”) parameters. I have always managed fine with outq’s only. The System/38, one of the predecessors of the AS/400, used outq’s exclusively. I understand that IBM added the print device option to support software that had been developed for the AS/400’s other
-
Cleaning Up RSE Detritus
January 19, 2011 Hey, Ted
… Read moreNote: The code accompanying this article is available for download here.
Hey, Ted:
While doing some year-end house (disk) cleaning, I ran across the temporary source files that CRTSQLRPGI uses when run from RSE (WDSC or RDx), which are named EVFTEMPF01 and EVFTEMPF02. I knew about these files but never paid much attention to them. Today I discovered that there were 20 files (10 of each type) with total size of 45MB.
I wrote the attached clean-up program and a command interface. I scheduled the command to run every weekend.
I didn’t clear the file at the end so