I use decommenting occasionally
Lars Sjöström, July 07, 2016 - 8:12 am UTC
Hi,
In order to see what condition in a WHERE-clause that is "failing" in some way or not getting you the correct (or expected) result set I use decommenting.
Decomment each one of the conditions and study the result.
A reader, July 07, 2016 - 8:26 am UTC
Chris Saxon,
Appreciate your quickly response. Perhaph, I've used the wrong word in my question: fail !
I think the right question should be:
which condition of the WHERE clause return FALSE ( or TRUE) ?
Thanks & Regards.
PV.
July 07, 2016 - 9:49 am UTC
"which condition of the WHERE clause return FALSE ( or TRUE) ? "
That's evaluated on a per row basis. So for row 1 it could be true, row 2 false, etc.
The fact that a row is returned means it was true for that row. If it wasn't, it was false.
All your predicates should reference at least one column from a table. If it doesn't why have you included it in your SQL?
Your example of :
1 = 1 + 1
Or any variation of "constant = formulas using constants" doesn't make any sense in the real world. Either it's always true or always false. So you get all the rows or none.
(caveat: when writing dynamic SQL I tend to use "where 1=1" to start the where clause. This is simply because it makes writing the extra conditions easier. No faffing with trailing/leading ANDs).
A reader, July 07, 2016 - 8:29 am UTC
Lars Sjöström,
I do agree with you, but do we have another better (automatic) ways ?
Rgds.
PV.
A reader, July 07, 2016 - 10:13 am UTC
"which condition of the WHERE clause return FALSE ( or TRUE) ? "
That's evaluated on a per row basis. So for row 1 it could be true, row 2 false, etc.
The fact that a row is returned means it was true for that row. If it wasn't, it was false.
All your predicates should reference at least one column from a table. If it doesn't why have you included it in your SQL?
Your example of :
1 = 1 + 1
Or any variation of "constant = formulas using constants" doesn't make any sense in the real world. Either it's always true or always false. So you get all the rows or none.
(caveat: when writing dynamic SQL I tend to use "where 1=1" to start the where clause. This is simply because it makes writing the extra conditions easier. No faffing with trailing/leading ANDs).
The practical example is:
I try to put all the logic of the pl/sql into a SQL statement (as the advise of asktom before), so my SQL is quite complex and sometime it not produces the result as expected, then so I dont know why and which condition not meet ?, if we know which condition in the WHERE clause return FALSE, we can check & fix it faster.
The more complex logic in SQL-statement the more time to debug.(de-comment or comment each condition, line by line consume much time).
Do we have other tools (ex: SQL Developer 4.1) to support this ?
Thanks & Rgds.
PV.
July 07, 2016 - 12:25 pm UTC
What do you mean:
"I try to put all the logic of the pl/sql into a SQL statement"
?
Show us your code!
If you have multiple conditions and you're not sure which is returning false, you'll need to remove them until you get the result you want!
put conditions on select
MatteoP, July 07, 2016 - 12:31 pm UTC
let's say you have a query like this
with a as (
select *
from user_objects
where rownum <=10)
select object_type, status
from a
where object_type in ('TABLE','INDEX')
or status != 'VALID'
If you want to know which condition is false then move it to the returned columns
with a as (
select *
from user_objects
where rownum <=10)
select object_type,
status,
case when object_type in ('TABLE','INDEX') then 'ThisIsTrue' else 'ThisIsFalse' end cond1,
case when status != 'VALID' then 'ThisIsTrue' else 'ThisIsFalse' end cond2
from a
results in
OBJECT_TYPE STATUS COND1 COND2
TABLE VALID ThisIsTrue ThisIsFalse
TABLE VALID ThisIsTrue ThisIsFalse
INDEX VALID ThisIsTrue ThisIsFalse
INDEX VALID ThisIsTrue ThisIsFalse
TABLE VALID ThisIsTrue ThisIsFalse
INDEX VALID ThisIsTrue ThisIsFalse
TABLE VALID ThisIsTrue ThisIsFalse
INDEX VALID ThisIsTrue ThisIsFalse
TABLE VALID ThisIsTrue ThisIsFalse
TABLE VALID ThisIsTrue ThisIsFalse
July 07, 2016 - 2:24 pm UTC
Yes, that's one way to do this.
A reader, July 08, 2016 - 1:54 am UTC
MatteoP, your way can meet our expect.
Does Oracle Database have any built-in solution to do this? or an automation way ?
Oracle Database has many built-in features for tuning SQL performance, and not for SQL*logic*analyzer yet. I think Oracle should consider this feature in the next release.
The SQL execute logic is quite important, especially with Analytic SQL statement.
/*
Show us your code!
If you have multiple conditions and you're not sure which is returning false, you'll need to remove them until you get the result you want!
*/
I mean we transform the logic of IF...THEN, FOR..LOOP or WHILE..LOOP into SQL Statement.
Thanks & Rgds.
PV.
July 08, 2016 - 3:54 am UTC
We encourage people to log enhancement requests. If there is something you want in the database, but it is not there today...log a request, and maybe it will be there in the next release.
Cheers,
Connor