Is it a Bug in Oracle or in The Query??
Bhushan, September 18, 2009 - 11:58 am UTC
Dear Thomas,
Below is the query i run it runs perfect with the where clause commnented.The moment i put in the where clause it gives me invalid number error.
SELECT CAlculated_total,csv_value-CAlculated_total FROM (
SELECT inv_no,CSV_STRING,tran_code,defaultcode,
prd_group,
product_dtl,to_number(CAlculated_total) CAlculated_total,base_amount ,tran_amount ,base_price ,csv_value ,to_number(csv_value-CAlculated_total) act_total
FROM(
SELECT
inv_no,CSV_STRING,tran_code,defaultcode,
prd_group,
product_dtl,
nvl(substr(FIRST,2,instr(FIRST,'$',1,2)-instr(FIRST,'$',1,1)-1)* substr(FIRST,instr(FIRST,'$',1,2)+1,instr(FIRST,'$',1,3)-instr(FIRST,'$',1,2)-1),0)+
nvl(substr(SECOND,2,instr(SECOND,'$',1,2)-instr(SECOND,'$',1,1)-1)* substr(SECOND,instr(SECOND,'$',1,2)+1,instr(SECOND,'$',1,3)-instr(SECOND,'$',1,2)-1),0)+
nvl(substr(third ,2,instr(third ,'$',1,2)-instr(third,'$',1,1)-1)* substr(third,instr(third,'$',1,2)+1,instr(third,'$',1,3)-instr(third,'$',1,2)-1),0)+
nvl(substr(fourth ,2,instr(fourth,'$',1,2)-instr(fourth,'$',1,1)-1)* substr(fourth,instr(fourth,'$',1,2)+1,instr(fourth,'$',1,3)-instr(fourth,'$',1,2)-1),0)+
nvl(substr(fifth ,2,instr(fifth,'$',1,2)-instr(fifth,'$',1,1)-1)* substr(fifth,instr(fifth,'$',1,2)+1,instr(fifth,'$',1,3)-instr(fifth,'$',1,2)-1),0)+
nvl(substr(sixth ,2,instr(sixth,'$',1,2)-instr(sixth,'$',1,1)-1)* substr(sixth,instr(sixth,'$',1,2)+1,instr(sixth,'$',1,3)-instr(sixth,'$',1,2)-1),0)+
nvl(substr(seventh ,2,instr(seventh,'$',1,2)-instr(seventh,'$',1,1)-1)* substr(seventh,instr(seventh,'$',1,2)+1,instr(seventh,'$',1,3)-instr(seventh,'$',1,2)-1),0)+
nvl(substr(eighth ,2,instr(eighth,'$',1,2)-instr(eighth,'$',1,1)-1)* substr(eighth,instr(eighth,'$',1,2)+1,instr(eighth,'$',1,3)-instr(eighth,'$',1,2)-1),0)+
nvl(substr(nine ,2,instr(nine,'$',1,2)-instr(nine,'$',1,1)-1)* substr(nine,instr(nine,'$',1,2)+1,instr(nine,'$',1,3)-instr(nine,'$',1,2)-1),0)+
nvl(substr(ten ,2,instr(ten,'$',1,2)-instr(ten,'$',1,1)-1)* substr(ten,instr(ten,'$',1,2)+1,instr(ten,'$',1,3)-instr(ten,'$',1,2)-1),0)+
nvl(substr(eleven ,2,instr(eleven,'$',1,2)-instr(eleven,'$',1,1)-1)* substr(eleven,instr(eleven,'$',1,2)+1,instr(eleven,'$',1,3)-instr(eleven,'$',1,2)-1),0)+
nvl(substr(twelve ,2,instr(twelve,'$',1,2)-instr(twelve,'$',1,1)-1)* substr(twelve,instr(twelve,'$',1,2)+1,instr(twelve,'$',1,3)-instr(twelve,'$',1,2)-1),0)+
nvl(substr(thirteen ,2,instr(thirteen,'$',1,2)-instr(thirteen,'$',1,1)-1)* substr(thirteen,instr(thirteen,'$',1,2)+1,instr(thirteen,'$',1,3)-instr(thirteen,'$',1,2)-1),0)+
nvl(substr(fourteen ,2,instr(fourteen,'$',1,2)-instr(fourteen,'$',1,1)-1)* substr(fourteen,instr(fourteen,'$',1,2)+1,instr(fourteen,'$',1,3)-instr(fourteen,'$',1,2)-1),0)+
nvl(substr(fifteen ,2,instr(fifteen,'$',1,2)-instr(fifteen,'$',1,1)-1)* substr(fifteen,instr(fifteen,'$',1,2)+1,instr(fifteen,'$',1,3)-instr(fifteen,'$',1,2)-1),0)+
nvl(substr(sixteen ,2,instr(sixteen,'$',1,2)-instr(sixteen,'$',1,1)-1)* substr(sixteen,instr(sixteen,'$',1,2)+1,instr(sixteen,'$',1,3)-instr(sixteen,'$',1,2)-1),0)+
nvl(substr(seventeen ,2,instr(seventeen,'$',1,2)-instr(seventeen,'$',1,1)-1)* substr(seventeen,instr(seventeen,'$',1,2)+1,instr(seventeen,'$',1,3)-instr(seventeen,'$',1,2)-1),0)+
nvl(substr(eighteen ,2,instr(eighteen,'$',1,2)-instr(eighteen,'$',1,1)-1)* substr(eighteen,instr(eighteen,'$',1,2)+1,instr(eighteen,'$',1,3)-instr(eighteen,'$',1,2)-1),0)+
nvl(substr(nineteen ,2,instr(eighteen,'$',1,2)-instr(eighteen,'$',1,1)-1)* substr(eighteen,instr(eighteen,'$',1,2)+1,instr(eighteen,'$',1,3)-instr(eighteen,'$',1,2)-1),0)+
nvl(substr(twenty ,2,instr(twenty,'$',1,2)-instr(twenty,'$',1,1)-1)* substr(twenty,instr(twenty,'$',1,2)+1,instr(twenty,'$',1,3)-instr(twenty,'$',1,2)-1),0)+
nvl(substr(twentyone ,2,instr(twentyone,'$',1,2)-instr(twentyone,'$',1,1)-1)* substr(twentyone,instr(twentyone,'$',1,2)+1,instr(twentyone,'$',1,3)-instr(twentyone,'$',1,2)-1),0)+
nvl(substr(twentytwo ,2,instr(twentytwo,'$',1,2)-instr(twentytwo,'$',1,1)-1)* substr(twentytwo,instr(twentytwo,'$',1,2)+1,instr(twentytwo,'$',1,3)-instr(twentytwo,'$',1,2)-1),0)+
nvl(substr(twentythree ,2,instr(twentythree,'$',1,2)-instr(twentythree,'$',1,1)-1)* substr(twentythree,instr(twentythree,'$',1,2)+1,instr(twentythree,'$',1,3)-instr(twentythree,'$',1,2)),0) CAlculated_total,
base_amount ,tran_amount ,base_price ,csv_value
FROM (SELECT CSV_STRING,tran_code,defaultcode,
SUBSTR(CSV_STRING, 2, INSTR(CSV_STRING, '/', 2, 1) - 2) FIRST,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 1) + 1,
INSTR(CSV_STRING, '/', 2, 2) - INSTR(CSV_STRING, '/', 2, 1) - 1) SECOND,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 2) + 1,
INSTR(CSV_STRING, '/', 2, 3) - INSTR(CSV_STRING, '/', 2, 2) - 1) THIRD,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 3) + 1,
INSTR(CSV_STRING, '/', 2, 4) - INSTR(CSV_STRING, '/', 2, 3) - 1) FOURTH,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 4) + 1,
INSTR(CSV_STRING, '/', 2, 5) - INSTR(CSV_STRING, '/', 2, 4) - 1) FIFTH,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 5) + 1,
INSTR(CSV_STRING, '/', 2, 6) - INSTR(CSV_STRING, '/', 2, 5) - 1) SIXTH,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 6) + 1,
INSTR(CSV_STRING, '/', 2, 7) - INSTR(CSV_STRING, '/', 2, 6) - 1) SEVENTH,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 7) + 1,
INSTR(CSV_STRING, '/', 2, 8) - INSTR(CSV_STRING, '/', 2, 7) - 1) EIGHTH,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 8) + 1,
INSTR(CSV_STRING, '/', 2, 9) - INSTR(CSV_STRING, '/', 2, 8) - 1) NINE,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 9) + 1,
INSTR(CSV_STRING, '/', 2, 10) - INSTR(CSV_STRING, '/', 2, 9) - 1) TEN,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 10) + 1,
INSTR(CSV_STRING, '/', 2, 11) - INSTR(CSV_STRING, '/', 2, 10) - 1) ELEVEN,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 11) + 1,
INSTR(CSV_STRING, '/', 2, 12) - INSTR(CSV_STRING, '/', 2, 11) - 1) TWELVE,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 12) + 1,
INSTR(CSV_STRING, '/', 2, 13) - INSTR(CSV_STRING, '/', 2, 12) - 1) THIRTEEN,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 13) + 1,
INSTR(CSV_STRING, '/', 2, 14) - INSTR(CSV_STRING, '/', 2, 13) - 1) FOURTEEN,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 14) + 1,
INSTR(CSV_STRING, '/', 2, 15) - INSTR(CSV_STRING, '/', 2, 14) - 1) FIFTEEN,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 15) + 1,
INSTR(CSV_STRING, '/', 2, 16) - INSTR(CSV_STRING, '/', 2, 15) - 1) SIXTEEN,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 16) + 1,
INSTR(CSV_STRING, '/', 2, 17) - INSTR(CSV_STRING, '/', 2, 16) - 1) SEVENTEEN,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 17) + 1,
INSTR(CSV_STRING, '/', 2, 18) - INSTR(CSV_STRING, '/', 2, 17) - 1) EIGHTEEN,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 18) + 1,
INSTR(CSV_STRING, '/', 2, 29) - INSTR(CSV_STRING, '/', 2, 18) - 1) NINETEEN,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 19) + 1,
INSTR(CSV_STRING, '/', 2, 20) - INSTR(CSV_STRING, '/', 2, 19) - 1) TWENTY,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 20) + 1,
INSTR(CSV_STRING, '/', 2, 21) - INSTR(CSV_STRING, '/', 2, 20) - 1) TWENTYONE,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 21) + 1,
INSTR(CSV_STRING, '/', 2, 22) - INSTR(CSV_STRING, '/', 2, 21) - 1) TWENTYTWO,
SUBSTR(CSV_STRING,
INSTR(CSV_STRING, '/', 2, 22) + 1,
INSTR(CSV_STRING, '/', 2, 23) - INSTR(CSV_STRING, '/', 2, 22) - 1) TWENTYTHREE,
INV_NO,
PRD_GROUP,
PRODUCT_DTL,
BASE_AMOUNT,
TRAN_AMOUNT,
BASE_PRICE,
CSV_VALUE
FROM BRS_TRAN_DTL WHERE company='SNL' AND prd_group='CHEM' AND defaultcode !='SLA' AND tran_effect IN ('R','S')
--AND defaultcode='SGM'
) ))
WHERE to_number(nvl(csv_value,0)-nvl(CAlculated_total,0)) > 0
I was very sure there are no characters so i created a table with 2 columns and data type as number and inserted from this SQL all perfect.Any clue why this query might be giving invalid number when run with WHERE (to_number(nvl(csv_value,0)-nvl(CAlculated_total,0)) > 0) clause. DB version is Connected to Oracle9i Enterprise Edition Release 9.0.1.1.1 .Connected through PL/SQL developer. One request..if you think there is noway you can answer having a look at the query, due to insufficient data please reply in a single word IGNORED.I will try to make up some data then. TIA Cheers!!!
Bhushan
September 18, 2009 - 12:45 pm UTC
I see no where clause
but undoubtedly - it is not a bug, you are comparing a string to a number, we convert the string to a number and - guess what - it isn't a number
happens every time you store numbers or dates in strings
every
single
time
I can already tell I hate your data "model", I put "model" in quotes - because if you have to parse a string like that in your "model", we are using the term "model" very loosely and very very generously