Re: should I be able to CAST a BOOLEAN type to a DOUBLE ( zero-one ) variable?

Sneaky. Perfect.. Thank you!!! On Thu, May 2, 2013 at 4:51 PM, Martin Kersten wrote:

Not all direct type cast are supported. In particular, this one is not. You can use the following hack

select (cast (cast (am > 0 as integer) as double)...

regards, Martin

On 5/2/13 10:42 PM, Anthony Damico wrote:

...

Thanks Stefan.. should these commands have worked, then? They do not for me.. Sorry if I'm making a beginner's mistake..

sql>select cast( ( am > 0 ) as double ) as colname from mtcars; types boolean(1,0) and double(53,0) are not equal sql>select cast( ( am > 0 ) as double precision ) as colname from mtcars; types boolean(1,0) and double(53,0) are not equal sql>explain select cast( ( am > 0 ) as double ) as colname from mtcars; types boolean(1,0) and double(53,0) are not equal sql>plan select cast( ( am > 0 ) as double ) as colname from mtcars; types boolean(1,0) and double(53,0) are not equal sql>select cast( TRUE as double ) as colname from mtcars; types boolean(1,0) and double(53,0) are not equal sql>explain select cast( TRUE as double ) as colname from mtcars; types boolean(1,0) and double(53,0) are not equal sql>plan select cast( TRUE as double ) as colname from mtcars; types boolean(1,0) and double(53,0) are not equal sql>

On Thu, May 2, 2013 at 4:24 PM, Stefan Manegold mailto:Stefan.Manegold@cwi.nl**> wrote:

cast syntax is "cast( <expression> as type )"

Anthony Damico mailto:ajdamico@gmail.com> wrote:

I tried lots of different things (pasted below) and none of them worked.

I ended up using a CASE statement which worked around the issue, but I would've thought something like the command..

SELECT ( ( COLNAME > 0 )*1.000 ) AS ANY_VALUE FROM TABLENAME ;

..should give a valid response instead of an error? But I'm not sure if MonetDB wants to support this type of CAST/CONVERT operation? (None of the things I tried worked, not just the on-the-fly multiplication by 1.000).

Using Feb2013-SP2 on Windows x86_64

If MonetDB should be able to convert TRUE/FALSE values to numerics, I'll submit a bug report with some PLAN and EXPLAIN results? :)

Thanks for the great software!!

sql>select ( mpg > 10 ) from mtcars ; +-------+ | >_mpg | +=======+ | null | | null | | true | | null | | true | | null | | true | | true | | true | | null | | null | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | null | | true | | true | +-------+ 32 tuples (15.010ms) sql>select ( mpg > 10 )*1.000 from mtcars ; TypeException:user.s11_2[15]:'**calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s11_2[17]:'**bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg > 10 )*1.000 ) from mtcars ; TypeException:user.s12_2[15]:'**calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s12_2[17]:'**bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg >= 10 )*1.000 ) from mtcars ; TypeException:user.s13_2[15]:'**calc.sht' undefined in: _59:any := calc.sht(_57:bi t, _22:int, _23:int) TypeException:user.s13_2[17]:'**bat.insert' undefined in: _62:any := bat.insert(_5 2:bat[:oid,:sht], _60:oid, _59:any) program contains errors sql>select ( ( mpg >= 10 ) ) from mtcars ; +--------+ | >=_mpg | +========+ | null | | null | | true | | null | | true | | null | | true | | true | | true | | null | | null | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | true | | null | | true | | true | +--------+ 32 tuples (103.019ms) sql>select CAST( ( mpg >= 10 ) ) as DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( ( mpg >= 10 ) )" sql>select CAST( ( mpg >= 10 ) ) AS DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( ( mpg >= 10 ) )" sql>select CAST( mpg >= 10 ) AS DOUBLE from mtcars ; syntax error, unexpected ')', expecting AS in: "select cast( mpg >= 10 )" sql>select ( CAST( mpg >= 10 ) AS DOUBLE ) from mtcars ; syntax error, unexpected ')', expecting AS in: "select ( cast( mpg >= 10 )" sql>select ( CAST( mpg >= 10 AS DOUBLE ) from mtcars ; syntax error, unexpected FROM, expecting ')' in: "select ( cast( mpg >= 10 as do uble ) from" sql>select ( CAST( mpg >= 10 AS DOUBLE ) as x from mtcars ; syntax error, unexpected AS, expecting ')' in: "select ( cast( mpg >= 10 as doub le ) as" sql>select ( CAST( mpg >= 10 ) AS DOUBLE as x from mtcars ; syntax error, unexpected ')', expecting AS in: "select ( cast( mpg >= 10 )" sql>select CONVERT( double , mpg > 10 ) as x from mtcars; syntax error, unexpected sqlDOUBLE in: "select convert( double" sql>select CAST( TRUE as double ) from mtcars ; types boolean(1,0) and double(53,0) are not equal sql>select CAST( TRUE*1.000 as double ) from mtcars ; TypeException:user.s15_2[11]:'**calc.sht' undefined in: _21:any := calc.sht(_20:bi t, _22:int, _23:int) program contains errors sql>select CAST( 'TRUE' as bit ) as z from mtcars; type (bit) unknown in: "select cast( 'TRUE' as bit )" syntax error, unexpected AS in: "as" sql>select CAST( TRUE as bit ) as z from mtcars; type (bit) unknown in: "select cast( true as bit )" syntax error, unexpected AS in: "as" sql>select CAST( TRUE as double ) as z from mtcars; types boolean(1,0) and double(53,0) are not equal sql> ______________________________**_________________ users-list mailing list users-list@monetdb.org mailto:users-list@monetdb.org** http://mail.monetdb.org/**mailman/listinfo/users-listhttp://mail.monetdb.org/mailman/listinfo/users-list

______________________________**_________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/**mailman/listinfo/users-listhttp://mail.monetdb.org/mailman/listinfo/users-list

______________________________**_________________ users-list mailing list users-list@monetdb.org http://mail.monetdb.org/**mailman/listinfo/users-listhttp://mail.monetdb.org/mailman/listinfo/users-list