Set a variable value in Query Service

[akwankl]

Hi,

I am looking to query the latest snapshot from one of the profile_snapshot_export_xxx datasets, so the variable/parameter cannot be hardcoded.

As a result, I need to do a subquery first, find the snapshot_id from the latest made_current_at date, and then store the value into a variable to be used by the main query.

The subquery looks like this:

select snapshot_id FROM
  (
  SELECT row_number() OVER (ORDER BY made_current_at desc) `rn`, made_current_at, snapshot_id, parent_id 
    from (
          select history_meta('profile_snapshot_export_xxx') order by made_current_at DESC
         ) tab
  )
where rn = 1

Is it possible to save the snapshot_id into a variable, and reference it in my main query? If so, can somebody share me an example how to make it work? I tried different variations of the syntax but none works. The documentation doesn't really explain much either.

Thanks.

[Manoj_Kumar_]

Hello @akwankl 

SET @my_snapshot_id=select snapshot_id FROM
  (
  SELECT row_number() OVER (ORDER BY made_current_at desc) `rn`, made_current_at, snapshot_id, parent_id 
    from (
          select history_meta('profile_snapshot_export_xxx') order by made_current_at DESC
         ) tab
  )
where rn = 1

Then, try using the @my_snapshot_id variable in your main query.

[akwankl]

Hi @Manoj_Kumar_ , 

I ran a simple test, 

SET @max=SELECT MAX(_repo.CreateDate) from profile_snapshot_export_xxx;

SELECT * from profile_snapshot_export_xxx where _repo.Createdate = @max

 but it gives me this error:

ErrorCode: 42601 Syntax error encountered. Reason: [Invalid command!]

[abhinavbalooni · Accepted solution]

Hey @akwankl 

Not sure if you've had a look at this documentation which is very useful for what you are trying to do.

In this case there is a meta data dataset created and then updated with the snapshot details but is a really neat solution.

https://experienceleague.adobe.com/docs/experience-platform/query/key-concepts/incremental-load.html...

Let us know if the above helps.

Cheers,

Abhinav

[akwankl]

Hey @abhinavbalooni, 

Yeah I have seen this. I am trying to avoid overcomplicating our process when this can all be solved simply by setting a value to a variable, which I haven't been able to get it working