Your achievements

Level 1

0% to

Level 2

Tip /
Sign in

Sign in to Community

to gain points, level up, and earn exciting badges like the new
Bedrock Mission!

Learn more

View all

Sign in to view all badges

Creating a snapshot of a table in AJO

Avatar

Level 2
Hi, I need to create a snapshot of my profile snapshot dataset in AJO so I can access values stored in aggregate fields in PowerBI. I have tried scheduling a query to select the columns and counters I need and write them to a dataset. The problem I have is that the scheduled query either adds rows to the table (thus I get duplicates), using the "Use Existing Dataset" option or schedule never runs when I use the "Use New Dataset". I've tried adding the SQL DELETE FROM tablename at the start of the query only to have my query fail with delete from tool_extract_layers_and_locations; 6:52:08 PM > Running query... 6:52:46 PM > Query failed in 38.285 seconds. 6:52:46 PM > ErrorCode: 42601 Syntax error encountered. Reason: [Invalid command!] How can I run a scheduled query (eg run every second day) , create a dataset with a fixed name, and only have it contain the results of the last time the query executed?
1 Reply

Avatar

Level 4

Hi @Richard_at_Qantas ,
Not sure on your requirement with AJO but with regards to creating table with same name here are your options - 

In AEP query service you may use drop table first and later create statement.DROP TABLE [IF EXISTS] [db_name.]table_name

You can also go with drop and create view. Go through this to learn about syntax and options AEP supports : https://experienceleague.adobe.com/docs/experience-platform/query/sql/syntax.html?lang=en#select-que...