[AccessD] Access / Oracle Distinct queries - Distinct not working!

[Heenan, Lambert]

Not sure why, but my follow-up post did not appear. Repeated below.
-----------------------------------

Finally got to the bottom of this, or close to the bottom, and so I thought I'd post this so the community is aware of the 'gotcha' I stumbled across.

The reason my select distinct query was not working was that the column APPLICANT_ID in Oracle table is defined as VARCHAR2(255, CHAR), but when I use the pass-through query, Access is seeing it as a Memo field for some reason. To fix this I cast the column in the pass-through to force it to be text...

CAST (LA. APPLICANT _ID AS VARCHAR2 (20)) APPLICANT _ID,

And that then allows the select distinct query to do its job.

Lambert



-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Bill Benson
Sent: Thursday, October 01, 2015 10:51 AM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Access / Oracle Distinct queries - Distinct not working!

Never a waste of time. I learned about the difference between Access's Unique Records and Unique Values relative to DISTINCTROW and DISTINCT, respectively, properties in the design window which I had forgotten about.
And that DISTINCTROW is not for single tables but for joined table queries at least in the Access environment.
Brain improved.

On Thu, Oct 1, 2015 at 10:39 AM, Heenan, Lambert <Lambert.Heenan at aig.com>
wrote:

> OK. All bets are off. Today when I run the 'problem' query it returns 
> the correct result! I am 99.99% sure that yesterday I ran
>
> SELECT DISTINCT APPLICANT_ID FROM MyQuery
>
> And not
>
> SELECT DISTINCTROW APPLICANT_ID FROM MyQuery
>
> But today DISTINCT is doing what it's supposed to do!!!
>
> Sorry if I wasted anyone's time.
>
>
>
> -----Original Message-----
> From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf 
> Of Stuart McLachlan
> Sent: Wednesday, September 30, 2015 6:50 PM
> To: 'Access-D Email\aaccessd at databaseadvisors.com''
> Subject: Re: [AccessD] Access / Oracle Distinct queries - Distinct not 
> working!
>
> Do you have GROUP BY APPLICANT_ID in the new query?
>
> On 30 Sep 2015 at 16:04, Heenan, Lambert wrote:
>
> > Cross Posted to Access-l and Access-d.
> >
> > Here's a very strange thing. I have a pass-through query that 
> > connects to an Oracle Db and returns a set of rows with Applicant 
> > IDs, and Medical Review IDs (MUR). There can be, and are multiple 
> > MUR IDs per Applicant ID.
> >
> > When I run the query in Access 2010 I get 8,429 rows back. When I 
> > use the Pass-through as the source for a new query and run SELECT 
> > DISTINCT APPLICANT_ID FROM MyQuery, to find out the number of unique 
> > applicants in the dataset then that query also returns 8,429 rows, 
> > and I see Applicant IDs repeated in the results. Distinct has had no effect.
> >
> > On the other hand, if I run the exact same select distinct query in 
> > Toad, then I get back the expected result: 3,829 unique applicants.
> > Likewise, if I use the first pass-through query as toe source for a 
> > make-table query and the run SELECT DISTINCT on the temporary table 
> > just created, then I get 3,829 rows.
> >
> > What gives?
> >
> > Lambert
> >
> >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
--
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com