Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 63 Insurance
- 536.4K On-Premises Infrastructure
- 138.3K Analytics Software
- 38.6K Application Development Software
- 5.8K Cloud Platform
- 109.5K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.3K Integration
- 41.6K Security Software
New type BOOLEAN
Comments
-
It's fairly heavy going I admit, especially as it get's into the mathematics of implied logic.
Not sure how that would change Tom's suggestion to use 0 and 1 etc.
Jeez, now you've even got me questioning it. LOL! Gonna have to go and clarify things for myself again now.....
BluShadow wrote: Not sure how that would change Tom's suggestion to use 0 and 1 etc.
Well, if Boolean logic is fundamentally invalidated by null values then that will be a problem regardless of what symbols we use for TRUE and FALSE.
-
To be honest, there's little point in me regurgitating what is already out there on the web.
Joe Celko's article generally explains the issue (though he's made a couple of mistakes in the article - they can be overlooked)
And as Tom has pointed out many times in the past on AskTom, those RDBMS that do implement Boolean types for SQL, are simply defining true and false as 0 and 1 (or suchlike) in the background, so there's not much point in it. If people are desperate to have a true/false indicator, then they could implement their own custom type (with appropriate methods for true and false) and create their columns based on that type. Ultimately, the values are still going to take up a byte, or word etc. as there is no concept of just storing bits.
And apologies for my previous mistake, I think it was SQL-99 when Boolean was introduced to the standards, not SQL-92.
BluShadow wrote: If people are desperate to have a true/false indicator, then they could implement their own custom type (with appropriate methods for true and false) and create their columns based on that type.
We would have to create an object type column and then change our queries from
where t.expired = 'Y'to
where t.expired.truefalse = 'TRUE'which I don't really see helping.
-
I finally made my mind up about this. And Yes it would be useful.
I don't think I would need it for columns. But I would like to be able to call plsql functions that have boolean input parameters or a boolean return type.
pseudo examples
select * from dual where func(1,2,true) = dummy;
or even
select case when funcHireOrFire(dummy) = true then 'hire him' else 'fire him' end from dual
or shorter
select case when funcHireOrFire(dummy) then 'hire him' else 'fire him' end from dual
-
I finally made my mind up about this. And Yes it would be useful.
I don't think I would need it for columns. But I would like to be able to call plsql functions that have boolean input parameters or a boolean return type.
pseudo examples
select * from dual where func(1,2,true) = dummy;
or even
select case when funcHireOrFire(dummy) = true then 'hire him' else 'fire him' end from dual
or shorter
select case when funcHireOrFire(dummy) then 'hire him' else 'fire him' end from dual
Agreed, SQL support for Booleans in PL/SQL procedures and functions would be helpful even without Boolean columns. Don't you have indicator/flag/status columns though? What I always do if I'm designing them is suffix them with _yn and add a check constraint, but I see all kinds of variations. A Boolean column would be absolutely unequivocal and impossible to mess up or misinterpret.
The syntax might need an IS TRUE / IS FALSE operator, or maybe just TRUE/FALSE and regular equality syntax, I'm not sure.
-
I agree with the examples given by Sven W. and I fully agree with William Robertson.
I'd love to be able to use boolean columns as it improves the readability of your code and it prevents mistakes as it cannot be misinterpreted.
If you have a reason not to use it, just don't. But now, if you want to use it, you simply can't. Why should someone else (partly) decide what my design should look like?
-
It is TRUE (not 1 or 'T') that this feature would be very nice
-
The nicest use case for BOOLEAN types is (as always) to perform set operations on them, like BOOL_AND() and BOOL_OR() aggregations. BOOL_AND() is the same as EVERY(). PostgreSQL has it. I've blogged about this here: [ Link to blog removed by moderator (BluShadow) - promoting blogs is not permitted]
-
The nicest use case for BOOLEAN types is (as always) to perform set operations on them, like BOOL_AND() and BOOL_OR() aggregations. BOOL_AND() is the same as EVERY(). PostgreSQL has it. I've blogged about this here: [ Link to blog removed by moderator (BluShadow) - promoting blogs is not permitted]
while it would be really nice to have explicit boolean analytics functions we could sumulate or() with regular max() and and() with regular min() function...
bye
TPD
-
Would be nice to have. Varchar NULL = 0 (no) NOT NULL = 1 (YES)?
-
Would be nice to have. Varchar NULL = 0 (no) NOT NULL = 1 (YES)?
BCG14 wrote:Would be nice to have. Varchar NULL = 0 (no) NOT NULL = 1 (YES)?
I disagree.
That's yet another lame workaround for the missing boolean type in SQL.
bye
TPD
