On 1 Aug 2019, at 13:28, martin van dinther wrote:

query:

SELECT s, cast(case s when '' then null else s end as int) si FROM tst;

works. so you need to do the cast on all values of the column, not some.

Hai Martin, Thanks! That works. Jennie

On 01-08-19 13:15, Ying Zhang wrote:

...

CREATE TABLE tst (s string); INSERT INTO tst VALUES ('1234'), (''), (‘4567');

I’d like to cast ’s' to an INT column. The following query works:

sql>SELECT CAST(s AS INT) FROM (SELECT CASE WHEN s='' THEN NULL ELSE s END AS s FROM tst) AS tmp; +------+ | L4 | +======+ | 1234 | | null | | 4567 | +------+ 3 tuples

But the following query doesn’t:

sql>SELECT CASE WHEN s='' THEN NULL ELSE CAST(s AS INT) END FROM tst; conversion of string '' to type int failed. sql>SELECT CASE WHEN s='' THEN CAST(NULL as INT) ELSE CAST(s AS INT) END FROM tst; conversion of string '' to type int failed.

This is probably because batcalc.int is applied on the whole “s” column as shown by the query plan:

sql>EXPLAIN SELECT CASE WHEN s='' THEN NULL ELSE CAST(s AS INT) END FROM tst; +--------------------------------------------------------------------------------------------------------------------+ | mal | +====================================================================================================================+ … | C_7:bat[:oid] := sql.tid(X_6:int, "sys":str, "tst":str); | | X_10:bat[:str] := sql.bind(X_6:int, "sys":str, "tst":str, "s":str, 0:int); | | X_19:bat[:str] := algebra.projection(C_7:bat[:oid], X_10:bat[:str]); | | X_21:bat[:bit] := batcalc.==(X_19:bat[:str], "":str); | | X_24:bat[:bit] := batcalc.isnil(X_21:bat[:bit]); | | X_30:bat[:bit] := batcalc.ifthenelse(X_24:bat[:bit], false:bit, X_21:bat[:bit]); | | X_35:bat[:int] := batcalc.int(X_19:bat[:str]); | | X_36:bat[:int] := batcalc.ifthenelse(X_30:bat[:bit], nil:int, X_35:bat[:int]); | …

Is my query wrong or the plan?

Thanks, Jennie

_______________________________________________ users-list mailing list users-list@monetdb.org https://www.monetdb.org/mailman/listinfo/users-list