11g. Incorrect results returned from query.
Date: Fri, 30 May 2008 06:29:14 -0700 (PDT)
Message-ID: <4690406e-f86e-45a5-a7b9-4aaf67d22e16@26g2000hsk.googlegroups.com>
Hi All,
MIDASTST>select * from v$version
2 ;
BANNER
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - 64bit Production
PL/SQL Release 11.1.0.6.0 - Production
CORE 11.1.0.6.0 Production
TNS for IBM/AIX RISC System/6000: Version 11.1.0.6.0 - Production NLSRTL Version 11.1.0.6.0 - Production
We are experiancing a very peculiar problem where the results returned from a query are "unpredictable". I say unpredictable because though the results change predictably, they are incorrect.
We do have an SR with support open currently, but they are not being very forthcoming...
I am wondering if anybody on here has seen similar, or can point out the the mistake we have made, because I can't see it. We have another similar issue when the result set changes depending on the order by, columns selected or parallel degree. I'll post that example if anybody is interested...but for now, this ought to be enough to get you going...or at least check my sanity.
MIDASTST>select * from (
2 SELECT
3 transaction_group, 4 transaction_type, 5 transaction_subtype,
6 nvl(transaction_subtype_type, 'AAA transaction_subtype_type field is NULL')
7 FROM warehouse.s4_claim_fact
8 -- WHERE transaction_subtype_type is null -- UNCOMMENT THIS LINE FIRST
9 order by 4
10 )
where rownum <= 10; 11
TRANSACTION_GROUP TRANSACTION_TYPE TRANSACTION_SUBTYPE
------------------------------ ------------------------------ --------------------------------------------------NVL(TRANSACTION_SUBTYPE_TYPE,'AAATRANSACTION_SUBTYPE_TYPEFIELDISNULL')
Claim Excess Excess amount
AAA transaction_subtype_type field is NULL
Claim Excess Excess amount
AAA transaction_subtype_type field is NULL
Claim Excess Excess amount
AAA transaction_subtype_type field is NULL
Claim Excess Excess amount
AAA transaction_subtype_type field is NULL
Claim Excess Excess amount
AAA transaction_subtype_type field is NULL
Claim Opened Open
AAA transaction_subtype_type field is NULL
Claim Opened Re-Opened
AAA transaction_subtype_type field is NULL
Claim Excess Excess amount
AAA transaction_subtype_type field is NULL
Claim Excess Excess amount
AAA transaction_subtype_type field is NULL
Claim Excess Excess amount
AAA transaction_subtype_type field is NULL
10 rows selected.
MIDASTST>8
8* -- WHERE transaction_subtype_type is null -- UNCOMMENT THIS LINE
FIRST
MIDASTST>c/--//
8* WHERE transaction_subtype_type is null -- UNCOMMENT THIS LINE FIRST
MIDASTST>/
no rows selected
Cheers
Ralph Received on Fri May 30 2008 - 08:29:14 CDT