The following simple SQL query generates an ORA-01427 error when searching for certain specific object numbers.
SELECT OBJ
FROM A.TABLEA
WHERE (OBJ ='XXXXXX');
The query works fine except for when you choose an OBJ where there is another OBJ row/s with a corresponding A suffix 'XXXXXXA'. i.e. querying for OBJ = 'XXXXXXA' works fine, but querying for OBJ = 'XXXXXX' produces the error, alternatively querying for OBJ = 'XXXXXY' works fine when there is no corresponding OBJ = 'XXXXXYA'
Is there a way to handle / avoid the error where it will pick the first/last/random single row?
And going a step further instead of searching for specific Objects I want to search within a date range where I have to join another table to get the date, the error will show if there is a 'XXXXXX' with corresponding 'XXXXXXA' within the date range. See code below,
SELECT
t1.*,
t2.OBJ,
t2.DATE
FROM
A.TABLEA t1
LEFT OUTER JOIN
A.TABLEB t2
ON (t1.OBJ = t2.OBJ)
WHERE
(t2.DATE BETWEEN TO_DATE ('2019/04/01', 'yyyy/mm/dd')
AND TO_DATE ('2019/04/30', 'yyyy/mm/dd');
Is there a way to handle / avoid the error in this code to force it to select 1 row?
Thanks in advance.
