Re: Creation of a column based on values of other columns, help please!

On Fri, 24 Apr 1998 09:57:31 GMT,
mark_tortolano_at_dial.pipex.com (Mark Tortolano) wrote:

> I want to create a third column in this view that will
>have a true/false value that will be "true" if either of the other
>columns has a non-zero number in it.

Oracle doesn't support boolean values in a database column. Instead you can use a char column with 'T' and 'F' values. There's probably more than one approach to the view you want, but you could try this:

CREATE OR REPLACE VIEW your_view AS
SELECT col_1, col_2,

        DECODE(abs(nvl(col_1,0))+abs(nvl(col_2,0)),0,'F','T') FROM your_table;

The NVL calls turn any nulls into zero. The ABS calls change any negative numbers to positive. The DECODE will compute the sum of the two columns, and if the sum is zero will return 'F', other wise a 'T' will be returned. You need to check the absolute value, otherwise values of -2 and 2 will sum to zero and return 'F'.         

I'll be interested in seeing what other solutions people come up with for this.

regards,

Jonathan Received on Fri Apr 24 1998 - 21:36:42 CDT