How to use an SQL resultset as the name of a Data Extension in a query

Question

I have two Data Extensions one with a field that contains the name of another data extension

SetUp DE

ID	Audience
01	bigAudience
02	smallAudience

smallAudience DE

PersID
00001
00002

I would like to select the data within the second DE (smallAudience) based on the result that is returned from querying the first DE (Setup)

SELECT
PersID 
FROM 
(CAST((SELECT TOP 1 a.Audience FROM [Setup] a ORDER BY a.ID DESC) AS CHAR))

In this example, I am attempting to CAST the resultset as a CHAR then return this to the FROM clause, given that only a single value is returned. However this does not seem to work as per error returned

An error occurred while checking the query syntax. Errors: CAST((SELECT is not a known data extension or system data view. You can only query existing data extensions or system data views.

This is all from an activity within Automation studio in Marketing Cloud

Answer 1

We had a similar requirement and we went for SSJS Query Definition Funtions, as we could not find a solution using SQL.

Below is an example

<script runat="server">
Platform.Load("Core", "1.1.1");
  //Retrieve records from the DE where DE names we want to use are stored
  var DEnameInRows = DataExtension.Init("Test_QResult").Rows.Retrieve();
  //for your case, sort your recordset first then use appropriate index to get the DE name from record set  
  var FromDEname = DEnameInRows[1].DEName;
  var query_load = {
       Name : "Q_Load",
       CustomerKey : "Q_Load",
       TargetUpdateType : "Overwrite",
       TargetType : "DE",
       Target : {
       Name : "targetDE",
       CustomerKey : "targetDE"},
       QueryText :"select cid, value from "+FromDEname};
  var q_sts = QueryDefinition.Add(query_load);
  var q_data = QueryDefinition.Init("Q_Load");
  var status_Qry1 = q_data.Perform();
  var status_Rm1 = q_data.Remove();
</script>

Sample Records

Test_QResult

sourceDE2

TargetDE

Just like Query activity, you can use this in automation using script activity.

Downside of this approach is that it takes some time to reflect the data in the target DE. but it's better than nothing.