New type BOOLEAN

Sven W.

TPD-Opitz wrote:as I earlier wrote: I'd like this function:TO_BOOLEAN(the_legacy_value_to_convert, THE_LEGACY_TRUE_VALUES_AS_CSV_LIST)byeTPD

That would be pointless.

If Boolean were to be acceptable in SQL, then just doing

update tablename set column = (legacycolumn in (truevalue1, truevalue2, truevalue3...));

would do the job.  And CSV lists are evil, as we end up with the mother in chemotherapy issue.

As long as the expression evaluates to true or false, then there is no need for some 'special' TO_BOOLEAN function.

TO_BOOLEAN would make sense, if it is able to convert other data types into boolean.

E.g.

TO_BOOLEAN('TRUE') => true

TO_BOOLEAN(1) => true

TO_BOOLEAN('WAHR','nls_bool_language=GERMAN') => true

BluShadow

TO_BOOLEAN would make sense, if it is able to convert other data types into boolean.

E.g.

TO_BOOLEAN('TRUE') => true

TO_BOOLEAN(1) => true

TO_BOOLEAN('WAHR','nls_bool_language=GERMAN') => true

Why should Oracle know that the number 1 represents TRUE?

In binary terms true is represented by -1.

Surely what you're referring to should just be:

CAST('TRUE' AS BOOLEAN)

as we already have a CAST function for that.

Sven W.

The most typical number representations of TRUE/FALSE I have seen as database columns use 1 for TRUE and 0 for FALSE.

For example an "ISACTIVE" column in a table that controlls user access to some system. I never have seen that -1 is used for true in such a column.

As with the other datatypes there are conversion functions that allow us to specify things like formatting and nls dependencies. I believe if a boolean type is implemented it should follow the same rules. So there should be an explicit conversion functions (TO_BOOLEAN) and the possibility to CAST which would use default settings like NLS_LANG, NLS_BOOL_LANGUAGE, etc.Same goes for the conversion from number to boolean. Why not have a conversion function where you can specify, which value is considered true and which false? On the other hand we can do that using simple AND / OR / NOT operators. But also some common sense default values should apply. And the internal representation should not matter for such a common sense default representations. So I firmly believe 1=TRUE would be way better than -1=TRUE. But I might be biased in this (as is everybody else).

BluShadow

The most typical number representations of TRUE/FALSE I have seen as database columns use 1 for TRUE and 0 for FALSE.

For example an "ISACTIVE" column in a table that controlls user access to some system. I never have seen that -1 is used for true in such a column.

As with the other datatypes there are conversion functions that allow us to specify things like formatting and nls dependencies. I believe if a boolean type is implemented it should follow the same rules. So there should be an explicit conversion functions (TO_BOOLEAN) and the possibility to CAST which would use default settings like NLS_LANG, NLS_BOOL_LANGUAGE, etc.Same goes for the conversion from number to boolean. Why not have a conversion function where you can specify, which value is considered true and which false? On the other hand we can do that using simple AND / OR / NOT operators. But also some common sense default values should apply. And the internal representation should not matter for such a common sense default representations. So I firmly believe 1=TRUE would be way better than -1=TRUE. But I might be biased in this (as is everybody else).

Going back to the days of the early languages I learnt.. Assembly Language, some variants of Basic, C etc.  True was represented internally as -1.

That allowed us to do things like:

variable := variable + (1 AND <some condition>);

If the condition was true, then the Logical AND of -1 (all binary bits = 1) and 1 gives a value of 1.  If it was False, that was represented by zero, so the logical AND of 0 and 1 gives a value of 0.

So, -1 as TRUE in binary terms is the perfect logical way to operate with other values.

Jon Theriault

I would love to see this.  It would definitely make things easier to read. 

I'm not sure about the comments to have an automagic function to convert my old column types (whatever we used) to the new type.  Leave them as is and let the DBA and app developer upgrade the schema as needed.

It would also potentially make migrating from SQL Server easier since they have the BIT type that is translated to boolean by most clients.  (Oracle seems to love conversions from SQL Server so it seems worth mentioning )

Nikolaus Thiel

Going back to the days of the early languages I learnt.. Assembly Language, some variants of Basic, C etc.  True was represented internally as -1.

That allowed us to do things like:

variable := variable + (1 AND <some condition>);

If the condition was true, then the Logical AND of -1 (all binary bits = 1) and 1 gives a value of 1.  If it was False, that was represented by zero, so the logical AND of 0 and 1 gives a value of 0.

So, -1 as TRUE in binary terms is the perfect logical way to operate with other values.

So, -1 as TRUE in binary terms is the perfect logical way to operate with other values.

If that is the case, then the language does not have a native boolean type but uses binary integers to represent boolean types, so true = 111...1111 (depending on the bit length of your binary numbers).

Instead, I would rather prefer to introduce a new data type for boolean which could be view as binary terms of bit length 1.

In that case false is represented by the binary term 0 and true by 1.

BluShadow

So, -1 as TRUE in binary terms is the perfect logical way to operate with other values.

If that is the case, then the language does not have a native boolean type but uses binary integers to represent boolean types, so true = 111...1111 (depending on the bit length of your binary numbers).

Instead, I would rather prefer to introduce a new data type for boolean which could be view as binary terms of bit length 1.

In that case false is represented by the binary term 0 and true by 1.

Nikolaus Thiel wrote:So, -1 as TRUE in binary terms is the perfect logical way to operate with other values.If that is the case, then the language does not have a native boolean type but uses binary integers to represent boolean types, so true = 111...1111 (depending on the bit length of your binary numbers).Instead, I would rather prefer to introduce a new data type for boolean which could be view as binary terms of bit length 1.In that case false is represented by the binary term 0 and true by 1.

There's no "if that is the case" about it.  If you ever coded in Assembly language back in the 'old days' you'd know that everything is binary bits, and -1 was the representation for TRUE of a condition to allow for logical AND to be performed against another value.

The whole point though was that the request for a TO_BOOLEAN function above using TO_BOOLEAN(1) as an example assumes that BOOLEAN has a known integer value... which it doesn't.  What should it do if someone supplied TO_BOOLEAN(-1) ?  The assumption that TRUE and FALSE should always be represented by 1 and 0 is false in itself.  Not every language (or software developer) considers those two values to represent those two Booleans.

Nikolaus Thiel

Nikolaus Thiel wrote:So, -1 as TRUE in binary terms is the perfect logical way to operate with other values.

If that is the case, then the language does not have a native boolean type but uses binary integers to represent boolean types, so true = 111...1111 (depending on the bit length of your binary numbers).

Instead, I would rather prefer to introduce a new data type for boolean which could be view as binary terms of bit length 1.

In that case false is represented by the binary term 0 and true by 1.

There's no "if that is the case" about it.  If you ever coded in Assembly language back in the 'old days' you'd know that everything is binary bits, and -1 was the representation for TRUE of a condition to allow for logical AND to be performed against another value.

The whole point though was that the request for a TO_BOOLEAN function above using TO_BOOLEAN(1) as an example assumes that BOOLEAN has a known integer value... which it doesn't.  What should it do if someone supplied TO_BOOLEAN(-1) ?  The assumption that TRUE and FALSE should always be represented by 1 and 0 is false in itself.  Not every language (or software developer) considers those two values to represent those two Booleans.

The point of my comment was to use a new data type of bit length 1 for boolean.

Can you explain how a binary term of bit length can represent -1, except if you assume -1 = 1 ?

BluShadow

The point of my comment was to use a new data type of bit length 1 for boolean.

Can you explain how a binary term of bit length can represent -1, except if you assume -1 = 1 ?

Even a "bit" datatype is going to take at least the number of bytes that the o/s works at.  Nothing gets stored in less than a byte under normal conditions, even in C which is the underlying code of Oracle.

Jhonata E. Lamim

I vote for this, it would be nice to have boolean as column data type