On 16-07-15 11:39, Dennis Wiest wrote:

Hello,

I have a question for the Operation Cast and CONVERT.

I have the following table and wants to run this query.

select * from value v2 WHERE v2.xpath_id = 378 AND v2.value BETWEEN 1000 AND 2000 LIMIT 10;

This provides me with the wrong result because v2.value is a textfield.

Conversion of a text value to a number can fail as not all strings represent an (integer) number. It will result in a runtime error as observed.
Instead compare strings with strings as in:

select * from value v2 WHERE v2.xpath_id = 378
AND length(v2.value) = 4
AND ((substring(v2.value, 1, 1) between '1' AND '1'
   AND substring(v2.value, 2, 1) between '0' AND '9'
   AND substring(v2.value, 3, 1) between '0' AND '9'
   AND substring(v2.value, 4, 1) between '0' AND '9')
OR (v2.value = '2000')) LIMIT 10;

This avoids casting and conversion errors.

Alternatively do:
ALTER TABLE value ADD COLUMN value_int int NULL;

UPDATE value set value_int = cast(value as int)
WHERE value IS NOT NULL
AND length(value) = 4
AND substring(value, 1, 1) between '0' AND '9'
AND substring(value, 2, 1) between '0' AND '9'
AND substring(value, 3, 1) between '0' AND '9'
AND substring(value, 4, 1) between '0' AND '9';

add next use query:
select * from value v2 WHERE v2.xpath_id = 378 AND v2.value_int BETWEEN 1000 AND 2000 LIMIT 10;

If you do not want to change the table with adding a column, use a view:
CREATE VIEW value_ints_only AS
SELECT id, element_id, xpath_id, value, cast(value as int) as value_int FROM value
WHERE value IS NOT NULL
AND length(value) = 4
AND substring(value, 1, 1) between '0' AND '9'
AND substring(value, 2, 1) between '0' AND '9'
AND substring(value, 3, 1) between '0' AND '9'
AND substring(value, 4, 1) between '0' AND '9';

select * from value_ints_only v2
WHERE v2.xpath_id = 378
AND v2.value_int BETWEEN 1000 AND 2000
LIMIT 10;

Now I wanted to with the following query to fix this problem, but I get an error. Is it at all possible to the cast or convert?

select * from value v2 WHERE v2.xpath_id = 378 AND CAST(CAST(v2.value AS float)AS integer) > 1000 LIMIT 10;

conversion of string 'RNAV' to type dbl failed.

But this one Works

select CAST(CAST(v2.value AS float)AS integer) from value v2 WHERE v2.xpath_id = 378 LIMIT 10;

probably the first 10 values could be converted to integers, but not all.
If you remove the LIMIT 10 it will give you the same error as above.

The problem is, “value” must be a text field, “value” can take all possible values.

CREATE TABLE value (

id int NOT NULL AUTO_INCREMENT,

element_id int NOT NULL,

xpath_id int NOT NULL,

value text,

PRIMARY KEY (id)

);

Thanks 4 Help

Greetings Dennis

Avitech GmbH
Principal Office: Bahnhofplatz 1 | 88045 Friedrichshafen | Germany
Court Registration: Amtsgericht Ulm | HRB 728293
Geschäftsführer/Managing Director: Antonio Maria Gonzalez Gorostiza
VAT No.: DE223719716
http://avitech.aero
_______________________________________________
users-list mailing list
users-list@monetdb.org
https://www.monetdb.org/mailman/listinfo/users-list