AS400 Dummy File And Pivot in SQL

IBM's equivalent to the DUAL table in Oracle is SYSDUMMY1. Some implementations of SQL allow values to be SELECTed without naming a table but, other implementations like DB2 and Oracle do not. IBM supplies the SYSDUMMY1 table for that purpose and Oracle, the DUAL table.

SELECTing a Row of Values From SYSDUMMY1

SELECT 1 ONE, 2 TWO, 3 THREE FROM sysibm.sysdummy1;

    ONE               TWO               THREE
      1                 2                   3

SELECTing a Column of Values From SYSDUMMY1

SELECT 'one' FROM sysibm.sysdummy1 UNION 
SELECT 'two' FROM sysibm.sysdummy1 UNION 
SELECT 'three' FROM sysibm.sysdummy1;

Constant Value
    one
    two
    three

Pivot in SQL

After learning about pivot tables in Excel, I found that SQL can also take horizontal data and place it in columns. This is often useful where tables have been denormalized. Retrieving the data with the UNION simplifies the coding that processes the data whether the code is RPG or Java. 
create table states_afflictions(key1 CHAR(10), prop1 VARCHAR(20), 
key2 CHAR(10), prop2 VARCHAR(20), 
key3 CHAR(10), prop3 VARCHAR(20));

insert into states_afflictions values('Texas', 'tornadoes', 'Oklahoma', 'tornadoes', 'Arkansas', 'floods'); 
insert into states_afflictions values('Idaho', 'potato famine', 'Texas', 'rodeos', 'Lousiana', 'hurricanes');
insert into states_afflictions values('Arkansas', 'cotton', 'Louisiana', 'mosquitoes', 'Texas', 'hurricanes');
insert into states_afflictions values('Texas', 'heat', 'Texas', 'drought', 'Florida', 'hurricanes');

select 'from 1st kv pair', key1, prop1 from states_afflictions where key1 = 'Texas' UNION ALL 
select 'from 2nd kv pair', key2, prop2 from states_afflictions where key2 = 'Texas' UNION ALL
select 'from 3rd kv pair', key3, prop3 from states_afflictions where key3 = 'Texas';

Results 
from 1st kv pair|Texas|tornadoes
from 1st kv pair|Texas|heat
from 2nd kv pair|Texas|rodeos
from 2nd kv pair|Texas|drought
from 3rd kv pair|Texas|hurricanes

Basic RPG Demo Executing the SQL


Basic Java Demo Executing the SQL

This is a standard Maven + Spring Boot project.


The Resulting Output From the Java 
keyval = Texas, drought
keyval = Texas, heat
keyval = Texas, hurricanes
keyval = Texas, rodeos
keyval = Texas, tornadoes

Blog Archive