Thanks for the question, Aben.
Asked: April 29, 2016 - 5:03 pm UTC
Last updated: March 15, 2021 - 5:07 am UTC
Version: 11.0.3.177 12.102801
Viewed 1000+ times
You Asked
Hello Tom and everyone,
I kinda new for Oracle and SQL, spare me if the question is too naive.
Just an observation I encounter in PL/SQL 11.0 today.
Here is part of the code causing ETL job abort, ORA-01722 Invalid Number.
--Query 0
SELECT 1
FROM B_FEE BF, B_FEE_DETAIL BFD,
B_SETTLE T3,B_OBJECT T4 ,B_INSURED T5,B_CASE T6,B_LOSSCAUSE T7,B_POLICY T8
WHERE BF.TRANS_ID = BFD.TRANS_ID
AND T3.SETTLE_ID=BF.SETTLE_ID
AND T4.OBJECT_ID=T3.OBJECT_ID
AND T6.CASE_ID=T4.CASE_ID
AND T7.LOSS_CAUSE=T6.LOSS_CAUSE
AND T6.CASE_ID=T8.CASE_ID
--A
AND T5.INSURED_ID=T4.LOCATION_INSURED_ID
--B
AND T8.ORDER_ID=T5.ORDER_ID
--C
AND BFD.INSTANCE_FEE_CATE IN (3,5)
AND EXISTS ( SELECT 1 FROM B_SETTLE_ITEM TCSI WHERE TCSI.RESERVE_TYPE IN ( '01','02')
AND TCSI.SETTLE_ID = BF.SETTLEMENT_ID )
Executed encounter ORA-01722 Invalid Number,
And I found issue casued by T4.LOCATION_INSURED_ID is Varchar2 type and T5.INSURED_ID is Number type.(--A to --B ).
We are patching the T4.LOCATION_INSURED_ID datatype to number now.
Here is the weird part. Before we patch anything.
When I move and only move AND T8.ORDER_ID=T5.ORDER_ID (--B to --C) ahead of AND T5.INSURED_ID=T4.LOCATION_INSURED_ID (--A to --B )
It executed without error and return 1000 rows.
For my understanding, the sequences of join does not affect the result right?
--Query 1
select 1 from tbl_A, tbl_B, tbl_C
where tbl_A.x = tbl_B.y and tbl_B.y =tbl_C.z
--Query 2
select 1 from tbl_A, tbl_B, tbl_C
where tbl_B.y =tbl_C.z and tbl_A.x = tbl_B.y
Query 1 and Query 2 suppose to return same result right?
Anyone can explain? Or what is the principle rule or basic knowledge I am missing?
First time here.Thank you very much.
Aben
and Connor said...
Here is a video by Chris showing a similar scenario to yours
https://www.youtube.com/watch?v=JrzAGLOcswo In his case, it's a date validation issue, but the concept is the same. Namely, that the order in which predicates are executed can impact whether you see errors in your SQL.
In a nut shell, consider:
where col > 1 and my_string_col = my_number_col
Let's say "col" is always zero. We also know that "string = number" will be evaluated as: "to_number(string) = number"
If we evaluate "col > 1" first, then we'd never get to do the string = number check, so if the string had bad data, it wouldn't matter.
But, if we did "my_string_col = my_number_col" first, then we might get ORA-01722 Invalid Number (and correctly so).
So the way the optimizer runs you query *can* change what you see in terms of errors.
Hope this helps.
Rating
(2 ratings)
Is this answer out of date? If it is, please let us know via a Comment