Re: ACE code question
> The problem:
> I have a list of operator ids (as many as 5) per row returned by the
> select in some ace code. Some rows will have all of the op ids the same,
> some will have all different ids while still others will have a couple the
> same ... Let me illustrate.
>
> sample id opid1 opid2 opid3 opid4 opid5
> --------- ----- ----- ----- ----- -----
> 1 JT JT JT JT JT
> 2 PY JT JT JT JT
> 3 PY JT JM JT JM
> . . . . . .
> . . . . . .
> . . . . . .
I am assuming that these rows are returned by some fairly complex
select statement, e.g.
select sample_id, opid1, opid2, opid3, opid4, opid5
from <various tables>
where <various conditions>
> I need to output a list of opid's that contains no duplicates.
> Let me illustrate again.
>
> sample id analyst(s):
> --------- -----------
> 1 JT
> 2 PY, JT
> 3 PY, JT, JM
> . .
> . .
>
> For reasons that are too complicated to explain here I cannot use a UNIQUE
> clause in the select hence my post.
Well, you might try something like this:
database blah
end
select sample_id, opid1, opid2, opid3, opid4, opid5 ---+
from <various tables> +--- your select
where <various conditions> ---+
into temp t1 with no log ;
select sample_id, opid1 opid_n
from t1
union
select sample_id, opid2
from t1
union
select sample_id, opid3
from t1
union
select sample_id, opid4
from t1
union
select sample_id, opid5
from t1
into temp t2 with no log ; { union causes a "unique" to occur ..... }
select sample_id, opid_n
from t2
order by sample_id
end
format
before group of sample_id
print sample_id, 10 spaces ;
on every row
print opid_n, 2 spaces ; { ; means "no end-of-line" }
after group of sample_id
print "" { terminate the line }
end
Note that this won't put in the commas: PY, JT, JM. To get the commas
right you need have a variable called "first_time" or something, and
print the comma only if the operotr being printed isn't the first one
in the list.
My suggestion above won't necessarily preserve the order of the operators,
e.g. your row:
3 PY JT JM JT JM
might get printed as: 3 PY JT JM
or as: 3 JM JT PY
If it is important to you to preserve the order then I think there is a
way to do that (send me e-mail for details).
Hope this helps ....
Paul