CODEWITHSUNDEEP
sqlcastingtype-conversionprestotrino
joey
joey

Reputation: 115

Cannot cast '' to bigint in presto

I tried to clear 'null' in my query, but there is still error when run it, keep telling me that 'cannot cast '' to bigint, is there any way to fix it?

myquery

select m.app, m.hour,m.user_id, 
 m.avg_minutes_watched, n.userid, n.watched_mins, n.active FROM 

(SELECT app,
substr(hour,1,8) hour, 
CAST(COALESCE(json_extract_scalar(json, '$.user_id'), '-999999') as BigInt) user_id,
CAST(COALESCE(json_extract_scalar(json, '$.playback_time'), '-999999') as BigInt) /60000 avg_minutes_watched
FROM prod
WHERE event_type = 'user_session_complete' AND hour > '20180331' and hour < '20180501')m

left join

(select userid, watched/60000 watched_mins,
(case when watched/60000 >= 2 then 'active' else 'not_active' end) active  from est where realdate > '2018-03-31' and realdate < '2018-05-01') n

on m.user_id = n.userid
order by m.hour, m.user_id;

error

Query 20180510_220127_17857_bxg5s, FAILED, 72 nodes
Splits: 5,178 total, 644 done (12.44%)
0:04 [39.2M rows, 1.93GB] [9.32M rows/s, 469MB/s]

Query 20180510_220127_17857_bxg5s failed: Can not cast '' to BIGINT

Upvotes: 7

Views: 25714

Answers (1)

leftjoin
leftjoin

Reputation: 38290

TRY_CAST will return null if cast fails:

TRY_CAST(json_extract_scalar(json, '$.user_id') as BigInt) user_id

Upvotes: 4

Related Questions