Re: distinct problems

[Greg Rabinovich <odecca25@YAHOO.COM>]

Couple of possible suggestions:

1) I always put 'begin-select distinct' all on one
line.

2) Verify that your select actually returns distinct
values in SQLPlus (if you work w/Oracle).  I try to
tune selects in database access tool prior to adding
them to the SQR.

Greg.


--- "Aguirre, Jose" <JAguir@LSUHSC.EDU> wrote:
> 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


__________________________________________________
Do You Yahoo!?
HotJobs - Search Thousands of New Jobs
http://www.hotjobs.com