Using Ampscript LookupOrderRows Function retrieving the information from DE

Question

I have a requirement to fetch the information into an email Dynamically from the Product_Image DE table

MasterDE > OrderSuppDE > Produt_Image

So there are 3 DE's, MasterDE, OrderSuppDE, Product_Image. Common fields among them are as follows,

MasterDE & OrderSupp DE: Customer Id is the common field on which I'm trying to do LookupOrderRows and get all the records.

Then I'm doing a similar step for OrderSuppDE & Product_Image: Product_Code is the common field on which I'm trying to do LookupOrderRows and get all the records.

Below is my ampscript what I've written now, and I'm getting an error when I'm trying to preview and test with MasterDE records. Can someone help me with what I'm doing wrong?

Error : HTML Version There is an error in your email. Please contact your customer service representative. Error 1: Script FOR Not Closed
A FOR statement is not closed with the required NEXT statement.
Invalid Content:
FOR @i = 1 to @rowCount1 do Var @PRODUCT_CODE SET @row1 = row(@rows1, @i)/* get row based on counter */ SET @PRODUCT_CODE = field(@row1,"PRODUCT_CODE") IF NOT EMPTY (@PRODUCT_CODE)THEN VAR @PRODUCT_CODE, @rows2, @row2, @rowCount2, @numRowsToReturn2, @j SET @PRODUCT_CODE = Attri

%%[

var @CUSTOMER_ID, @rows1, @row1, @rowCount1, @numRowsToReturn1, @i
SET @CUSTOMER_ID = AttributeValue("CUSTOMER_ID")
SET @numRowsToReturn1 = 0  /* 0 means all, max 2000 */
SET @rows1 = LookupOrderedRows("CheckGallery_OrderHistorySup", @numRowsToReturn1,"CUSTOMER_ID desc","CUSTOMER_ID", @CUSTOMER_ID)
SET @rowCount1 = rowcount(@rows1)

IF @rowCount1 > 0 then

FOR @i = 1 to @rowCount1 do

Var @PRODUCT_CODE

SET @row1 = row(@rows1, @i)  /* get row based on counter */
SET @PRODUCT_CODE = field(@row1,"PRODUCT_CODE")

    IF NOT EMPTY (@PRODUCT_CODE)THEN

      VAR @PRODUCT_CODE, @rows2, @row2, @rowCount2, @numRowsToReturn2, @j
      SET @PRODUCT_CODE = AttributeValue("PRODUCT_CODE")
      SET @numRowsToReturn2 = 0  /* 0 means all, max 2000 */
      SET @rows2 = LookupOrderedRows("Product_Images", @numRowsToReturn2,"PRODUCT_CODE desc","PRODUCT_CODE", @PRODUCT_CODE)
      SET @rowCount2 = rowcount(@rows2)

        IF @rowCount2 > 0 then

          FOR @j = 1 to @rowCount2 do
      
            var @NAME1, @NAME2, @CHECK_IMAGE, @CHECK_LINK, @COPYRIGHT2
            SET @row2 = row(@rows2, @j)  /* get row based on counter */
            SET @NAME1 = field(@row2,"NAME1")
            SET @NAME2 = field(@row2,"NAME2")
            SET @CHECK_IMAGE = field(@row2,"CHECK_IMAGE")
            Set @link = Concat('https://', @CHECK_IMAGE)
            SET @CHECK_LINK = field(@row2,"CHECK_LINK")
            SET @COPYRIGHT2 = field(@row2,"COPYRIGHT2")
            
            next @j
         ENDIF
        ENDIF
       next@i
     ENDIF
    ]%%

Answer 1

Looks like there are a couple of syntax issues.

You have an extra space after the empty and the left parenthesis.

• This: IF NOT EMPTY (@PRODUCT_CODE)THEN

• Should be: IF NOT EMPTY(@PRODUCT_CODE)THEN

Also, I think you need a space before after the next

• This: next@i

• Should be: next @i

• Or you could just leave off the variable, it's not required: next

Here's how I'd indent it:

%%[

  var @CUSTOMER_ID, @rows1, @row1, @rowCount1, @numRowsToReturn1, @i
  SET @CUSTOMER_ID = AttributeValue("CUSTOMER_ID")
  SET @numRowsToReturn1 = 0  /* 0 means all, max 2000 */
  SET @rows1 = LookupOrderedRows("CheckGallery_OrderHistorySup", @numRowsToReturn1,"CUSTOMER_ID desc","CUSTOMER_ID", @CUSTOMER_ID)
  SET @rowCount1 = rowcount(@rows1)

  IF @rowCount1 > 0 then

    FOR @i = 1 to @rowCount1 do

      Var @PRODUCT_CODE

      SET @row1 = row(@rows1, @i)  /* get row based on counter */
      SET @PRODUCT_CODE = field(@row1,"PRODUCT_CODE")

      IF NOT EMPTY(@PRODUCT_CODE)THEN

        VAR @PRODUCT_CODE, @rows2, @row2, @rowCount2, @numRowsToReturn2, @j
        SET @PRODUCT_CODE = AttributeValue("PRODUCT_CODE")
        SET @numRowsToReturn2 = 0  /* 0 means all, max 2000 */
        SET @rows2 = LookupOrderedRows("Product_Images", @numRowsToReturn2,"PRODUCT_CODE desc","PRODUCT_CODE", @PRODUCT_CODE)
        SET @rowCount2 = rowcount(@rows2)

        IF @rowCount2 > 0 then

          FOR @j = 1 to @rowCount2 do

            var @NAME1, @NAME2, @CHECK_IMAGE, @CHECK_LINK, @COPYRIGHT2
            SET @row2 = row(@rows2, @j)  /* get row based on counter */
            SET @NAME1 = field(@row2,"NAME1")
            SET @NAME2 = field(@row2,"NAME2")
            SET @CHECK_IMAGE = field(@row2,"CHECK_IMAGE")
            Set @link = Concat('https://', @CHECK_IMAGE)
            SET @CHECK_LINK = field(@row2,"CHECK_LINK")
            SET @COPYRIGHT2 = field(@row2,"COPYRIGHT2")

          next @j

        ENDIF

      ENDIF

    next @i
    
  ENDIF

]%%