Query Error: Old style JOIN (ANSI JOINs) syntax is not allowed

Question

I'm trying to build a Segment Count history table. There's three columns in this table. I want to insert 5 rows daily. The code below worked (minus createdate). I needed to add CreateDate so as not to violate the primary key. After making CreateDate a primary key i ran into the error:

Errors: Old style JOIN (ANSI JOINs) syntax is not allowed."

I think this can work with a tweak to my code - I just don't know what.

Columns

Segment Name (key)
Segment Count
Create Date (key - default getdate())

Code

select 
'Appointed Advisors' as [Segment Name]
, (select count(*) from [Advisors - Appointed] ) as [Segment Count]
, getdate() as [CreateDate]

union

select 
'Appointed Advisors - Engaged 30 days' as [Segment Name]
, (select count(*) from [Advisors - Engaged 30 days] ) as [Segment Count]
, getdate() as [CreateDate]

union

select 
'Appointed Advisors - Engaged 60 Days' as [Segment Name]
, (select count(*) from [Advisors - Engaged 60 Days] ) as [Segment Count]
, getdate() as [CreateDate]

union

select 
'Appointed Advisors - Unengaged 60 days' as [Segment Name],
, (select count(*) from [Advisors - Unengaged 60 days] ) as [Segment Count]
, getdate() as [CreateDate]

union

select 
'Appointed Advisors - Producers' as [Segment Name]
, (select count(*) from [Advisors - Producers] ) as [Segment Count]
, getdate() as [CreateDate]

Answer 1

Why not just select the counts directly from the tables and then UNION the results of each? The ANSI JOINs aren't necessary if you go this route.

Also, it'd probably be a good idea to make the UNIONs an inner query and set the CreateDate in an outer query, so all of the resulting rows have the same date/time.

select 
  x.[segment name]
, x.[segment count]
, getDate() CreateDate

from (

    select 
    'Appointed Advisors' as [Segment Name]
    , count(*) as [Segment Count]
    from [Advisors - Appointed]

    union

    select 
    'Appointed Advisors - Engaged 30 days' as [Segment Name]
    , count(*) as [Segment Count]
    from [Advisors - Engaged 30 days]

    union

    select 
    'Appointed Advisors - Engaged 60 days' as [Segment Name]
    , count(*) as [Segment Count]
    from [Advisors - Engaged 60 days]

    union

    select 
    'Appointed Advisors - Unengaged 60 days' as [Segment Name]
    , count(*) as [Segment Count]
    from [Advisors - Unengaged 60 days]

    union

    select 
    'Advisors - Producers' as [Segment Name]
    , count(*) as [Segment Count]
    from [Advisors - Producers]
) x