Fully managed cloud. 30-day FREE full featured trial. Start Now
cancel
Showing results for 
Search instead for 
Did you mean: 

I can't change the data type of this field

rodrigogargiulo
Contributor

When I try to change the data type from VARCHAR to INTEGER I get the error:

SQL Error [22018]: data exception - invalid character value for cast; Value: ' ' in cast of column 

 

I have not been able to solve it

field: SESSIONS_COUNT_DAY_180

1 ACCEPTED SOLUTION

Accepted Solutions

PeterK
Xpert

Exasol is strict about datatypes matching across all UNION subqueries.

I couldn't say which of your columns is causing the error without seeing the data but my guess is that it's because max(SESSIONS_UNIQUE_USERS_DAY_179) AS SESSIONS_UNIQUE_USERS and max(CAST( REGEXP_REPLACE(SESSIONS_UNIQUE_USERS_DAY_180, '[^0-9]','') AS INT )) AS SESSIONS_UNIQUE_USERS are VARCHAR and INTEGER respectively. The SUM columns are OK because SUM coerces VARCHAR into INT but MAX does not.

So I suspect that if you CAST SESSIONS_UNIQUE_USERS_DAY_179 as an INT it would work.

Regards

Peter

View solution in original post

5 REPLIES 5

PeterK
Xpert

Hi @rodrigogargiulo ,

If those field values were actually NULL (or empty string) the cast should work fine so I would suspect that they are not actually NULL. Perhaps they are tabs/CHR(9) ?

Try running SELECT UNICODE(sessions_count_day_180) FROM stg_cohort_data_dev  and see what value is shown for those blank fields. The cast to INT will only work if they are actually NULL.

Regards,

Peter

rodrigogargiulo
Contributor

Thanks @PeterK 

I get this result when I perform the query, is there any way to solve it from there?

PeterK
Xpert

Hi @rodrigogargiulo ,

It appears the fields have a carriage return (ASCII 13) in them which explains the casting error.

There are several ways you could fix it depending on how careful you want to be with the data.

If you just want to blindly ignore all non-numeric characters you could do:

 CAST( REGEXP_REPLACE(session_count_day_180, '[^0-9]','') AS INT )

If you want to only skip that specific case you could do:
CAST(NULLIF(session_count_day_180, CHR(13)) AS INT)

Regards,

Peter

rodrigogargiulo
Contributor

Thanks @PeterK 

If I get this error when I make a union. Is it not possible to make union like this?

 

 

 

 

 

Query:

SELECT APP_ID AS APP_ID ,
PLATFORM AS PLATFORM ,
DATE_COHORT AS 'DATE',
GEO AS GEO,
CHANNEL AS CHANNEL,
SUM(USERS) AS USERS,
'SESSIONS_COUNT_DAY_179' AS Measure_S_COUNT,
sum(SESSIONS_COUNT_DAY_179) AS SESSIONS_COUNT,
'SESSIONS_RATE_DAY_179' AS Measure_S_RATE,
max(SESSIONS_RATE_DAY_179) AS SESSION_RATE,
'SESSIONS_UNIQUE_USERS_DAY_179' AS Measure_S_U_Users,
max(SESSIONS_UNIQUE_USERS_DAY_179) AS SESSIONS_UNIQUE_USERS
FROM APPSFLYER_DEV.STG_COHORT_DATA_DEV
GROUP BY 1,2,3,4,5
UNION ALL
SELECT APP_ID AS APP_ID ,
PLATFORM AS PLATFORM ,
DATE_COHORT AS 'DATE',
GEO AS GEO,
CHANNEL AS CHANNEL,
SUM(USERS) AS USERS,
'SESSIONS_COUNT_DAY_180' AS Measure_S_COUNT,
sum(CAST( REGEXP_REPLACE(SESSIONS_COUNT_DAY_180, '[^0-9]','') AS INT )) AS SESSIONS_COUNT,
'SESSIONS_RATE_DAY_180' AS Measure_S_RATE,
max(CAST( REGEXP_REPLACE(SESSIONS_RATE_DAY_180, '[^0-9]','') AS DECIMAL(18,0) )) AS SESSION_RATE,
'SESSIONS_UNIQUE_USERS_DAY_180' AS Measure_S_U_Users,
max(CAST( REGEXP_REPLACE(SESSIONS_UNIQUE_USERS_DAY_180, '[^0-9]','') AS INT )) AS SESSIONS_UNIQUE_USERS
FROM APPSFLYER_DEV.STG_COHORT_DATA_DEV
GROUP BY 1,2,3,4,5;

PeterK
Xpert

Exasol is strict about datatypes matching across all UNION subqueries.

I couldn't say which of your columns is causing the error without seeing the data but my guess is that it's because max(SESSIONS_UNIQUE_USERS_DAY_179) AS SESSIONS_UNIQUE_USERS and max(CAST( REGEXP_REPLACE(SESSIONS_UNIQUE_USERS_DAY_180, '[^0-9]','') AS INT )) AS SESSIONS_UNIQUE_USERS are VARCHAR and INTEGER respectively. The SUM columns are OK because SUM coerces VARCHAR into INT but MAX does not.

So I suspect that if you CAST SESSIONS_UNIQUE_USERS_DAY_179 as an INT it would work.

Regards

Peter