Re: distinct problems

[Poonam Auluck <poonam.auluck@AMS.COM>]

Hey Everyone,

Thanks to all that replied.  The problem with my distinct select was that I
was pulling a field that was unique per row.  I got it fixed now.

Thanks for all the help!
Poonam Auluck



                    Alan Ljungberg
                    <Alan.Ljungberg@CNPL.ENB       To:     
SQR-USERS@list.iex.net
                    RIDGE.COM>                     cc:
                    Sent by: "Discussion of        Subject:     Re: distinct 
problems
                    SQR, Brio Software's
                    database reporting
                    language"
                    <SQR-USERS@list.iex.net>


                    08/14/2002 11:26 AM
                    Please respond to
                    sqr-users






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.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