Re: SQL Order

From: Billy Verreynne <vslabs_at_onwe.co.za>
Date: Wed, 18 Sep 2002 09:41:25 +0200
Message-ID: <am9aog$j5s$1@ctb-nnrp2.saix.net>

Lucas wrote:

> After extensive testing/usage of 'decode', it only seems to handle ~150
> codes than I get an 'ORA-00939 too many arguments for function'....any
> other way of solving my original problem?

A proper database design.

Decode is IMO primarily intended for aggregation purposes, e.g. SELECT

  SUM (DECODE( gender, 'M', 1, 0)) MALES,
  SUM (DECODE( gender, 'F', 1, 0)) FEMALES,
  SUM (DECODE( gender, 'X', 1, 0)) ALIENS_VISITING_FROM_ALPHA_CENTAURI

FROM world

Decode is not intended to fix design flaws - not saying that it has helped many of us out of the poo, but when you finally hit that brickwall it is not the fault of the DECODE being limited to 150 or so expressions.

To fix your problem.
CREATE TABLE fix_database_design
( empcode NUMBER,

   sort_order NUMBER
);

INSERT INTO fix_database_design ( empcode )   SELECT DISTINCT empcode FROM emp;
COMMIT; Now you manually update this table to specify what the sort order should be.

Now you do your query by including this table via a join on empcode. Sort the results on sort_order.

Then, if you ask me nicely, you may borrow my lead pipe and beat the shit out of the person who designed your database.

--
Billy

Received on Wed Sep 18 2002 - 02:41:25 CDT