Hello Experts,

I am facing a problem in form of an error so please if you guys have any solution for that then please share it with me as I am doing the following things:

I Select 1/0 from dual; -- this gives an error "divisor is equal to 0"

select count(*) from (select 1/0 from dual); -- this gives output as 1.

Can anyone explain why?

Thanks in Advance!

Its because of Optimizer Plan. I mean if we fire, "Select 1/0 from dual" means, Oracle will compute the expression "1/0" and hence gives you the error. But when you do "select count(1) from (select 1/0 from dual)", oracle knows you just need to get the count of dual table i.e the above query is equal to select count(1) from dual. In Detail, Oracle assumes "1/0" as any other constant like 0,1,2,etc. and it assumes there is no need to evaluate the expression "1/0",, its just directly count the rows in dual and gives you the output. But the below query will through error SELECT * FROM (SELECT 1/0 FROM DUAL) because in this case, since we are doing "SELECT *", Oracle needs to evaluate the expression "1/0". Again consider another query, SELECT COUNT(1) FROM (

SELECT * FROM (SELECT 1/0 FROM DUAL)). This query also, will give output as 1. Because here there is no need to evaluate the expression "1/0" so this error will only come if it needs to evaluate the expression, otherwise it wont and we may think one more query SELECT COUNT(1/0) FROM DUAL;. This will give the error, the reason is different... Because Oracle needs to evaluate the values passed into the COUNT function.. it cannot assume it as a constant because COUNT(NULL) will always give result as 0. So in this case (COUNT(1/0)), it evaluates the expression "1/0" and gives error. I am not saying this is correct/Perfect behavior, I am saying this is how it behaves. Personally, I think it (the scenario which you have asked) should not behave like this.