7

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

leftjoin
  • 36,950
  • 8
  • 57
  • 116
joey
  • 115
  • 1
  • 1
  • 10
  • Please [edit] and format this wall of unreadable text. –  May 10 '18 at 22:06
  • 3
    figured out, just replace cast with try_cast – joey May 10 '18 at 22:22
  • 1
    I believe the problem isn't `null`, but is that you have an empty string. – Dain Sundstrom May 11 '18 at 00:38
  • 1
    @ Dain Sundstrom yea,you are right! I have empty string so I put coalesce to transfer those empty string into -999999, but still not working.. so I finally use try_cast, which resolved the problem. – joey May 11 '18 at 17:27

1 Answers1

4

TRY_CAST will return null if cast fails:

TRY_CAST(json_extract_scalar(json, '$.user_id') as BigInt) user_id
leftjoin
  • 36,950
  • 8
  • 57
  • 116