Depending on the answers to the questions I posed in my
prior post, there may be much simpler ways to accomplish
whatever is desired [what ultimately needs to be achieved]
from the original posting. If what is required is not a
specific\limited case of a generic scenario wherein the
columns must be transposed into rows, perhaps...
Given the request is to effect a /pivot query/ [or, it seems
instead to have been given the moniker /unpivot query/] whereby
the columns [from a selected row] should be returned as rows
[in the order defined by the ordinal position of the column in
the table], then this should be one way to accomplish that:
<code>
with
TRQS_COLS as
(select trqs01 ,trqs02
, <<SNIP trqs03 to trqs99>>
, trqs00
from FILE1
where TCLS = :c2Class and TVEN = :c2Vendor
and TSTY = :c2Style and TCLR = :c2Color
and TSIZ = :c2Size and TRID = :c2rowId
)
select 1,trqs01 from TRQS_COLS union all
select 2,trqs02 from TRQS_COLS union all
<<SNIP select of 3,trqs03 to 99,trqs99>> union all
select 100,trqs00 from TRQS_COLS
order by 1
;
</code>
I have seen this done instead, using a CASE for the
inclusion of each value from the ordinal column position,
where that position is the respective row number within
the set of the original row having been duplicated for the
count of the columns. A recursive query can generate the
duplicate copies of the row, to include a column to identify
a count [which correlates to the ordinal column position from
which to obtain the data] for each row. One way to do that:
<code>
with
TRQS_ROW as
(select trqs01 ,trqs02
, <<SNIP trqs03 to trqs99>>
, trqs00
from FILE1
where TCLS = :c2Class and TVEN = :c2Vendor
and TSTY = :c2Style and TCLR = :c2Color
and TSIZ = :c2Size and TRID = :c2rowId
)
,
TRQS_SET as /* column list is not required here.? */
(select 1 as TCOUNT ,A.*
from TRQS_ROW A
union all
select TCOUNT+1 as TCOUNT ,B.*
from TRQS_SET B
where TCOUNT<100
)
select
CASE TCOUNT
WHEN 1 THEN trqs01
WHEN 2 THEN trqs02
<<SNIP when 3 to when 99>>
WHEN 100 THEN trqs00
END AS TRQSxx
from TRQS_SET
order by TCOUNT
;
</code>
Another way [that may be only possible since IBM i OS 6.1]
is a temporary TABLE of rows generated on a VALUES clause
from the columns of the selected row(s). Something like:
http://it.toolbox.com/blogs/db2luw/unpivot-query-12798
The selection of the row and transposition of the columns
into rows could be encapsulated in a USER DEFINED FUNCTION.
The example below may need the SQL TABLE FUNCTION to have a
column added [e.g. before TRQSxx column] as in first example.
That column then named in an ORDER BY clause of the later
SELECT FROM that TABLE FUNCTION. An INT column could be
added to the TABLE FUNCTION, that would be used to ensure
that the elements of the array contain the value from the
respective field, from 1 to 100. Each SELECT for UNION ALL
would identify the relative field position as literal number
to identify its relative row just as with the first example:
<code>
create function TRQS_VALS
(c2Class &ClassType ,c2Vendor &VendorType
,c2Style &StyleType ,c2Color &ColorType
,c2Size &SizeType ,c2RowId &RowIdType )
/* set &xxxType to respective type for :c2xxxHostVar */
RETURNS TABLE
( TRQSxx DECIMAL (7, 0) )
LANGUAGE SQL NOT DETERMINISTIC
READS SQL DATA CALLED ON NULL INPUT
NOT FENCED DISALLOW PARALLEL
CARDINALITY 100
SET OPTION DBGVIEW = *SOURCE
BEGIN
RETURN
with TRQS_COLS as
(select trqs01 ,trqs02
, <<SNIP trqs03 to trqs99>>
, trqs00
from FILE1
where TCLS = c2Class and TVEN = c2Vendor
and TSTY = c2Style and TCLR = c2Color
and TSIZ = c2Size and TRID = c2rowId
) /* like first example, but variables vs HostVar */
select trqs01 from TRQS_COLS union all
select trqs02 from TRQS_COLS union all
<<SNIP select of trqs03 to trqs99>> union all
select trqs00 from TRQS_COLS
;
END
</code>
With the above UDTF, the SELECT for the program would change
to request the one column of that TABLE FUNCTION, to provide
[its rows as] values for the elements of the array:
<code>
exec sql
select TRQSxx
from TABLE(TRQS_VALS(:c2Class ,:c2Vendor ,:c2Style
,:c2Color ,:c2Size ,:rowId) )
into :allRequests
-- order by INT-column if added to the UDTF
;
</code>
Regards, Chuck
Mark Keller wrote:
I've got a series of 100 fields that I only need 1 value from.
I'm doing a SELECT INTO, so a dynamic statement won't work.
If it did, my statement would be pretty simple.
The program compiles OK, but I get SQL0030 during execution.
"Number of host variables less than result values."
Sorry if the formatting of the code below is funky...
Any thoughts on this? What am I doing wrong here? TIA and
please don't embarrass me too bad! :) Mark
The Data structure:
D allRequests...
D DS
D theRequests 7P 0 dim(100)
The SQL statement:
exec sql
select trqs01, trqs02, trqs03, trqs04, trqs05,
trqs06, trqs07, trqs08, trqs09, trqs10,
trqs11, trqs12, trqs13, trqs14, trqs15,
trqs16, trqs17, trqs18, trqs19, trqs20,
trqs21, trqs22, trqs23, trqs24, trqs25,
trqs26, trqs27, trqs28, trqs29, trqs30,
trqs31, trqs32, trqs33, trqs34, trqs35,
trqs36, trqs37, trqs38, trqs39, trqs40,
trqs41, trqs42, trqs43, trqs44, trqs45,
trqs46, trqs47, trqs48, trqs49, trqs50,
trqs51, trqs52, trqs53, trqs54, trqs55,
trqs56, trqs57, trqs58, trqs59, trqs60,
trqs61, trqs62, trqs63, trqs64, trqs65,
trqs66, trqs67, trqs68, trqs69, trqs70,
trqs71, trqs72, trqs73, trqs74, trqs75,
trqs76, trqs77, trqs78, trqs79, trqs80,
trqs81, trqs82, trqs83, trqs74, trqs85,
trqs86, trqs87, trqs88, trqs89, trqs90,
trqs91, trqs92, trqs93, trqs94, trqs95,
trqs96, trqs97, trqs98, trqs99, trqs00
into :allRequests
from FILE1
where TCLS = :c2Class and
TVEN = :c2Vendor and
TSTY = :c2Style and
TCLR = :c2Color and
TSIZ = :c2Size and
TRID = :rowId ;
midrange.com
