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 <Stefan.Manegold@cwi.nlusers-list@monetdb.org <mailto:users-list@monetdb.org>Anthony Damico <ajdamico@gmail.com <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
http://mail.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list@monetdb.org
http://mail.monetdb.org/mailman/listinfo/users-list
_______________________________________________
users-list mailing list
users-list@monetdb.org
http://mail.monetdb.org/mailman/listinfo/users-list