This week's book giveaway is in the OO, Patterns, UML and Refactoring forum. We're giving away four copies of Refactoring for Software Design Smells: Managing Technical Debt and have Girish Suryanarayana, Ganesh Samarthyam & Tushar Sharma on-line! See this thread for details.
You may also wish to look into stored procedures if the logic gets more complicated. Of course, any time one contemplates stored procedures, one should think about moving the logic into the application code.
Kaustubh G Sharma wrote:sorry brother but link is not working at my side
The link should be fine, so if you cannot access the Oracle site, try to search for sql case expression in Google. However, if you do work with Oracle, obtain the documenation. You'll need it. The link leads to book named Oracle® Database SQL Reference.
The case expression is an expresion, that is it does not "do" things, but returns some value. It can be used like this:
The value1, value2, value3 can be any value or expression, even another CASE Expression, so you can have it pretty complex. The case expression may be used in where or order by clauses as well.
If you need to "do" things, post the SQL code you would use to "do it" and separately the conditions. I can try to put in the conditions for you.
Your SQL could look something like this:Several notes:
The messageId field appears to be a number, however you compared it to strings in your query. That leads to implicit conversions and is potentially dangerous; always avoid these.
The outgoing list of valid IDs is strange; some nubers were repeated. I've sorted it and put it into an IN list so that it would be more apparent.
The WHERE clause limits the list of valid messageId's. Numbers that do not appear there will never make it into selected rows (eg. messageId will never equal to 1 in the CASE expression), it is therefore useless and possibly confusing to list them in the CASE expression conditions. If you remove them, the lists will be shorter and more manageable.
When direction is 'incoming', the value of checkbox will be either 'true' or null, when direction is 'outgoing', the value of checkbox will be either 'false' or null. For other values of direction it will be always null. Is this the expected behaviour? It seems a bit strange to me.
If your query is slow, it probably is not because of the CASE expression. Try replacing the computed value of checkbox field with a constant or null and re-run the statement. I'd expect the runtime to be practically equal. The problem could lie in the way tables are joined in your query, may be a missing index or something.