title: Using UNION clause in OPENQUERY to query Oracle Linked Server with VBA
body: <p>I am considerably new to SQL and VBA. I am trying to execute the following Oracle SQL query using OPENQUERY in VBA to query a Linked Server:</p>
  
  <pre><code>with 
  untagged_affiliates as (
    select 
      ife.entityid,
      'Untagged affiliate' as TYPE_CHANGE,
      to_number(
        case when IFE.OLDVALUE is not null 
          then EXTRACTVALUE(xmltype(IFE.OLDVALUE), '/DocumentElement/ORG_AFFILIATIONS/AFFILIATED_ORGID')
          else null
        end) as RELATED_ID,
      ife.analystuserid as ANALYST,
      ife.auditdatetime
  
      from ife.audittrail ife
      where
        IFE.ATTRIBUTEID = 314
        and 
        ife.newvalue is null
        and 
        ife.oldvalue is not null
        and
        ife.AUDITDATETIME
        between 
        TO_DATE('27/11/2016 00:00:00','dd/mm/yyyy hh24:mi:ss') 
        and 
        TO_DATE('03/12/2016 23:59:59 ','dd/mm/yyyy hh24:mi:ss')
  ),
  untagged_unv_subs as (
    select 
      to_number(ife.oldvalue) as EntityID,
      'Untagged Unverified Sub' as TYPE_CHANGE,
      ife.entityid as RELATED_ID,
      ife.analystuserid as ANALYST,
      ife.auditdatetime
  
      from ife.audittrail ife
      left join oa.organizations o on ife.entityid = o.orgid
  
      where
        IFE.ATTRIBUTEID = 268
        and 
        ife.newvalue is null
        and 
        ife.oldvalue is not null
        and
        o.org_is_verified = 0
        and
        o.org_immediate_parent_orgid is null
        and
        ife.AUDITDATETIME
        between 
         TO_DATE('27/11/2016 00:00:00','dd/mm/yyyy hh24:mi:ss') 
        and 
        TO_DATE('03/12/2016 23:59:59 ','dd/mm/yyyy hh24:mi:ss')
  ),
  union_queries as
  (
    select * from untagged_affiliates
    union
    select * from untagged_unv_subs
  )
  select 
    uq.*,
    case
        when (L.LOCATION_NAME = 'Bangalore') then 'BLR'
        when (L.LOCATION_NAME = 'Beijing' or L.LOCATION_NAME = 'Singapore') then 'Asia'
        when (L.LOCATION_NAME = 'Gdansk' or L.LOCATION_NAME = 'Nicosia') then 'EMEA'
        else 'Other'
      end as REGION,
      C.COUNTRYDESC,
      u.USERID,
      u.username as ANALYST
    from union_queries uq
    left join oa.organizations o on uq.entityid = o.orgid
    left join admin.USERS U on UQ.ANALYST = U.USERID
    inner join admin.USERSUSERGROUPS ug on u.userid = ug.userid and ug.groupcode in (1,2,4,29,30,31,34,42,43,45,46,55,56)
    left join DOMAINS.COUNTRYCODES C on coalesce(o.org_country_of_domicile,o.org_country_of_incorporation) = C.COUNTRYCODE
    left join domains.TR_LOCATIONS l on u.PRIMARYLOCATION = l.LOCATION_ID
  </code></pre>
  
  <p>The corresponding VBA code I'm using to query the Linked Server is the following:</p>
  
  <pre><code>'SQL_String = "select * from openquery([OA],'with " &amp; _
  '"untagged_affiliates as (select ife.entityid, to_number( case when IFE.OLDVALUE is not null then EXTRACTVALUE(xmltype(IFE.OLDVALUE), ''/DocumentElement/ORG_AFFILIATIONS/AFFILIATED_ORGID'') else null end) as RELATED_ID, ife.analystuserid as ANALYST, ife.auditdatetime from ife.audittrail ife where IFE.ATTRIBUTEID = 314 and ife.newvalue is null and ife.oldvalue is not null and ife.AUDITDATETIME between TO_DATE(''" &amp; from_date_string &amp; " 00:00:00'',''dd/mm/yyyy hh24:mi:ss'') and TO_DATE(''" &amp; to_date_string &amp; " 23:59:59 '',''dd/mm/yyyy hh24:mi:ss'') ), " &amp; _
  '"untagged_unv_subs as (select to_number(ife.oldvalue) as EntityID, ife.entityid as RELATED_ID, ife.analystuserid as ANALYST, ife.auditdatetime from ife.audittrail ife left join oa.organizations o on ife.entityid = o.orgid where IFE.ATTRIBUTEID = 268 and ife.newvalue is null and ife.oldvalue is not null and o.org_is_verified = 0 and o.org_immediate_parent_orgid is null and ife.AUDITDATETIME between TO_DATE(''" &amp; from_date_string &amp; " 00:00:00'',''dd/mm/yyyy hh24:mi:ss'') and TO_DATE(''" &amp; to_date_string &amp; " 23:59:59 '',''dd/mm/yyyy hh24:mi:ss'') ), " &amp; _
  '"union_queries as (select * from untagged_affiliates union select * from untagged_unv_subs ) select uq.*, case when (L.LOCATION_NAME = ''Bangalore'') then ''BLR'' when (L.LOCATION_NAME = ''Beijing'' or L.LOCATION_NAME = ''Singapore'') then ''Asia'' when (L.LOCATION_NAME = ''Gdansk'' or L.LOCATION_NAME = ''Nicosia'') then ''EMEA'' else ''Other'' end as REGION, C.COUNTRYDESC, u.USERID, u.username as ANALYST " &amp; _
  '"from union_queries uq left join oa.organizations o on uq.entityid = o.orgid left join admin.USERS U on UQ.ANALYST = U.USERID inner join admin.USERSUSERGROUPS ug on u.userid = ug.userid and ug.groupcode in (1,2,4,29,30,31,34,42,43,45,46,55,56) left join DOMAINS.COUNTRYCODES C on coalesce(o.org_country_of_domicile,o.org_country_of_incorporation) = C.COUNTRYCODE left join domains.TR_LOCATIONS l on u.PRIMARYLOCATION = l.LOCATION_ID')"
  'recset.Open SQL_String, con
  'ThisWorkbook.Sheets("Untagged").Range("A2").CopyFromRecordset recset
  </code></pre>
  
  <p>From this, I'm getting the following error:
  <a href="https://i.stack.imgur.com/iq6oY.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/iq6oY.png" alt="enter image description here"></a></p>
  
  <p>As such, I suppose that I can't make a single query where two tables have columns with the same name and them use them for a UNION. But how do I go about to unite the tables if I can't assign the column ANALYST the same name on both?</p>