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

Heenan, Lambert Lambert.Heenan at aig.com
Thu Oct 1 10:47:34 CDT 2015


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 Heenan, Lambert
Sent: Thursday, October 01, 2015 10:40 AM
To: 'Access-D Email\aaccessd at databaseadvisors.com''
Cc: 'ACCESS-L Email (ACCESS-L at PEACH.EASE.LSOFT.COM)'
Subject: Re: [AccessD] Access / Oracle Distinct queries - Distinct not working!

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



More information about the AccessD mailing list