Re: distinct problems

[Paul Hoyte <phoyte@SHARIDIONNE.COM>]

Can you let us know what version of SQR you are using?  EG.  ODBC,
Oracle, DB2, etc...

Paul Hoyte
Sharidionne Inc.
(248) 559-6868
Email: phoyte@sharidionne.com



-----Original Message-----
From: Discussion of SQR, Brio Software's database reporting language
[mailto:SQR-USERS@list.iex.net] On Behalf Of Alan Ljungberg
Sent: Wednesday, August 14, 2002 11:26 AM
To: SQR-USERS@list.iex.net
Subject: Re: distinct problems


Are any of the columns in the from clause LOB columns?
DISTINCT does not like them, at least in Oracle anyway.  I understand
there maybe some issues with DISTINCT and views, but I'm not sure about
that.

Have you tried using GROUP BY on all columns instead of DISTINCT?

-Alan.




                    "Aguirre,
                    Jose"                 To:     SQR-USERS@list.iex.net
                    <JAguir@LSUHSC        cc:
                    .EDU>                 Subject:     Re: distinct
problems
                    Sent by:           Expire On:
                    "Discussion of
                    SQR, Brio
                    Software's
                    database
                    reporting
                    language"
                    <SQR-USERS@lis
                    t.iex.net>


                    08/14/2002
                    07:57 AM
                    Please respond
                    to sqr-users





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
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_c
las_

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