oracle query with inconsistent results
- by Spencer Stejskal
Im having a very strange problem, i have a complicated view that returns incorrect data when i query on a particular column.
heres an example:
select empname, has_garnishment from timecard_v2 where empname = 'Testerson, Testy';
this returns the single result 'Testerson, Testy', 'N'
however, if i use the query:
select empname, has_garnishment from timecard_v2 where empname = 'Testerson, Testy' and has_garnishment = 'Y';
this returns the single result 'Testerson, Testy', 'Y'
The second query should return a subset of the first query, but it returns a different answer.
I have dissected the view and determined that this section of the view definition is where the problem arises(Note, I removed all of the select clause except the parts of interests for clarity, in the full query all joined tables are required):
SELECT
e.fullname empname ,
NVL2(ded.has_garn, 'Y', 'N') has_garnishment
FROM timecard tc ,
orderdetail od ,
orderassign oa ,
employee e ,
employee3 e3 ,
customer10 c10 ,
order_misc om,
(SELECT COUNT(*) has_garn,
v_ssn
FROM deductions
WHERE yymmdd_stop = 0
OR (LENGTH(yymmdd_stop) = 7
AND to_date(SUBSTR(yymmdd_stop, 2), 'YYMMDD') sysdate)
GROUP BY v_ssn
) ded
WHERE oa.lrn(+) = tc.lrn_order
AND om.lrn(+) = od.lrn
AND od.orderno = oa.orderno
AND e.ssn = tc.ssn
AND c10.custno = tc.custno
AND e.lrn = e3.lrn
AND e.ssn = ded.v_ssn(+)
One thing of note about the definition of the 'ded' subquery. The v_ssn field is a virtual field on the deductions table.
I am not a DBA im a software developer but we recently lost our DBA and the new one is still getting up to speed so im trying to debug this issue. That being said, please explain things a little more thoroughly then you would for a fellow oracle expert.
thanks
