Re: distinct problems

["Aguirre, Jose" <JAguir@LSUHSC.EDU>]

Have you tried to select your fields in UPPERCASE?



José A. Aguirre



-----Original Message-----
From: Poonam Auluck [mailto:poonam.auluck@AMS.COM]
Sent: Tuesday, August 13, 2002 6:30 PM
To: SQR-USERS@list.iex.net
Subject: distinct problems


Hi All -

I'm having a bit of problems selecting distinct fields in SQR.  I'm hoping
someone can help me out.  I read some of the archived emails and figured
out why I may be having some problems, so I shortened my select, only the
find out that it doesn't work.  Here's what I'm trying to do:



begin-select
DISTINCT(b.fund_id||b.acty_id||b.user_dm4_id||b.bdob_id||d.actg_cat_id||d.ac
tg_clas_id)

          &acct_line
SUBSTR(B.ACTY_ID,7, INSTR(B.ACTY_ID, '&',1,3) - INSTR(B.ACTY_ID, '&', 1,2)
-1)        &LIMIT
b.fund_id                                                    &fund

     show &acct_line
     show &limit
     show &fund

from mf_rqt_actg_ln b, mf_fund d
where b..= d...
end-select

At first, I thought I was selecting too much, but I don't think that's the
problem.  The output of this statement does not give me the distinct values
of &acct_line.  It compiles and shows the data, but it gives me duplicates.
If I take the substr and b.fund_id out, the select statement bombs out.   I
tried putting the distint at the beginning of the select like,
"Begin-select distinct", but that didn't work.  I tried to make the select
even shorter to look like:

begin-select distinct
(b.fund_id||b.acty_id||b.user_dm4_id||b.bdob_id||d.actg_cat_id||d.actg_clas_
id)

          &acct_line
     show &acct_line
from mf_rqt_actg_ln b, mf_fund d
where b..= d...
end-select

But this didn't even work.  If I could get the second select to work, I'd
be happy...any ideas how to do this?

Thanks in advance,
Poonam