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

Question

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

Answer 1

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