Re: Query question
bickel@zonnet.nl (Bickel) wrote:
>Jonathan Leffler <jleffler@earthlink.net> wrote in message
>news:<7NEsb.11218> >
> >
> > SELECT DISTINCT numid AS numid, numid AS subid FROM table
> > UNION
> > SELECT DISTINCT numid AS numid, subid AS subid FROM table
> > INTO TEMP t;> >
> > SELECT subid FROM t ORDER BY numid, subid; -- IDS 9.40 only!> >
> > Everywhere else, you'd have to select numid as well as subid and
> > ignore the value.
>
>Sorry, but this doesn't work. i get a result of only 11 rows where i
>expect 12. Also on 9.40.
>As you can see here....numid 2 and 3 show 4 times, instead of 3 time.
>Also ignoring the numid (or working with 9.40) are no options.
>So i guess it can only be done with spl..?
> numid subid
> 1 1
> 1 3
> 1 5
> 2 1
> 2 2
> 2 4
> 2 5
> 3 1
> 3 3
> 3 8
> 3 9
I was able to get the results you requested, but it isn't pretty. The test
that I ran is as follows:
create temp table testit
(numid integer,
subid integer) with no log;
insert into testit values (1, 2);
insert into testit values (2, 1);
insert into testit values (3, 1);
insert into testit values (1, 3);
insert into testit values (2, 4);
insert into testit values (3, 8);
insert into testit values (1, 5);
insert into testit values (2, 5);
insert into testit values (3, 9);
create temp table worktable
(col1 integer,
col2 integer) with no log;
insert into worktable
select unique numid * 10000 + numid, numid from testit;
insert into worktable
select numid * 10000 + subid * 100, subid from testit;
select col1 c1, col2 c2 from worktable order by col1 into temp showtable;
select c2 from showtable;
drop table worktable;
drop table showtable;
drop table testit;
With the results as in your original request:
1
2
3
5
2
1
4
5
3
1
8
9
I don't have 9.40 available for testing, so had to go another way. I used
two temp tables; the first holds a modified value to allow proper sorting,
the second column is the item that is to print. (It is why I asked for the
size of the actual values in an earlier email to you. I am guessing at a
size that will work for the sort value (e.g. 10000 and 100).) The second
temp table is simply built for the final output. I used an 'order by' when
populating the second temp table, but... I would not trust that the sort
order will always be maintained. If this is a one-time operation for you,
you might want to give it a shot (and cross your fingers that the sort order
is maintained), otherwise the SPL may be the way to go.
As I said, this isn't pretty, but it did work. I'm not clear if you have
9.40 yourself, but if so, you could try building the first temp table as I
did and select from it with the 'order by' as Jonathan suggested. I think
you are going to have to go just a little further than he suggested for the
sort. Using his example but selecting both fields from temp table t, the
sort order did not appear as in your original request. I did, however, get
all twelve results.
I hope there is something here that you can use.
--
June Hunt
_________________________________________________________________
MSN Shopping upgraded for the holidays! Snappier product search...
http://shopping.msn.com
sending to informix-list