 Wrong results with combination of Boolean, Left Join and To_Char [message #639500] |
Wed, 08 July 2015 13:04  |
 |
RFoersterj
Messages: 4
Registered: July 2015
|
Junior Member |
|
|
Hi dear Forum-members,
we've got a strange behaviour of our Oracle 12c database:
We've got 2 tables, one containing informations about orders and another one that contains additional Information about those orders (so the relationship is a 1:1 relation).
When I run this specific query on our database, it won't give me back any values for the field CFISDEPOT (all results are null altough it is a boolean field that contains 1), but it should.
SELECT b.cfisdepot,
b.cfdepotkennung,
To_char(a.vonlieferdatumzeit, 'ddmmyyyy'),
To_char(a.vonlieferdatumzeit, 'ddmmyyyy')
FROM asendung a
LEFT JOIN abusinesspartnercustomfieldv b
ON a.absnr = b.id
The explain plan Shows the following:
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 388152569
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 483K| 17M| 37176 (1)| 00:00:02 |
|* 1 | HASH JOIN RIGHT OUTER| | 483K| 17M| 37176 (1)| 00:00:02 |
| 2 | TABLE ACCESS FULL | ABUSINESSPARTNERCUSTOMFIELDV | 626 | 15024 | 11 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | ASENDUNG | 483K| 6138K| 37164 (1)| 00:00:02 |
------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$0E991E55
2 - SEL$0E991E55 / B@SEL$1
3 - SEL$0E991E55 / A@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ABSNR"="B"."ID"(+))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - (#keys=1) ROWID[ROWID,10], "B"."CFISDEPOT"[NUMBER,22], "B"."CFISDEPOT"[NUMBER,22],
"B"."CFDEPOTKENNUNG"[VARCHAR2,1020], "A"."VONLIEFERDATUMZEIT"[DATE,7]
2 - ROWID[ROWID,10], "B"."ID"[NUMBER,22], "SYS_NC00022$"[RAW,126],
"B"."CFISDEPOT"[NUMBER,22], "B"."CFDEPOTKENNUNG"[VARCHAR2,1020], "B"."CFISDEPOT"[NUMBER,22]
3 - "A"."ABSNR"[NUMBER,22], "A"."VONLIEFERDATUMZEIT"[DATE,7]
31 Zeilen gewählt
When I remove one of the To_Char Statements, it works perfectly fine. Also it works fine when I change the left join to an inner join.
Have you ever experienced such a behaviour? The strange thing is that when I run this
query on our development-database, it works!
I compared all parameters of those two machines (SPFILE) and I could
figure out that the only differences are the nls_territory setting (on our production-box it's set to "Germany", on the
Dev-Box it's set to "Austria") and the allocated memory.
Thank you very much in advance for your help! Please let me know if you need any more Information.
-Robert
[Updated on: Wed, 08 July 2015 13:17] Report message to a moderator |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
