Re: using constants

On Thu, 29 Nov 2007 16:44:20 +0100, Marten Lehmann <lehmannmapson_at_cnm.de> wrote:

>Hello,
>
>currently, we are using strings like "pending" or "finished" in some
>tables to specify the state of certain jobs stored in these tables.
>These values are self-explaining. These columns are indexed, of course.
>
>But I guess, lookups of jobs with certain states would be much faster,
>if the states would be stored is integer values, e.g. pending = 1,
>finished = 2 and so on. However, these values wouldn't be
>self-explaining any more.
>
>As long as I'm working with Java, I could defined constants within a
>class, so instead of integer values, I could use constants like
>State.PENDING or State.FINISHED.
>
>Is it possible to create such constants directly in Oracle, so I can use
>them within SQL-queries like
>
>select * from jobs where state = State.PENDING
>
>?
>
>Regards
>Marten

If you do switch to numbers, a VIEW can be used that automatically changes the numbers to words, either with a CASE expression, or by joining to a TABLE that defiunes them. Then, the search could be directly on the words.

To your first question, as others have said, it is not likely to make a difference. If the word defines a well known state, and the name will never change, it is just fine. A lookup table may be nice, but that depends on the way it is used.

B. Received on Thu Nov 29 2007 - 11:33:33 CST