Select Distinct problem from VB
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
--------------------------------------------------------
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?
--
==========================================================================
http://www.timinator.com
"My Reality Check Bounced!"
(Remove the "nojunk" if you want to send me mail)
==========================================================================
