Re: distinct problems

[Prasanna Menta <pmenta@UMUC.EDU>]

Poonam,
Try to do begin-select distinct of the required columns and then get
the acct line by concatenating the columns, this will avoid duplicates.
Hope this helps
Prasanna

----- Original Message -----
From: Poonam Auluck <poonam.auluck@AMS.COM>
Date: Tuesday, August 13, 2002 7:30 pm
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
> figuredout 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.actg_
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
>