e.cust_id AS customer_id,
  e.event_id AS event_id,
  max(t.updatedON)  as recent_timestamp
  event_table AS e
  INNER JOIN car AS c ON e.cust_id = c.cust_id and e.event_id = c.event_id
  INNER JOIN walk AS w ON e.cust_id = w.cust_id and e.event_id = w.event_id
  CROSS APPLY (values(c.updated_On),(w.updated_on)) as t(updatedOn)
  group by e.cust_id, e.event_id

Error: An error occurred while checking the query syntax. Errors: The values(values(c.updated_On table has a multi-part specification. This is not allowed. values(values(c.updated_On is not a known data extension or system data view. You can only query existing data extensions or system data views.

The three data extensions event_table, car and walk do exist. Any ideas what i can do to resolve?

EDIT: Included sample data and desired output here.

EDIT 2: The accepted answer below is correct and should remain for helping others. Since the implementation however changed OUTER APPLY to act as INNER JOIN, I did some cleaning:

SELECT e.cust_id, e.event_id,
    car.updated_on >= motorcycle.updated_on and car.updated_on >= walk.updated_on
      THEN car.updated_on
  WHEN motorcycle.updated_on >= car.updated_on
      THEN motorcycle.updated_on
  ELSE walk.updated_on
END as updated_on
  event_table AS e
  INNER JOIN car AS car ON e.cust_id = car.cust_id
  INNER JOIN motorcycle AS motorcycle ON e.cust_id = motorcycle.cust_id
  INNER JOIN walk AS walk ON e.cust_id = walk.cust_id
  e.event_id = car.event_id
  AND e.event_id = motorcycle.event_id
  AND e.event_id = walk.event_id
  • Are you getting the error in a Query Activity or in Query Studio? Jul 27, 2021 at 16:44
  • 1
    Pretty sure values is not supported in SFMC. Jul 27, 2021 at 16:44
  • Hey Adam, thanks for respond. I am doing this in Automation Studio > Query Activity. All i want to do is get the GREATEST date of three columns and i can't figure out how :(
    – user80446
    Jul 27, 2021 at 16:54
  • what's the third column -- car.updatedON, walk.updatedOn and __? Jul 27, 2021 at 17:47
  • motorcycle.updatedOn, sorry. I simplified it for easier example. Same structure as the other two tables. Just one more complexity to since there's 3 tables to consider instead of two. Car, Motorcycle and Walk
    – user80446
    Jul 27, 2021 at 18:15

1 Answer 1


I only see two updatedON values. If you only need to find the greatest of those two then you could do something like this:

  e.cust_id customer_id
, e.event_id event_id
, case 
    when c.updatedon >= w.updatedon 
         and c.updatedon >= m.updatedon then c.updatedon
    when w.updatedon >= m.updatedon then w.updatedon
    else m.updatedon 
  end recent_timestamp
from event_table e
outer apply (
  max(c0.updatedon) updatedon
  from car c0 
  where e.cust_id = c0.cust_id 
  and e.event_id = c0.event_id
) c
outer apply (
  max(w0.updatedon) updatedon
  from walk w0 
  where e.cust_id = w0.cust_id 
  and e.event_id = w0.event_id
) w
outer apply (
  max(m0.updatedon) updatedon
  from motorcycle m0 
  where e.cust_id = m0.cust_id 
  and e.event_id = m0.event_id
) w
where m.updatedOn is not null
and w.updatedOn is not null
and c.updatedOn is not null

The OUTER APPLYs are acting like LEFT JOINs but with the criteria inside.

  • 1
    Well poop, too slow yet again. I was just about to post something similar. Jul 27, 2021 at 17:56
  • Is OUTER APPLY/LEFT JOIN correct in this instance? I only want the result set that exists in all tables. E.g. the cust_id and event_id needs to exist in 1) event_table, 2) car, 3) walk and if we're talking about the last case then also in motorcycle which is same structure as car or walk.
    – user80446
    Jul 27, 2021 at 18:19
  • Updated my answer. Adding another apply for motorcycle. The CASE is a little more complex, but doable with 3 values. The WHERE forces the the LEFT JOIN to only include matches in all three. Jul 27, 2021 at 18:40
  • Hey Adam, thanks again. Let me try this one, the previous one did not work as expected for my test data. Will see how this one works, I did add a link to test data I am using if it add clarity. Thanks for taking a look.
    – user80446
    Jul 27, 2021 at 19:02
  • I can't edit your post. There is an alias typo on the third outer apply. I believe it should be m instead of w. It worked like a charm. Thanks so much. Very good use of WHERE` to force a LEFT JOIN into an INNER JOIN. That did not occur me. Thanks so much, especially with the limitation around VALUES
    – user80446
    Jul 27, 2021 at 19:13

You must log in to answer this question.