In an automation, I want to query a number from one table and then use it as a parameter for a select top statement in another query

Question

I hope I'm explaining this well enough. I have table/DE a, where column a is strings and column b is numbers. I want to query table/DE a and select a record that has a specific number, and then use that to select top (number) from table/DE b and plug that result into a third table/DE.

It is my understanding that I cannot do this inside a SQL activity, and that I would need a script activity and SSJS to pull this off. But I don't think I know enough SSJS. I just need a little help.

Edit: I tried to use markdowns to explain better. I apologize if it's not the cleanest.

I copied the script from another question I saw that I thought was remotely similar and I made edits to it. I'm sure it's far from correct. I know I need 2 queries but I'm lost on how to save the result and use it in another query.

Table/DE1                 Table/DE2          Table/DE3
 ________________         ___________         __________
|  Name | Number |       |  Produce  |       | Produce  |
________________          ___________         __________
| John  | 1      |       |  Apples   |       |          |
 ________________        |  Oranges  |        __________
                          ___________

<script runat="server">
Platform.Load("Core","1.1") ;

var queryDef1 = { Name : "Get Number from Table 1",

CustomerKey : "myQueryDef",
/*TargetUpdateType : "Overwrite", I don't think I need an UpdateType
TargetType : "DE", or a Target Type. I simply want to get that "1" value in the first table*/
int num; //would ideally be equal to the "1" in table 1


Target:{
Name:"Table 3",
CustomerKey : "Table 3"
},
QueryText : "SELECT TOP num FROM Table2"
};

var status = QueryDefinition.Add(queryDef2);

var outDE=DataExtension.Init("table 2"); // just to see the     result

 outDE.Rows.Add(  {message:status});

Answer 1

option 1,no SSJS.

use a nested SELECT expression to populate the "TOP" number in SQL to avoid SSJS.

For this, I made a pretty terrible example, but you get the idea:

one DE "controlDE" where I save a number. You could populate this in any way that matches your usecase.

controlDE

numberOfRecords (number) // id (text)
1,myId

if in controlDE you instead have a text field where the number is stored, then you need to CAST it to integer, see query below.

else you can leave the CAST out, but you can also just always keep it, as long as you write actual numbers into the field.

SELECT TOP 
(SELECT CAST(numberOfRecords AS INT) AS intValue FROM controlDE WHERE id = 'myId')
SubscriberKey
FROM
_subscribers

if numberOfRecords in controlDE is 1, this returns the top 1 Subscriberkey from _subscribers data view. Change numberOfRecords to 2, and your targetDE contains 2 rows.

Note, this query only works in Automation Studio, query studio does not like the syntax somehow.

===

option 2, with SSJS: Write a valid query and save it as activity, then update it like the following example using SSJS. You need to define batchsize and dataExtensionName as vars, e.g. through a prior Lookup into a DE:

//Update querytext 1 with selected DE Name (select)
<script type="javascript" runat="server">
Platform.Load("Core","1")
var batchsize = /* get some number in here */
var dataExtensionName = /* your FROM de here */


var existingQuery_Key = "SelectBatch1";
var queryText = "SELECT TOP " + batchSize + " " + "someFieldName" + " FROM [" + dataExtensionName + "] source WHERE source.test = ''";
var qd = QueryDefinition.Init(existingQuery_Key);
var update_status = qd.Update({QueryText: queryText});
</script>