SQL code error mismatched input 'from' expecting

Aimee picture Aimee · Sep 19, 2018 · Viewed 24.5k times · Source

Hi below is my SQL code and it gives me error and I do not know why. If anyone can help.

select time_dif, count(time_dif) as count 
from 
(
select datediff(so_so_close_time,date_closed) as time_dif
from `mbg_service_prd`.`mds_service_orders_base_cdl`
inner join `mbg_service_prd`.`rnt_incident_detail_base_cdl`
on 
(srv_customer_phone = mobile_phone or srv_customer_email = email_address)
where (
(srv_customer_phone<>''or srv_customer_phone is not null)
or (srv_customer_email<>'' or srv_customer_email is not null) 
or (mobile_phone<>'' or mobile_phoneis not null) 
or (email_addressis<>'' or email_addressis not null) 
)
)
group by time_dif
order by time_dif

It gives me error says: org.apache.spark.sql.catalyst.parser.ParseException: mismatched input 'from' expecting {, 'WHERE', 'GROUP', 'ORDER', 'HAVING', 'LIMIT', 'LATERAL', 'WINDOW', 'UNION', 'EXCEPT', 'INTERSECT', 'SORT', 'CLUSTER', 'DISTRIBUTE'}(line 3, pos 0)

Answer

Naresh picture Naresh · Sep 19, 2018

Try with the below query

select a.time_dif, count(a.time_dif) as time_dif_count 
from 
(
select datediff(day,so_so_close_time,date_closed) as time_dif
from mbg_service_prd.mds_service_orders_base_cdl
inner join mbg_service_prd.rnt_incident_detail_base_cdl
on 
(srv_customer_phone = mobile_phone or srv_customer_email = email_address)
where (
(srv_customer_phone <> '' or srv_customer_phone is not null)
or (srv_customer_email <> '' or srv_customer_email is not null) 
or (mobile_phone <> '' or mobile_phone is not null) 
or (email_address <> '' or email_address is not null) 
)
)a
group by a.time_dif
order by a.time_dif