0

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});
New contributor
NeedSSJSHelp is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
2
  • Welcome to Salesforce Stack Exchange (SFSE). Firstly, it might be easier to read and follow your question and example if you use the easy Markdown formatting available in the editor (e.g., "I have Table A, where Column A is a String and Column B is a Number"). Secondly, please give a specific example using fake sample data in both tables. Thirdly, this site exists to help you, not write code or implementations for you. So no matter how badly your SSJS might be, that's OK - try it, and then come back with what specifically you've tried & what issues or errors you have.
    – Moonpie
    Sep 14 at 0:30
  • Thank you for your feedback. I tried my best to make edits to make an earnest attempt at this. I hope it helps at least a little. I hope I managed to address all 3 points to make it easier to help me. Sep 14 at 0:56
0

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>
1
  • This is exactly what I needed! Thank you! Sep 15 at 13:38

Your Answer

NeedSSJSHelp is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.