Creating a snapshot of a table in AJO | Community
Skip to main content
R
Richard_at_Qantas
Level 2
August 17, 2022
Solved

Creating a snapshot of a table in AJO

  • August 17, 2022
  • 1 reply
  • 1223 views
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?
This post is no longer active and is closed to new replies. Need help? Start a new post to ask your question.
Best answer by AtulChavan

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-queries

1 reply

AtulChavan
Community Advisor
AtulChavanCommunity AdvisorAccepted solution
Community Advisor
August 19, 2022

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-queries

    Terms & ConditionsAccessibility statement

    Loading footer...