0

I want to return some rows from a data extension (the rows contain titles and links for news items to populate a newsletter)

I was going to use LookupOrderedRows returning all rows from this data extension sorted by date descending. However I do not wish to use a WHERE clause - I want all of them. Should I be using a different Ampscript function or is there a way to not use a WHERE clause in LookupOrdereRows, such as this:

set @rows = LookupOrderedRows("UCAS_Media_News_Items_DE",@num,"[date] desc")
2

Unfortunately there is no way to run a LookupOrderedRows without a where statement.

The easiest way to accomplish what you want is by adding in a column in your DE that is something like 'Secret' and make the value '1' for ALL records.

You can then do a LookupOrderedRows using that column as the where statement.

For example:

set @rows = LookupOrderedRows("UCAS_Media_News_Items_DE", @num, "[date] desc", "Secret", 1)

This will return all the rows as all the rows will contain a value of 1 in the 'Secret' column.


Another option is to instead use the SSJS Rows.Retrieve() method.

Example:

var yourDE = DataExtension.Init("UCAS_Media_News_Tems_DE");
var data = yourDE.Rows.Retrieve();

This function returns information in an array of objects in JSON format:

[
    {
        "yourColumn1":"yourValue1",
        "yourColumn2":"yourValue2",
        "yourColumn3":"yourValue3"
    }
]

Depending on your use case and familiarity with SSJS, it may be better to stick with the Ampscript. I personally would stick to the AMPScript solution as it is quicker and more efficient - especially if it is on an email as SSJS runs very slow on emails at send time. This can greatly delay your emails at send time, or potentially cause them to time out and error.

  • Thanks @Gortonington, do you know if there's a way to use a 'like' operator in the where clause? All of the id numbers I want to search by start with a 2, so i could maybe use "[id]","2%" – TCassa Oct 11 '17 at 13:46
  • Nope - unfortunately it is a straight up comparision of X = Y in AMPscript. You would need to use SSJS, SQL or the API to get those options. – Gortonington Oct 11 '17 at 13:48
  • Although, you could do a lookup that returns all and then do an IF statement in your FOR loop so that although it grabs all, it only displays the ones you want it to. Inefficient, but functional. – Gortonington Oct 11 '17 at 13:49
  • 1
    Not totally sure if it works syntaxwise but you could try to nest the Substring function (developer.salesforce.com/docs/…) in the where condition to isolate the first digit and compare that against '2'. If you try it I would love to know if this works because I can't test it myself at the moment – Stephan de Paly Oct 12 '17 at 20:18
  • Agreed. Substring() and/or IndexOf() would likely be the best options for the IF statement. – Gortonington Oct 12 '17 at 20:19

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.