Re: [Help]Select Distinct problem from VB

From: Jeff Carver <jeff_carver_at_hotmail.com>
Date: 1998/04/29
Message-ID: <eB7EdP7c9GA.201@uppssnewspub04.moswest.msn.net>#1/1

Tim,

Using "distinct" in a subquery does not in itself guarantee that the subquery will always return only one value. In TABLE2, is FIELD3 constrained as "no duplicates allowed"? If not, the subquery could return any number of distinct OTHFIELD values in rows where FIELD3 = 'myval'. With the following rows in TABLE2, the subquery would return two distinct values, 'X' and 'Y', for FIELD3 = 'myval', and that _should_ make the insert fail:

FIELD1 FIELD2 FIELD3 OTHFIELD
====== ====== ====== =========

A             2             myval      X
B             5             myval      Y

If the VB problem is in fact being caused by multiple rows with the same FIELD3 value but different OTHFIELD values, and if the same insert "fails to fail" with the same data in SQL Plus, then I can only assume that SQL Plus automatically, and invisibly, turns the subquery into a "select first 1..." query instead of raising an error (if so, shame on Oracle!).

Hope this helps,
Jeff Carver

Tim Trimble wrote in message <893442467.476839_at_wagasa.cts.com>... I've run into a major problem. The following statement works fine from SQL Plus (Oracle) but when ran from VB it hangs the application. The statement works fine when there is only one matching record. However, if there are more than one matching record (the reason for having the Distinct statement) then the app hangs from VB (5.0, SP3).

Here's the SQL statement:
UPDATE MYTABLE SET MYFIELD =
(SELECT DISTINCT OTHFIELD FROM TABLE2 WHERE FIELD3 = 'myval')
WHERE MYFIELD2 = 'myotherval'

Any ideas?

--


==========================================================================

The Timinator - ttrimble_at_nojunk.fda.net (personal)  ICQ#: 1685060
http://www.timinator.com
"My Reality Check Bounced!"
(Remove the "nojunk" if you want to send me mail)


==========================================================================

Received on Wed Apr 29 1998 - 00:00:00 CDT