I have a field in a view that is obtained by extracting an value from an XML encoded string. When I inspect the view I notice that it has a type of CLOB. I looked in the documentation for CAST and found that I should be able to cast it as an Int (which is what the field should be outside of the XML). However when I try to select the field value and cast it, like this
[
 select cast(capacity as int) from tables
]
I receive
[
 ElevateDB Error #700 An error was found in the statement at line 1 and column 13 (Expected Char, VarChar, Byte, VarByte, Boolean, SmallInt, Integer, BigInt, Float, Decimal, Interval Year, Interval Day,      Interval Hour, Interval Minute, Interval Second, or Interval MSecond expression but instead found   "capacity")
].

I am extremely new to ElevateDB and am used to working with MySQL/MS SQL, so please excuse any ignorance.

Any suggestions would be greatly appreciated.

mwhobrey


>I have a field in a view that is obtained by extracting an value from an XML encoded string. When I inspect the view I notice that it has a type of CLOB. I looked in the documentation for CAST and found that I should be able to cast it as an Int (which is what the field should be outside of the XML). However when I try to select the field value and cast it, like this
>[
> select cast(capacity as int) from tables
>]

There are two things here. First you need to use integer rather than int, Secondly, ElevateDB doesn't believe in converting CLOBs or BLOBs to numbers so you need to convert it to something its happy with first.

Try

SELECT CAST(CAST(capacity AS VARCHAR(20)) AS INTEGER) FROM tables

Roy Lambert