✏️ Explanatory Question

How do you use the LIKE operator in X++ to filter records based on a partial match in a field? Provide an example to select a customer record where the customer’s name contains "ABC".

👁 40 Views
📘 Detailed Answer
🟢 Easy
No previous question
💡

Answer with Explanation

X++ Code: Select Data from Table with Where Clause Using the LIKE Operator in X++ for Pattern Matching in SQL Queries

Question:

How do you use the LIKE operator in X++ to filter records based on a partial match in a field? Provide an example to select a customer record where the customer’s name contains "ABC".

Answer:

In X++, the LIKE operator is used for pattern matching in a SELECT query. It allows you to filter records based on partial matches in a field. The * wildcard is used to represent any number of characters in the pattern.


static void SelectWithLikeExample(Args _args)
{
    CustTable custTable;
    
    // Using the SELECT statement with WHERE clause and LIKE
    select firstOnly custTable
        where custTable.Name like "*ABC*"; // The Name field contains the substring "ABC"

    // Displaying the result
    if (custTable)
    {
        info("Customer found: " + custTable.AccountNum + " - " + custTable.Name);
    }
    else
    {
        info("No customer found with the specified name.");
    }
}

Explanation:

  1. select firstOnly: Retrieves only the first record matching the criteria.
  2. custTable.Name like "*ABC*": The LIKE operator checks if the Name field contains the substring "ABC" anywhere in the field. The * is a wildcard that matches any characters before or after "ABC".
  3. info(): This function displays the account number and name of the found customer. If no customer matches, a message saying "No customer found with the specified name" will be displayed.

Key Takeaways:

  • The LIKE operator helps in searching for partial matches in a field.
  • The * symbol in the LIKE expression acts as a wildcard that matches zero or more characters.

Another Example


if (surMarkupTrans.RecId > 2 && surMarkupTrans.CalculatedAmount != 0.0)
{
    ttsBegin;

    select firstOnly forUpdate markupTrans
        where markupTrans.TransTableId == _tableId
        && markupTrans.TransRecId == _refRecid
        && markupTrans.MarkupCode == _ccMarkupCode
        && markupTrans.MarkupCategory != MarkupCategory::Percent
        && !(surMarkupTrans.Txt like '*FET-TPNA*');  

    if (markupTrans.RecId)
    {
        markupTrans.CalculatedAmount = markupTrans.CalculatedAmount + surMarkupTrans.CalculatedAmount;
        markupTrans.Posted = markupTrans.CalculatedAmount;
        markupTrans.doUpdate();
    }

    ttsCommit;

    return surMarkupTrans.CalculatedAmount;
}
No previous question