0
SELECT
  e.cust_id AS customer_id,
  e.event_id AS event_id,
  max(t.updatedON)  as recent_timestamp
FROM
  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,
CASE 
  WHEN
    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
FROM
  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
WHERE
  e.event_id = car.event_id
  AND e.event_id = motorcycle.event_id
  AND e.event_id = walk.event_id
5
  • Are you getting the error in a Query Activity or in Query Studio? Jul 27 at 16:44
  • 1
    Pretty sure values is not supported in SFMC. Jul 27 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 :(
    – Jordan
    Jul 27 at 16:54
  • what's the third column -- car.updatedON, walk.updatedOn and __? Jul 27 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
    – Jordan
    Jul 27 at 18:15
3

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

select
  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 (
  select 
  max(c0.updatedon) updatedon
  from car c0 
  where e.cust_id = c0.cust_id 
  and e.event_id = c0.event_id
) c
outer apply (
  select 
  max(w0.updatedon) updatedon
  from walk w0 
  where e.cust_id = w0.cust_id 
  and e.event_id = w0.event_id
) w
outer apply (
  select 
  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.

6
  • 1
    Well poop, too slow yet again. I was just about to post something similar. Jul 27 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.
    – Jordan
    Jul 27 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 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.
    – Jordan
    Jul 27 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
    – Jordan
    Jul 27 at 19:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.