RE: Decode with > or < in 8i??

Jim,

What you *might* be thinking about is the CASE expression, another means of expressing IF..THEN..ELSE logic. Here's an example from the 8.1.6 SQL manual:

        SELECT AVG(CASE WHEN e.sal > 2000 THEN e.sal ELSE 2000 END) from emp e;

<<<Related Question and Thoughts. A little long (surprise!) >>>

My *gut* feeling is that you cannot do a comparison operation using decode and the <, >, etc. in 8i. The reason I say that is if, for example, you did decode(sal>comm,.......), the return result from "sal>comm" would be a BOOLEAN, TRUE or FALSE. I've tried a few variations; but, nothing worked. There might be a way and neither of us has stumbled on it. Maybe someone else has?

And the reason I say there "might" be is that Oracle has loosened up the restrictions on how and where you can "specify" expressions. For example:

        select decode((select 'x' from dual),'x',1,2) from dual

While I know the example is dumb, we couldn't have done that in earlier versions. I think it began in 8.1.5 where we could begin to nest SELECTS within the SELECT statement as opposed to the in-line view method of specifying it in the FROM clause. It also loosened up in other places as well:

        insert into dept values (60,'DALLAS',(select max(ename) from emp));

I don't think we could have embedded a SELECT statement as an expression like that prior to 8i. I might be wrong.

Anyway, there might be ways to do it, fake it, whatever due to the ways we have greater freedom in specifying expressions. And while I am comfortable with the use of DECODE in conjunction with comparisons using SIGN, LEAST, GREATEST, and so on, even the experienced person has to spend some time thinking through some of the more complex DECODES they encounter where they nest 5 or 6 levels deep, use things such as SIGN, and so on. I agree with you that the SIGN approach "is not very elegant", and, I think it can be particularly difficult for a less experienced person to understand when dealing with complex DECODE's.

So, I wonder what stance, if any, people are taking on the use of the CASE expression versus the DECODE expression. For simple DECODE's, like that used for an example in the SQL Manual, the IF..THEN..ELSE logic is pretty clear and easily understood. But, many of us have encountered extremely complex DECODE's that took a little work to understand. If the CASE expression was used instead, it might be more readable and more easily maintained?

And the maintainability, readability, and clarity of code really should be a concern when coding. Good comments and documentation always help. How many of us have seen some really slick stuff done, maybe takes a few less lines of code, maybe it's a tad faster, whatever. But, the logic is so much more complex that the person who has to come in and make a change has to spend much more time figuring out *how* the code works than if it had been coded in a way that was more easily understood? So, that gets back to the point of my wondering what stance, if any, people are taking, or planning on taking, with regards to the use of CASE versus DECODE.

<<<Random thoughts, opinions, rants and so on off>>>

Regards,

L. Elkins

-----Original Message-----
From: root_at_fatcity.com [mailto:root_at_fatcity.com]On Behalf Of Jim Walski Sent: Friday, August 25, 2000 6:04 PM
To: Multiple recipients of list ORACLE-L Subject: Decode with > or < in 8i??

Is there any new SQL in 8i that will allow < > >= <= in a decode statement? I thought I read somewhere about a function to allow this? I know I can use the "sign" function but that is not very elegant. I read the 8.1.5 new features and couldn't seem to find it.

Thanks, Jim

Jim Walski
ClassicPlan Received on Fri Aug 25 2000 - 19:45:19 CDT