What I showed was a means to effect replacement of a code[d-value] by
the corresponding\paired expanded value [e.g. descriptive text], just as
described in both Subject line and the opening post of the thread. No
"pivot" for reporting, just some simple data\value replacements; e.g.
'TX' becomes 'Texas'.
Somehow the OP pursued a different [if even tangential] course; one
which indeed, was more aligned with UNION of data. However my original
scenario is, as best I can infer, exactly what was asked of the OP. I
have no idea how things went awry.
Consider the following scenario, expanding the scenario from my
response to that given in\by the OP; in conjunction with a
system-supplied TABLE QCUSTCDT in QIWS and its data, thus providing a
working example:
<code>
create table qtemp/order_status
( order_status_code dec(1)
, order_status char(8)
)
; -- Table ORDER_STATUS created in QTEMP.
insert into qtemp/order_status values
(1, 'Active')
, (2, 'Closed')
, (3, 'Review')
; -- 3 rows inserted in ORDER00001 in QTEMP.
create table qtemp/state
( state_code char(2)
, state_name char(22)
)
; -- Table STATE created in QTEMP.
insert into qtemp/state values
('TX', 'Texas'), ('NV', 'Nevada'), ('GA', 'Georgia')
, ('VT', 'Vermont'), ('NY', 'New York'), ('CA', 'California')
, ('MN', 'Minnesota'), ('WY', 'Wyoming'), ('CO', 'Colorado')
; -- 9 rows inserted in STATE in QTEMP.
create view qtemp/cust as
(select
co.LSTNAM
, co.CITY
, ( select sn.state_name from state sn
where sn.state_code = co.state )
as cust_state_name
, ( select os.order_status from order_status os
where os.order_status_code = co.CHGCOD )
as cust_order_status
from qiws/qcustcdt co
)
; -- View CUST created in QTEMP.
select lstnam, city, state, chgcod as status
from qiws/qcustcdt
order by lstnam
; -- report from above SELECT follows; the "STATE" values
-- are USPS encoding\abbreviations, and the "CHGCOD"
-- values 1, 2, and 3 are digits instead of text\words:
....+....1....+....2....+....3.
LSTNAM CITY STATE STATUS
Abraham Isle MN 3
Alison Isle MN 3
Doe Sutter CA 2
Henning Dallas TX 3
Johnson Helen GA 2
Jones Clay NY 1
Lee Hector NY 2
Stevens Denver CO 1
Thomas Casper WY 2
Tyron Hector NY 1
Vine Broton VT 1
Williams Dallas TX 1
******** End of data ********
;
select * from qtemp/cust
order by lstnam
; -- report from above SELECT follows:
....+....1....+....2....+....3....+....4....+....5....+....
LSTNAM CITY CUST_STATE_NAME CUST_ORDER_STATUS
Abraham Isle Minnesota Review
Alison Isle Minnesota Review
Doe Sutter California Closed
Henning Dallas Texas Review
Johnson Helen Georgia Closed
Jones Clay New York Active
Lee Hector New York Closed
Stevens Denver Colorado Active
Thomas Casper Wyoming Closed
Tyron Hector New York Active
Vine Broton Vermont Active
Williams Dallas Texas Active
******** End of data ********
;
</code>
Regards, Chuck
On 09 Jul 2012 15:47, DeLong, Eric wrote:
Ok, so I found Chuck's post. The output of his technique would be
useful for stringing together COLUMNS, a bit like a pivot. However,
that would not produce the list results you say that you're seeking,
so I don't think Chuck's post is quite the solution for your task...
<<SNIP debasement of original request, as reflected in Subject>>
CRPence on Monday, June 25, 2012 9:06 PM wrote:
On 25 Jun 2012 17:24, Stone, Joel wrote:
I need to send the data elements, but they also want to expand
codes into meaningful code descriptions.
For example, if the customer-address field shows the state as
"TX", <<SNIP>> need to read the STATE table and expand the
"TX" code to "Texas".
<<SNIP>>
Replacing encoded values with expanded replacement values is
easily effected with a UDF, or when the correlated data is
already in a TABLE, then just as easily composed as another
SELECT. So perhaps just code the replacement values effect in a
VIEW:
create view cust_order_etl as
( select ...
,( select sn.state_name
from STATE sn
where sn.state_code = co.cust_state_code )
as cust_state_name
,( select os.order_status
from ORDER_STATUS os
where os.order_status_code = co.cust_order_status_code )
as cust_order_status
,...
from ... co ...
)
midrange.com
