Not all the records returning from database?

[kelqc]

Hi
Below is the code I use to return some values from an Informix database. The query should return 13 values, but only actually returns 12 of them. 2 of the 13 values are the same though. eg bannana, egg, egg, orange, apple, grape etc.
For some reason the recordset call is removing any replcates. How do I stop this from happening and return all 13 records.

--------------------------------------------------

Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim fld As ADODB.Field
Dim SQL1 As String
Dim SQL2 As String
Dim SQLFINAL As String

Sub Button1_Click()

cn.ConnectionString = "metrix"
cn.Provider = "msdatashape"
cn.CursorLocation = adUseNone
cn.Open

SQL1 = "Select sample.job_no from sample where sample.user_decimal_1 in (" & "9662" & &quot"

SQL2 = "Select qa_sample.job_no from qa_sample where qa_sample.user_decimal_1 in (" & "9662" & &quot"

SQLFINAL = SQL1 & " union " & SQL2

rs.Open SQLFINAL, cn, adOpenForwardOnly, adLockReadOnly

rs.Sort = "job_no Asc"

While Not rs.EOF

For Each fld In rs.Fields

Range("B1&quot.Value = rs.RecordCount
Num_Str = "(A" & Pos1_Num & &quot"
Range(Num_Str).Value = fld.Value
rs.MoveNext
Pos1_Num = Pos1_Num + 1
Next
Wend

cn.Close
end sub

 

[VBOnly]

The problem lies in the SELECT statement add predicate ALL will solve your problem:
SQL1 = "Select ALL sample.job_no from sample where ..
For more information check the syntax at:

http://www.asp-help.com/database/db_sql_stmt.asp#SELECT

 

[kelqc]

Thanks for the reply VBOnly

What I have found out since posting the message is that if I do the query just with SQL1, I get one value back as expected.
When I do the query with just SQL2 I get 12 values back, including the dupicate values , which is expected. But when I "union" SQL1 and SQL2 I only get 12 records back, it should be 13 values. Only one fo the duplicate values are returned.

Any ideas why the "union" statment is doing this.

Thanks

 

[VBOnly]

kelqc:

Don't know exact mechanism there, but mathematic union does gives only non-repeating elements.

The solution would be to add the primary key field name of the database in both SQLs. This will definite get you all the records. I recommend to include the primary key in all the SQL (unless it is not neede intentionallY) queries, even your problem does not exist.

e.g. for SQL 1.
<CODE>
SQL1 = &quot;Select pkey, sample.job_no from sample where sample.user_decimal_1 in (&quot; & &quot;9662&quot; & &quot&quot;
</CODE>

If your database do not have primary key, then you may consider to add one. Or in the SQL add other field names to make the query result not unique.