Re: distinct problems

[Prasanna Menta <pmenta@UMUC.EDU>]

Poonam,
Only other thing is that you need to look at the keys that you are
joining, if the join conditions are not met you might get duplicates,
post the full sql so that the user group can look into.
Good luck
Prasanna

----- Original Message -----
From: Poonam Auluck <poonam.auluck@AMS.COM>
Date: Wednesday, August 14, 2002 8:57 am
Subject: Re: distinct problems

> Thanks for the thought Prasanna, but I still was unable to get it
> to work.
> Any other ideas?
>
>
>
>
>                    Prasanna Menta
>                    <pmenta@UMUC.E       To:     SQR-
> USERS@list.iex.net                    DU>                  cc:
>                    Sent by:             Subject:     Re: distinct
> problems                    "Discussion of
>                    SQR, Brio
>                    Software's
>                    database
>                    reporting
>                    language"
>                    <SQR-USERS@lis
>                    t.iex.net>
>
>
>                    08/14/2002
>                    12:17 AM
>                    Please respond
>                    to sqr-users
>
>
>
>
>
>
> 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
> >
>