Re: Unable to use DECODE with boolean variables

It is not objecting to the DECODE but to the values you are trying to return after testing the boolean_var. FALSE and TRUE are not column names in TABLE TEST. You could return 'FALSE' or 'TRUE'.

scott moyer
dmoyer_at_gpu.com
dsmoyer_at_enter.net

In <ABgZB7nCp5_at_dwayne.FreeNet.Kiev.UA>, Dwayne King <dwayne_at_dwayne.FreeNet.Kiev.UA> writes:
>Hi there,
>
>I am curently trying to use the DECODE function in a batch job I am working on.
>
>Is there a restriction that boolean variables cannot be used with DECODE? There
>is no mention of this in the manuals. I have tried the folowing as a simple
>test:
>
>SQL> create table test (boolean_var number(1);
>
>Table created.
>
>SQL> insert into test values(0);
>Commit complete.
>
>1 row created.
>
>SQL> c/0/1
> 1* insert into test values(1)
>SQL> run
> 1* insert into test values(1)
>Commit complete.
>
>1 row created.
>
>SQL> select * from test;
>
>BOOLEAN_VAR
>-----------
> 0
> 1
>
>SQL> select decode(boolean_var,0,FALSE,1,TRUE) from test;
>select decode(boolean_var,0,FALSE,1,TRUE) from test
> *
>ERROR at line 1:
>ORA-0904: invalid column name
>
>
>SQL> spool off
>
>
>Of course, this works properly if I enclose the words FALSE and TRUE in quotes
>but then they are treated as character data. I want to use this to populate
>a boolean variable based on a numeric table column (0 or 1). I still don't
>understand why columns of type boolean aren't suported.
>
>Is there anyway to do this or do I have to use an IF structure after selecting
>into some temporary numeric variables.
>
>
>Thx ahead of time.
>---
>*****************************************************************************
>* Dwayne K. King * email: dwayne_at_dwayne.freenet.kiev.ua *
>* International Monetary Fund * phone: (011-380-44) 212-54-81 *
>*****************************************************************************
Received on Wed Feb 14 1996 - 00:00:00 CET