Re: Empty Row When Using DISTINCT

From: Val Mazur (group51a_at_hotmail.com)
Date: 05/29/04 

Date: Fri, 28 May 2004 22:30:32 -0400

Hi Tim,

Are you sure that you do not have any matching rows? Check if you do not
have this one row where RNPToLocation field contains NULL 

-- 
Val Mazur
Microsoft MVP
"Tim H." <anonymous@discussions.microsoft.com> wrote in message 
news:0B6F7743-19FA-49C3-B392-C79EDC275819@microsoft.com...
>I have a readonly recordset that I open using the following SQL:
>
>    Set mcnProject = New ADODB.Connection
>    strConnString = "Provider=Microsoft.Jet.OLEDB.3.51;Data Source=" & 
> mstrProjectPathAndName
>    Call mcnProject.Open(strConnString )
>
>    sSql = " SELECT DISTINCT 'NA' AS OwnerID,'S' & NPPS.RNPFromLocation & 
> 'to' & NPPS.RNPToLocation AS ID, "
>    sSql = sSql & " NPPS.RNPFromLocation AS OriginatingPointRowNumber, "
>    sSql = sSql & " NPPS.RNPToLocation AS TerminatingPointRowNumber, 
> NPPS.SpanLength "
>    sSql = sSql & " FROM NewPPS AS NPPS "
>    sSql = sSql & " WHERE NPPS.RNPToLocation <> 0 "
>    Call mrsSpans.Open(sSql, mcnProject, adOpenStatic, adLockReadOnly)
>    Call MakeClunkyFixBecauseOfADOBug(mrsSpans, sSql)
>
> The problem is that the table contains no matching rows for the WHERE 
> criteria and yet the recordset shows one row, and all of its fields are 
> empty. What I found was that by removing DISTINCT in the SQL I would get 
> back zero records as desired exception on UNION queries where I had to 
> take the extra step of removing and UNIONs in order to get zero records. I 
> created a method to get around this but its very clunky. Is this a bug 
> with ADO or am I possibly doing something wrong in the above code? And 
> yes, the table, for sure, has no matching records for the criteria. If I 
> drop the sql directly into and Access query and run it from Access, it 
> works correctly. If I call that query from code, I get the same bad 
> behavior mention above.
>
> '-----Method to get around the problem:
> Private Sub MakeClunkyFixBecauseOfADOBug(ByRef rs As ADODB.Recordset, 
> ByVal sql As String)
>
>    If rs.RecordCount = 1 Then
>        If rs!OwnerID & "" = "" Then
>            If InStr(1, UCase$(sql), "UNION") > 0 Then
>                sql = Left(sql, InStr(1, UCase$(sql), "UNION") - 1)
>            End If
>            sql = Replace(UCase$(sql), "DISTINCT", "")
>            If InStr(1, UCase$(sql), "WHERE") > 1 Then
>                sql = sql & " AND True=False"
>            Else
>                sql = sql & " WHERE True=False"
>            End If
>            If rs.State = adStateOpen Then
>                Call rs.Close
>            End If
>            Call rs.Open(sql, mcnProject, adOpenStatic, adLockReadOnly)
>            Debug.Print rs.RecordCount
>        End If
>    End If
>
> End Sub
>

Relevant Pages

  • Re: SQL SELECT Question
    ... bradm98 wrote: ... > My SQL is a bit rusty, and this one has me stumped: ... like what you want is all rows in A with matching rows in B that have ...
    (microsoft.public.sqlserver.server)
  • Re: Determine if record exists
    ... You could have a query that returns a field that only has the count of the ... number of matching rows such as: ... > SQL ... > Is there any way I can do this without using a try/catch? ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: How to count the result from sql statement?
    ... > I want to know how to count the result of sql from oracle9i. ... > if the result is two ppls, how can i retrieve this value ... If you only want to know the number of matching rows, ... That way, you don't need to download 100,000 rows when all you want ...
    (comp.lang.java.databases)