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:\[code\]select empname , has_garnishment from timecard_v2 where empname = 'Testerson, Testy';\[/code\]this returns the single result 'Testerson, Testy', 'N'however, if i use the query:\[code\]select empname , has_garnishment from timecard_v2 where empname = 'Testerson, Testy'and has_garnishment = 'Y';\[/code\]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.When I use the query:\[code\]select empname , has_garnishment from timecard_v2 where empname = 'Testerson, Testy'and has_garnishment = 'N';\[/code\]I get no resultsI have dissected the view and determined that this section of the view definition is where the problem arises and the problem exists even if i take the sql definition and run it as a straight query(Note, I removed all of the select clause except the parts of interests for clarity, in the full query all joined tables are required):\[code\]SELECT e.fullname empname , NVL2(ded.has_garn, 'Y', 'N') has_garnishmentFROM 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 ) dedWHERE oa.lrn(+) = tc.lrn_orderAND om.lrn(+) = od.lrnAND od.orderno = oa.ordernoAND e.ssn = tc.ssnAND c10.custno = tc.custnoAND e.lrn = e3.lrnAND e.ssn = ded.v_ssn(+)\[/code\]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
heres an example:\[code\]select empname , has_garnishment from timecard_v2 where empname = 'Testerson, Testy';\[/code\]this returns the single result 'Testerson, Testy', 'N'however, if i use the query:\[code\]select empname , has_garnishment from timecard_v2 where empname = 'Testerson, Testy'and has_garnishment = 'Y';\[/code\]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.When I use the query:\[code\]select empname , has_garnishment from timecard_v2 where empname = 'Testerson, Testy'and has_garnishment = 'N';\[/code\]I get no resultsI have dissected the view and determined that this section of the view definition is where the problem arises and the problem exists even if i take the sql definition and run it as a straight query(Note, I removed all of the select clause except the parts of interests for clarity, in the full query all joined tables are required):\[code\]SELECT e.fullname empname , NVL2(ded.has_garn, 'Y', 'N') has_garnishmentFROM 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 ) dedWHERE oa.lrn(+) = tc.lrn_orderAND om.lrn(+) = od.lrnAND od.orderno = oa.ordernoAND e.ssn = tc.ssnAND c10.custno = tc.custnoAND e.lrn = e3.lrnAND e.ssn = ded.v_ssn(+)\[/code\]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