Re: Unable to use DECODE with boolean variables

dmoyer_at_gpu.com wrote:

>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'.

To complete the question, yes you will have to test the character strings returned in your application. Another point... If your application tool treats 0 and 1 as boolean then why don't you just return the values from the column?

>
>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 *
>>*****************************************************************************
>

_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ Gordon E. Hooker MACS PCP
25 Clarke Street Ripley Queensland Australia Phone 61-7-32940555
Email gordonh_at_acslink.net.au
_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/_/ We are just two lost souls living in a fish bowl Year after year

Pink Floyd Received on Mon Feb 19 1996 - 00:00:00 CET