1

I have some code which doesn't seem to loop through the i variable incrementally... any ideas where I'm going wrong?

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

var DE1 = DataExtension.Init("abc-123-def-456");
var MaxIt = DE1.Rows.Retrieve();

for (var i = 0; i < MaxIt.length; i++)
        {

        var IT = Platform.Function.Lookup('SSJS_Query_updating_01','QueryExternalKey','Row',[i]);
        var qd = QueryDefinition.Init(IT);
        var QT1 = "SELECT MR.* FROM masterDE MR ";
        var QT2 = "WHERE 1 + 1 = "
        var QT3 = [i];

        var status = qd.Update({
                                QueryText: QT1 + QT2 + QT3
                                });

        status.Perform()
        }

the MaxIT DE has two rows with, so i expect this to loop through both rows picking up the ext key for the two queries and re-writing both exactly the same as above. I suspect that i am not using the [i] iteration correctly but not sure

UPDATE

I have tweaked the code and simplified what I am asking but i can only get one of the two queries in the data extension to update and it's always the second row of the two rows in the data extension. Anything in this code that stands out as to why both query definitions sitting in the fields won't update?

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

    var DE1 = DataExtension.Init("abc-123-def-456");
    var MaxIt = DE1.Rows.Retrieve();

    for (var i = 1; i = MaxIt.length; i++)
        {

    var IT = Platform.Function.Lookup('SSJS_Query_updating_01','QueryExternalKey','Row', [i] + 1);
    var qd = QueryDefinition.Init(IT);
    var QT1 = "SELECT M.* ";
    var QT2 = "FROM [masterDE] M";

    var status = qd.Update({
                            QueryText: QT1 + QT2
                            });

    status.Perform();
    }

</script>

Note i've added i + 1... i can't get it to update even one query without putting the +1 there

13
  • is it supposed to be WHERE 1 + 1 = [0] or 1+1 = 0 for first iteration? What is the purpose of the brackets you have there? – Gortonington Oct 16 '19 at 17:11
  • Its a great help to me to put the code in a cloud page and add Write statements throughout the code as debug. If you just want the value of i, I think you have to remove the brackets. – SebOB Oct 17 '19 at 6:33
  • @Gortonington, I was just adding SQL query code that would concatenate together. I need to do something more complicated than the above but i am taking baby steps as I am new to this and maybe the steps will help others when searching. I just need the loop to work and my goal here is to update the two query references i have in my data extension with the SQL query text – Hari Govindan Oct 17 '19 at 8:21
  • @SebOB, thanks i will try the bracket removal. I am so new to SSJS and indeed JavaScript itself, so not sure how to do a Write statement! – Hari Govindan Oct 17 '19 at 8:24
  • Just put the code in a cloud page and add Write('some text<br>'); and press Preview, you will see the text written in the page. You can put variables as well so you can follow the code. – SebOB Oct 17 '19 at 9:49
0

This code works:

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

var DE1 = DataExtension.Init("abc-123-def-456");
var MaxIt = DE1.Rows.Retrieve();

for (var i = 0; i < MaxIt.length; i++)
        {

        var IT = Platform.Function.Lookup('SSJS_Query_updating_01','QueryExternalKey','Row', i + 1);
        var qd = QueryDefinition.Init(IT);
        var QT1 = "SELECT M.* ";
        var QT2 = "FROM [masterDE] M";

        var status = qd.Update({
                                QueryText: QT1 + QT2
                                });


        }

so i cannot be in the [brackets], i should be less than maxit.length and finally the status.perform has to be removed, qd.Update performs the task

Your Answer

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.