DISTINCT 
Author Message
 DISTINCT

This may have been asked 15 times already. I'm using Crystal Reports 7 and I
my query is returning duplicates of some records. This is screwing up my
running totals. Adding DISTINCT the query I'm passing makes no difference as
I guess Crystal doesn't recognize it. Is there a work around for this? I
don't see anything in the help.



Sat, 01 Mar 2003 02:42:48 GMT  
 DISTINCT

Here's the solution I got off the Seagate KB.  I changed the query I'm
passing to read as follows.  Originally it was everything after the 0=1
UNION. Now my report won't run. I get the Starting Background Processing
Failed stuff.  Do I have to actually change anything in the report itself as
well?  Thanks.

SELECT
 LAND_INSTRUMENT.LAND_INSTRUMENT_NUMBER,
 BAND.BAND_NAME,
 LAND_INSTRUMENT.INSTRUMENT_HECTARE,
 LAND_INSTRUMENT.LAND_INSTRUMENT_NUMBER,
 LAND_INSTRUMENT.INSTRUMENT_TYPE_NUMBER,
 LAND_INSTRUMENT.EFFECTIVE_DATE,
 IT.INSTRUMENT_TYPE_EDESC
FROM
 NETLANDS.LAND_INSTRUMENT,
 NETLANDS.INSTRUMENT_TYPE IT,
 NETLANDS.INSTRUMENT_PURPOSE,
 NETLANDS.LAND_INSTRUMENT_PARTY LIP_OWNER,
 COMMON.RESERVE, COMMON.BAND, NETLANDS.LAND_INSTRUMENT_RESERVE,
 COMMON.PERSON, NETLANDS.LAND_INSTRUMENT_PARTY LIP_LAND_OFFICER,
 NETLANDS.LAND_INSTRUMENT_PARTY LIP_BAND
WHERE 0=1 UNION
SELECT
 DISTINCT LAND_INSTRUMENT.LAND_INSTRUMENT_NUMBER,
 BAND.BAND_NAME, LAND_INSTRUMENT.INSTRUMENT_HECTARE,
 LAND_INSTRUMENT.LAND_INSTRUMENT_NUMBER,
 LAND_INSTRUMENT.INSTRUMENT_TYPE_NUMBER,
 LAND_INSTRUMENT.EFFECTIVE_DATE,
 IT.INSTRUMENT_TYPE_EDESC
FROM
 NETLANDS.LAND_INSTRUMENT,
 NETLANDS.INSTRUMENT_TYPE IT,
 NETLANDS.INSTRUMENT_PURPOSE,
 NETLANDS.LAND_INSTRUMENT_PARTY LIP_OWNER,
 COMMON.RESERVE,
 COMMON.BAND,
 NETLANDS.LAND_INSTRUMENT_RESERVE,
 COMMON.PERSON, NETLANDS.LAND_INSTRUMENT_PARTY LIP_LAND_OFFICER,
 NETLANDS.LAND_INSTRUMENT_PARTY LIP_BAND
WHERE
 LAND_INSTRUMENT.LAND_INSTRUMENT_NUMBER =
LIP_LAND_OFFICER.LAND_INSTRUMENT_NUMBER
 AND LAND_INSTRUMENT.LAND_INSTRUMENT_NUMBER =
LIP_OWNER.LAND_INSTRUMENT_NUMBER
 AND LAND_INSTRUMENT.LAND_INSTRUMENT_NUMBER =
LIP_BAND.LAND_INSTRUMENT_NUMBER(+)
 AND LIP_LAND_OFFICER.RPN = PERSON.PERSON_RPN AND LIP_BAND.RPN =
BAND.BAND_RPN(+)
 AND LAND_INSTRUMENT.LAND_INSTRUMENT_NUMBER =
LAND_INSTRUMENT_RESERVE.LAND_INSTRUMENT_NUMBER(+)
 AND LAND_INSTRUMENT_RESERVE.RESERVE_NUMBER = RESERVE.RESERVE_NUMBER(+)
 AND LIP_LAND_OFFICER.RPROLE_NUMBER = 8
 AND LIP_OWNER.RPROLE_NUMBER IN(17,18)
 AND LIP_BAND.RPROLE_NUMBER(+) = 19
 AND LAND_INSTRUMENT.INSTRUMENT_TYPE_NUMBER = IT.INSTRUMENT_TYPE_NUMBER
 AND LAND_INSTRUMENT.INSTRUMENT_PURPOSE_NUMBER =
INSTRUMENT_PURPOSE.INSTRUMENT_PURPOSE_NUMBER(+)
 AND LIP_OWNER.RPN = 6561 AND LAND_INSTRUMENT.INSTRUMENT_TYPE_NUMBER = 22



Sat, 01 Mar 2003 02:54:03 GMT  
 DISTINCT
Jason,
    Hate to say it, but consider upgrading to CR 8 - it supports 'distinct'.
Otherwise redo the reports to use a ttx (data defnition file) and a
disconnected ADO recordset.  You can use the distinct sql keyword when
querying your db to return want you want.  Realistically, for the cost of an
upgrade, go for the former over the latter - major time savings.

HTH,
--
Dan McGarry
HCI Systems, Inc.
http://www.hcisystems.com


Quote:

> This may have been asked 15 times already. I'm using Crystal Reports 7 and
I
> my query is returning duplicates of some records. This is screwing up my
> running totals. Adding DISTINCT the query I'm passing makes no difference
as
> I guess Crystal doesn't recognize it. Is there a work around for this? I
> don't see anything in the help.



Sat, 01 Mar 2003 03:02:21 GMT  
 DISTINCT

The solution on the Seagate KB doesn't work with Oracle.  Next we're going
to try a view that uses distinct. This will probably work and is a way
around upgrading to v8 system-wide


Quote:
> Jason,
>     Hate to say it, but consider upgrading to CR 8 - it supports
'distinct'.
> Otherwise redo the reports to use a ttx (data defnition file) and a
> disconnected ADO recordset.  You can use the distinct sql keyword when
> querying your db to return want you want.  Realistically, for the cost of
an
> upgrade, go for the former over the latter - major time savings.

> HTH,
> --
> Dan McGarry
> HCI Systems, Inc.
> http://www.hcisystems.com



> > This may have been asked 15 times already. I'm using Crystal Reports 7
and
> I
> > my query is returning duplicates of some records. This is screwing up my
> > running totals. Adding DISTINCT the query I'm passing makes no
difference
> as
> > I guess Crystal doesn't recognize it. Is there a work around for this? I
> > don't see anything in the help.



Tue, 04 Mar 2003 03:00:00 GMT  
 
 [ 4 post ] 

 Relevant Pages 

1. Distinct Count on a Field?

2. Microsoft Access SQL: Distinct Count?

3. SELECT DISTINCT and memo field problem?

4. Does Access support SELECT Count(DISTINCT fieldname) ...

5. DISTINCT like clause

6. distinct values in listcontrols

7. finding distinct column values in datatable

8. finding distinct column values in datatable

9. ComboBox Show distinct????

10. VB select distinct Fax, Co_nam from tableA doesn't

11. Distinct Corporation's ICMP control and WINS query

12. Unique or Distinct field in a Report

 

 
Powered by phpBB® Forum Software