CODEWITHSUNDEEP
sqldatesnowflake-cloud-data-platformunion-allyear2038
User1011
User1011

Reputation: 135

i've to union two different tables sym33 and sym and then check the difference of max svcdate and min svcdate which should be greater then 90 days

create temporary table sand_scipher_ds_db.public.Rf as (
  select PATIENTID, max(SVCDATE)-min(SVCDATE) as days from symphony_hvh_db.sym33.dx

  union all
  
  select PATIENTID, max(SVCDATE)-min(SVCDATE) as days from symphony_hvh_db.sym.dx
  group by PATIENTID
  having days>90
  limit 100
);
select * from sand_scipher_ds_db.public.Rf limit 100

error: SQL compilation error: [DX.PATIENTID] is not a valid group by expression

Upvotes: 1

Views: 54

Answers (1)

Mike Walton
Mike Walton

Reputation: 7369

You need a group by on both sides of your UNION ALL as this is failing because you are using an aggregate function against sym33.dx without specifying a group by. Therefore, PATIENTID is not a valid group by expression in the first select.

create temporary table sand_scipher_ds_db.public.Rf as (
  select PATIENTID, max(SVCDATE)-min(SVCDATE) as days from symphony_hvh_db.sym33.dx
  group by PATIENTID
  having days>90

  union all
  
  select PATIENTID, max(SVCDATE)-min(SVCDATE) as days from symphony_hvh_db.sym.dx
  group by PATIENTID
  having days>90
  limit 100
);
select * from sand_scipher_ds_db.public.Rf limit 100

It's also possible that you wanted to union all and then do the group by. You'd need to do a sub-select like this:

create temporary table sand_scipher_ds_db.public.Rf as (
  select a.PATIENTID, max(a.SVCDATE)-min(a.SVCDATE) as days from
  ( 
    select PATIENTID, SVCDATE from symphony_hvh_db.sym33.dx
    union all
    select PATIENTID, SVCDATE from symphony_hvh_db.sym.dx
  ) a
  group by PATIENTID
  having days>90
  limit 100
);
select * from sand_scipher_ds_db.public.Rf limit 100

Upvotes: 1

Related Questions