Splitting rows without temp tables

This isn't a question as such - I asked that earlier and got prompt and useful help, thanks. But it seemed only fair to share the final solution I built with your help and some other digging, in case someone else trolling the archives finds it useful. (Also, I'm happy to take suggestions on further improving my answer even though this one seems to work; I am definitely a novice in the ways of IDS.) My original problem was that I needed to split each row of a table into a number of other rows, where the number of other rows would be based on some value in the original row. My knee-jerk response was to build a temporary table and do a join with it, but I'm not allowed; I'm supposed to be pulling data from another companies proprietary database, and I have read-only access, so no temp tables allowed. So I faked a temp table with a subquery - with the help of this list - and came up with the following (model; the full version has lots more columns and lines, but is effectively the same): select pkid, newcol from routegroup inner join table(multiset( select 3 as tkqsig, 'beginning' as newcol from table( set{1} ) union all select 3 as tkqsig, 'middle' as newcol from table( set{1} ) unionall select 3 as tkqsig, 'end' as newcol from table( set{1} ) unionall select 4 as tkqsig, 'first quarter' as newcol from table( set{1} ) union all select 4 as tkqsig, 'second quarter' as newcol from table( set{1} ) union all select 4 as tkqsig, 'third quarter' as newcol from table( set{1} ) union all select 4 as tkqsig, 'last quarter' as newcol from table( set{1} ) )) as repeat (tkqsig, newcol) on routegroup.tkqsig = repeat.tkqsig So, A, 3 B, 4 Becomes: A, 'beginning' A, 'middle' A, 'end' B, 'first quarter' B, 'second... That the "as tkqsig" column aliases were necessary surprised me, as did having to select constants from an imaginary made-up table... but its an easy fix. As I said, any suggestions on how to do this more efficiently, or even just more gracefully, would be more than welcome. Thanks again for your help, both directly and in the archives, - rob.

Dev wrote: > This isn't a question as such - I asked that earlier and got prompt and > useful help, thanks. But it seemed only fair to share the final > solution I built with your help and some other digging, in case someone > else trolling the archives finds it useful. (Also, I'm happy to take > suggestions on further improving my answer even though this one seems > to work; I am definitely a novice in the ways of IDS.) > > My original problem was that I needed to split each row of a table into > a number of other rows, where the number of other rows would be based > on some value in the original row. My knee-jerk response was to build > a temporary table and do a join with it, but I'm not allowed; I'm > supposed to be pulling data from another companies proprietary > database, and I have read-only access, so no temp tables allowed. So I > faked a temp table with a subquery - with the help of this list - and > came up with the following (model; the full version has lots more > columns and lines, but is effectively the same): > > select pkid, newcol > from routegroup inner join > table(multiset( > select 3 as tkqsig, 'beginning' as newcol from table( set{1} ) > union all > select 3 as tkqsig, 'middle' as newcol from table( set{1} ) union> all > select 3 as tkqsig, 'end' as newcol from table( set{1} ) union> all > select 4 as tkqsig, 'first quarter' as newcol from table( set{1} > ) > union all > select 4 as tkqsig, 'second quarter' as newcol from table( set{1} > ) > union all > select 4 as tkqsig, 'third quarter' as newcol from table( set{1} > ) > union all > select 4 as tkqsig, 'last quarter' as newcol from table( set{1} ) > )) as repeat (tkqsig, newcol) > on routegroup.tkqsig = repeat.tkqsig> > So, > > A, 3 > B, 4 > > Becomes: > > A, 'beginning' > A, 'middle' > A, 'end' > B, 'first quarter' > B, 'second... > > That the "as tkqsig" column aliases were necessary surprised me, as did > having to select constants from an imaginary made-up table... but its > an easy fix. As I said, any suggestions on how to do this more > efficiently, or even just more gracefully, would be more than welcome. > > Thanks again for your help, both directly and in the archives, > > - rob. > Could you use table(set()) straight up? Something like: ..INNER JOIN TABLE(SET{(3, 'beginning'), (3, 'middle'),....}) AS repeat (tkqsig, newcol) I don't know the capabilities of set.. Cheers Serge PS: Can I propose features too? INNER JOIN TABLE(VALUES(2, 'beginning'), ....) AS repeat (tkqsig, newcol) Values is very powerful for that exact purpose: tables on the fly. -- Serge Rielau DB2 Solutions Development DB2 UDB for Linux, Unix, Windows IBM Toronto Lab