Perform logic if last row of SQL Fetch/Loop doesn't find match

Question

I have the below Application Engine program that I am modifying. I am basically adding in code for cases when the Unit of Measure (&UOMResult) of an item is entered in a CSV/File Layout does not match the value of the existing row(s) retrieved for that same item, then I perform a REC.INSERT, otherwise if there is not a match found then I go through a different REC1.INSERT logic.

My understanding is that the SQL Fetch function (&SQL2.Fetch) only can fetch and store one row of data at a time into a variable (Please correct me if I am wrong on that) and therefore I have to include a While looping statement to continue getting all rows, one at a time (If there is an alternative to &SQL2.Fetch that can process multiple rows in the same iteration please let me know). Based on this I added in code to loop through the &SQL2.Fetch and if there is a matching row (to the variable &UOM) then I do a REC.INSERT processing and then Break out of the loop, otherwise it continues looping (Used Continue function- don't know if it's necessary) through to compare the next row(s) until it either finds a match and does the break or - (what I intend for it to do) go into REC2.Insert logic. I don't want it to go to Continue if it has processed the last row.

The way it's coded now I am afraid it will be an infinite loop in cases where there is never a match. I would like it to be that after the entire looping iterations have completed (last row has been read) and there isn't a match then go to REC2.Insert logic. Is there a way for the code to know that the last row has been looped through, if so how can I change it accordingly?

Local SQL &SQL;
Local string &VendorId, &InvItemId, &ItmIdVndr, &UOM, &UOMResult, &ConvRate, &PriceVndr, &Oprid, &PriceStatus, &VendorId_check, &insert_cols, &insert_select, &insert_where, &insert_sql, &Error, &MaxEffdt, &ItmField;
Local date &Effdt;
Local Record &ITM_VNDR_UOM_PR, &REC, &MASTER_ITEM_TBL;

&Oprid = "'BATCH'";
&PriceStatus = "2";
&Error = "N";

MessageBox(0, "", 0, 0, "**** BEGINING OF VALIDATION ERRORS ****");

&SQL = CreateSQL("SELECT VENDOR_ID, INV_ITEM_ID, ITM_ID_VNDR, UNIT_OF_MEASURE, CONVERSION_RATE, PRICE_VNDR, %DateOut(EFFDT), ITEM_FIELD_C10_B FROM PS_GH_ITM_PR_UPDT ORDER BY VENDOR_ID, INV_ITEM_ID, ITM_ID_VNDR", &VendorId, &InvItemId, &ItmIdVndr, &UOM, &ConvRate, &PriceVndr, &Effdt, &ItmField);
MessageBox(0, "", 0, 0, &VendorId | " " | &InvItemId | " " | &ItmIdVndr | " " | &UOM | " " | &ConvRate | " " | &PriceVndr | " " | &Effdt | " " | &ItmField | " " | &Error);
While &SQL.Fetch(&VendorId, &InvItemId, &ItmIdVndr, &UOM, &ConvRate, &PriceVndr, &Effdt, &ItmField)

   SQLExec("SELECT A.VENDOR_ID FROM PS_ITM_VENDOR A, PS_ITM_VNDR_UOM B WHERE A.SETID = B.SETID AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.VENDOR_SETID = B.VENDOR_SETID AND A.VENDOR_ID = B.VENDOR_ID AND A.VENDOR_ID = :1 AND A.INV_ITEM_ID = :2 AND A.ITM_ID_VNDR = :3 AND A.ITM_VNDR_PRIORITY = 1 AND B.UNIT_OF_MEASURE = :4 AND B.CONVERSION_RATE = :5", &VendorId, &InvItemId, &ItmIdVndr, &UOM, &ConvRate, &VendorId_check);

   If None(&VendorId_check) Then;
      &Error = "Y";
      MessageBox(0, "", 0, 0, &VendorId | " " | &InvItemId | " " | &ItmIdVndr | " " | &UOM | " " | &ConvRate | " " | &PriceVndr | " " | &Effdt | " " | &ItmField | " " | &Error);
     &CurrentDateTime = %Datetime;
     &Oprid = %OperatorId;
     &RECITEM2 = CreateRecord(Record.ITM_VNDR_UOM_PR);
     SQLExec("SELECT C.EFFDT FROM PS_ITM_VNDR_UOM_PR C WHERE C.INV_ITEM_ID = :1 AND C.VENDOR_ID = :2 AND C.LAST_DTTM_UPDATE = (SELECT MAX(A_ED.LAST_DTTM_UPDATE) FROM PS_ITM_VNDR_UOM_PR A_ED WHERE C.SETID = A_ED.SETID AND C.INV_ITEM_ID = A_ED.INV_ITEM_ID AND C.VENDOR_SETID = A_ED.VENDOR_SETID AND C.VENDOR_ID = A_ED.VENDOR_ID AND C.VNDR_LOC = A_ED.VNDR_LOC AND C.CURRENCY_CD = A_ED.CURRENCY_CD AND C.QTY_MIN = A_ED.QTY_MIN ) ", &InvItemId, &VendorId, &UOM, &MaxEffdt);
     &SQL2 = CreateSQL("SELECT C.UNIT_OF_MEASURE FROM PS_ITM_VNDR_UOM_PR C WHERE C.INV_ITEM_ID = :1 AND C.VENDOR_ID = :2 AND C.EFFDT = (SELECT MAX(C1.EFFDT) FROM PS_ITM_VNDR_UOM_PR C1 WHERE C.SETID = C1.SETID AND C.INV_ITEM_ID = C1.INV_ITEM_ID AND C.VENDOR_SETID = C1.VENDOR_SETID AND C.VENDOR_ID = C1.VENDOR_ID AND C.VNDR_LOC = C1.VNDR_LOC AND C.UNIT_OF_MEASURE = C1.UNIT_OF_MEASURE AND C.CURRENCY_CD = C1.CURRENCY_CD AND C.QTY_MIN = C1.QTY_MIN) ", &InvItemId, &VendorId, &UOMResult);
      While &SQL2.Fetch(&UOMResult)
       If &UOM = &UOMResult Then;
       SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND (CONVERT(CHAR(10),EFFDT,121)) = :5", &RECITEM2, &InvItemId, &VendorId, &UOM, &MaxEffdt, &RECITEM2);
       /*SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND EFFDT = :5", &RECITEM, &InvItemId, &VendorId, &UOM, &MaxEffdt, &RECITEM); */
       &new_UNIT_OF_MEASURE = &RECITEM2.UNIT_OF_MEASURE.Value;
       &REC2 = CreateRecord(Record.ITM_VNDR_UOM_PR);
       &REC2.SETID.Value = &RECITEM.SETID.Value;
       &REC2.INV_ITEM_ID.Value = &RECITEM2.INV_ITEM_ID.Value;
       &REC2.VENDOR_SETID.Value = &RECITEM2.VENDOR_SETID.Value;
       &REC2.VENDOR_ID.Value = &RECITEM2.VENDOR_ID.Value;
       &REC2.VNDR_LOC.Value = &RECITEM2.VNDR_LOC.Value;
       &REC2.UNIT_OF_MEASURE.Value = &RECITEM2.UNIT_OF_MEASURE.Value;
       &REC2.CURRENCY_CD.Value = &RECITEM2.CURRENCY_CD.Value;
       &REC2.QTY_MIN.Value = &RECITEM2.QTY_MIN.Value;
       &REC2.EFFDT.Value = &Effdt;
       &REC2.EFF_STATUS.Value = &RECITEM.EFF_STATUS.Value;
       &REC2.PRICE_VNDR.Value = &PriceVndr;
       &REC2.UNIT_PRC_TOL.Value = &RECITEM.UNIT_PRC_TOL.Value;
       &REC2.EXT_PRC_TOL.Value = &RECITEM.EXT_PRC_TOL.Value;
       &REC2.PCT_UNIT_PRC_TOL.Value = &RECITEM.PCT_UNIT_PRC_TOL.Value;
       &REC2.PCT_EXT_PRC_TOL.Value = &RECITEM.PCT_EXT_PRC_TOL.Value;
       &REC2.USE_STD_TOLERANCES.Value = &RECITEM.USE_STD_TOLERANCES.Value;
       &REC2.QTY_RECV_TOL_PCT.Value = &RECITEM.QTY_RECV_TOL_PCT.Value;
       &REC2.UNIT_PRC_TOL_L.Value = &RECITEM.UNIT_PRC_TOL_L.Value;
       &REC2.PCT_UNIT_PRC_TOL_L.Value = &RECITEM.PCT_UNIT_PRC_TOL_L.Value;
       &REC2.EXT_PRC_TOL_L.Value = &RECITEM.EXT_PRC_TOL_L.Value;
       &REC2.PCT_EXT_PRC_TOL_L.Value = &RECITEM.PCT_EXT_PRC_TOL_L.Value;
       &REC2.BU_PRICE_STATUS.Value = "2";
       &REC2.STD_PRICE_STATUS.Value = "2";
       &REC2.LEAD_TIME.Value = &RECITEM.LEAD_TIME.Value;
       &REC2.OPRID_MODIFIED_BY.Value = &Oprid;
       &REC2.LAST_DTTM_UPDATE.Value = &CurrentDateTime;
       &REC2.PRICE_CHANGE.Value = &RECITEM.PRICE_CHANGE.Value;
       &REC2.Insert();
       Break;
       End-While;


       Else;
       Continue;    /* Unless last row has been processed in While - NEED HELP HERE */


      End-If;  /* End-If here or below next code block NEED HELP HERE ?? */

       /* Final Else - If all rows have been looped through and there was not a match found then do the following - NEED HELP HERE */

       SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND (CONVERT(CHAR(10),EFFDT,121)) = :5", &RECITEM3, &InvItemId, &VendorId, &UOM, &MaxEffdt, &RECITEM3);
       &new_UNIT_OF_MEASURE = &RECITEM2.UNIT_OF_MEASURE.Value;
       &REC2 = CreateRecord(Record.ITM_VNDR_UOM_PR);
       &REC2.SETID.Value = &RECITEM3.SETID.Value;
       &REC2.INV_ITEM_ID.Value = &RECITEM3.INV_ITEM_ID.Value;
       &REC2.VENDOR_SETID.Value = &RECITEM3.VENDOR_SETID.Value;
       &REC2.VENDOR_ID.Value = &RECITEM3.VENDOR_ID.Value;
       &REC2.VNDR_LOC.Value = &RECITEM3.VNDR_LOC.Value;
       &REC2.UNIT_OF_MEASURE.Value = &UOMResult
       &REC2.CURRENCY_CD.Value = &RECITEM3.CURRENCY_CD.Value;
       &REC2.QTY_MIN.Value = &RECITEM3.QTY_MIN.Value;
       &REC2.EFFDT.Value = &Effdt;
       &REC2.EFF_STATUS.Value = &RECITEM3.EFF_STATUS.Value;
       &REC2.PRICE_VNDR.Value = &PriceVndr;
       &REC.UNIT_PRC_TOL.Value = &RECITEM3.UNIT_PRC_TOL.Value;
       &REC2.EXT_PRC_TOL.Value = &RECITEM3.EXT_PRC_TOL.Value;
       &REC2.PCT_UNIT_PRC_TOL.Value = &RECITEM3.PCT_UNIT_PRC_TOL.Value;
       &REC2.PCT_EXT_PRC_TOL.Value = &RECITEM3.PCT_EXT_PRC_TOL.Value;
       &REC2.USE_STD_TOLERANCES.Value = &RECITEM3.USE_STD_TOLERANCES.Value;
       &REC2.QTY_RECV_TOL_PCT.Value = &RECITEM3.QTY_RECV_TOL_PCT.Value;
       &REC2.UNIT_PRC_TOL_L.Value = &RECITEM3.UNIT_PRC_TOL_L.Value;
       &REC2.PCT_UNIT_PRC_TOL_L.Value = &RECITEM3.PCT_UNIT_PRC_TOL_L.Value;
       &REC2.EXT_PRC_TOL_L.Value = &RECITEM3.EXT_PRC_TOL_L.Value;
       &REC2.PCT_EXT_PRC_TOL_L.Value = &RECITEM3.PCT_EXT_PRC_TOL_L.Value;
       &REC2.BU_PRICE_STATUS.Value = "2";
       &REC2.STD_PRICE_STATUS.Value = "2";
       &REC2.LEAD_TIME.Value = &RECITEM3.LEAD_TIME.Value;
       &REC2.OPRID_MODIFIED_BY.Value = &Oprid;
       &REC2.LAST_DTTM_UPDATE.Value = &CurrentDateTime;
       &REC2.PRICE_CHANGE.Value = &RECITEM3.PRICE_CHANGE.Value;
       &REC2.Insert();
     End-If;  
    End-While;
   Else;
      &CurrentDateTime = %Datetime;
      &Oprid = %OperatorId;
      &RECITEM = CreateRecord(Record.ITM_VNDR_UOM_PR);

      /*GETTING MAX EFFECTIVE DATED ROW TO COPY EXISTING ROW VALUES FROM INTO NEW ROW */
      SQLExec("SELECT C.EFFDT FROM PS_ITM_VNDR_UOM_PR C WHERE C.INV_ITEM_ID = :1 AND C.VENDOR_ID = :2 AND C.UNIT_OF_MEASURE = :3 AND C.EFFDT = (SELECT MAX(C1.EFFDT) FROM PS_ITM_VNDR_UOM_PR C1 WHERE C.SETID = C1.SETID AND C.INV_ITEM_ID = C1.INV_ITEM_ID AND C.VENDOR_SETID = C1.VENDOR_SETID AND C.VENDOR_ID = C1.VENDOR_ID AND C.VNDR_LOC = C1.VNDR_LOC AND C.UNIT_OF_MEASURE = C1.UNIT_OF_MEASURE AND C.CURRENCY_CD = C1.CURRENCY_CD AND C.QTY_MIN = C1.QTY_MIN)  ", &InvItemId, &VendorId, &UOM, &MaxEffdt);

      SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND (CONVERT(CHAR(10),EFFDT,121)) = :5", &RECITEM, &InvItemId, &VendorId, &UOM, &MaxEffdt, &RECITEM);
      /*SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND EFFDT = :5", &RECITEM, &InvItemId, &VendorId, &UOM, &MaxEffdt, &RECITEM); */
      &new_UNIT_OF_MEASURE = &RECITEM.UNIT_OF_MEASURE.Value;
      &REC = CreateRecord(Record.ITM_VNDR_UOM_PR);
      &REC.SETID.Value = &RECITEM.SETID.Value;
      &REC.INV_ITEM_ID.Value = &RECITEM.INV_ITEM_ID.Value;
      &REC.VENDOR_SETID.Value = &RECITEM.VENDOR_SETID.Value;
      &REC.VENDOR_ID.Value = &RECITEM.VENDOR_ID.Value;
      &REC.VNDR_LOC.Value = &RECITEM.VNDR_LOC.Value;
      &REC.UNIT_OF_MEASURE.Value = &RECITEM.UNIT_OF_MEASURE.Value;
      &REC.CURRENCY_CD.Value = &RECITEM.CURRENCY_CD.Value;
      &REC.QTY_MIN.Value = &RECITEM.QTY_MIN.Value;
      &REC.EFFDT.Value = &Effdt;
      &REC.EFF_STATUS.Value = &RECITEM.EFF_STATUS.Value;
      &REC.PRICE_VNDR.Value = &PriceVndr;
      &REC.UNIT_PRC_TOL.Value = &RECITEM.UNIT_PRC_TOL.Value;
      &REC.EXT_PRC_TOL.Value = &RECITEM.EXT_PRC_TOL.Value;
      &REC.PCT_UNIT_PRC_TOL.Value = &RECITEM.PCT_UNIT_PRC_TOL.Value;
      &REC.PCT_EXT_PRC_TOL.Value = &RECITEM.PCT_EXT_PRC_TOL.Value;
      &REC.USE_STD_TOLERANCES.Value = &RECITEM.USE_STD_TOLERANCES.Value;
      &REC.QTY_RECV_TOL_PCT.Value = &RECITEM.QTY_RECV_TOL_PCT.Value;
      &REC.UNIT_PRC_TOL_L.Value = &RECITEM.UNIT_PRC_TOL_L.Value;
      &REC.PCT_UNIT_PRC_TOL_L.Value = &RECITEM.PCT_UNIT_PRC_TOL_L.Value;
      &REC.EXT_PRC_TOL_L.Value = &RECITEM.EXT_PRC_TOL_L.Value;
      &REC.PCT_EXT_PRC_TOL_L.Value = &RECITEM.PCT_EXT_PRC_TOL_L.Value;
      &REC.BU_PRICE_STATUS.Value = "2";
      &REC.STD_PRICE_STATUS.Value = "2";
      &REC.LEAD_TIME.Value = &RECITEM.LEAD_TIME.Value;
      &REC.OPRID_MODIFIED_BY.Value = &Oprid;
      &REC.LAST_DTTM_UPDATE.Value = &CurrentDateTime;
      &REC.PRICE_CHANGE.Value = &RECITEM.PRICE_CHANGE.Value;
      &REC.Insert();

   End-If;

     If None(&ItmField) Then;
      &Error = "Y";
      MessageBox(0, "", 0, 0, &VendorId | " " | &InvItemId | " " | &ItmIdVndr | " " | &UOM | " " | &ConvRate | " " | &PriceVndr | " " | &Effdt | " " | &ItmField);
   Else;
      SQLExec("UPDATE PS_MASTER_ITEM_TBL SET ITEM_FIELD_C10_B = :1 WHERE INV_ITEM_ID = :2 AND SETID = 'SHARE' ", &ItmField, &InvItemId);
      /* Else;
       &REC1 = CreateRecord(Record.MASTER_ITEM_TBL);
       &REC1.ITEM_FIELD_C10_B.Value = &ItmField;
       &REC1.SETID.Value = "SHARE";
       &REC1.INV_ITEM_ID.Value = &InvItemId;
       &REC1.Update();
    End-If;  */

   End-If;

End-While;
MessageBox(0, "", 0, 0, "**** END OF VALIDATION ERRORS ****");
MessageBox(0, "", 0, 0, " ");

You will see references to the &SQL2 and While statements in the code above, along with my 'NEED HELP HERE' comments with how to deal with after comparing the last row. Any suggestions with overall program flow are also welcome. Thank you!

10/23/18 Edit:

Local SQL &SQL;
Local string &VendorId, &InvItemId, &ItmIdVndr, &UOM, &UOMResult, &ConvRate, &PriceVndr, &Oprid, &PriceStatus, &VendorId_check, &insert_cols, &insert_select, &insert_where, &insert_sql, &Error, &MaxEffdt, &ItmField;
Local boolean &RowFound;
Local date &Effdt;
Local Record &ITM_VNDR_UOM_PR, &REC, &MASTER_ITEM_TBL;

&Oprid = "'BATCH'";
&PriceStatus = "2";
&Error = "N";

MessageBox(0, "", 0, 0, "**** BEGINING OF VALIDATION ERRORS ****");

&SQL = CreateSQL("SELECT VENDOR_ID, INV_ITEM_ID, ITM_ID_VNDR, UNIT_OF_MEASURE, CONVERSION_RATE, PRICE_VNDR, %DateOut(EFFDT), ITEM_FIELD_C10_B FROM PS_GH_ITM_PR_UPDT ORDER BY VENDOR_ID, INV_ITEM_ID, ITM_ID_VNDR", &VendorId, &InvItemId, &ItmIdVndr, &UOM, &ConvRate, &PriceVndr, &Effdt, &ItmField);
MessageBox(0, "", 0, 0, &VendorId | " " | &InvItemId | " " | &ItmIdVndr | " " | &UOM | " " | &ConvRate | " " | &PriceVndr | " " | &Effdt | " " | &ItmField | " " | &Error);
While &SQL.Fetch(&VendorId, &InvItemId, &ItmIdVndr, &UOM, &ConvRate, &PriceVndr, &Effdt, &ItmField)

   SQLExec("SELECT A.VENDOR_ID FROM PS_ITM_VENDOR A, PS_ITM_VNDR_UOM B WHERE A.SETID = B.SETID AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.VENDOR_SETID = B.VENDOR_SETID AND A.VENDOR_ID = B.VENDOR_ID AND A.VENDOR_ID = :1 AND A.INV_ITEM_ID = :2 AND A.ITM_ID_VNDR = :3 AND A.ITM_VNDR_PRIORITY = 1 AND B.UNIT_OF_MEASURE = :4 AND B.CONVERSION_RATE = :5", &VendorId, &InvItemId, &ItmIdVndr, &UOM, &ConvRate, &VendorId_check);

   If None(&VendorId_check) Then;
     MessageBox(0, "", 0, 0, &VendorId | " " | &InvItemId | " " | &ItmIdVndr | " " | &UOM | " " | &ConvRate | " " | &PriceVndr | " " | &Effdt | " " | &ItmField | " " | &Error);
     &CurrentDateTime = %Datetime;
     &Oprid = %OperatorId;
     &RECITEM2 = CreateRecord(Record.ITM_VNDR_UOM_PR);
     SQLExec("SELECT C.EFFDT FROM PS_ITM_VNDR_UOM_PR C WHERE C.INV_ITEM_ID = :1 AND C.VENDOR_ID = :2 AND C.LAST_DTTM_UPDATE = (SELECT MAX(A_ED.LAST_DTTM_UPDATE) FROM PS_ITM_VNDR_UOM_PR A_ED WHERE C.SETID = A_ED.SETID AND C.INV_ITEM_ID = A_ED.INV_ITEM_ID AND C.VENDOR_SETID = A_ED.VENDOR_SETID AND C.VENDOR_ID = A_ED.VENDOR_ID AND C.VNDR_LOC = A_ED.VNDR_LOC AND C.CURRENCY_CD = A_ED.CURRENCY_CD AND C.QTY_MIN = A_ED.QTY_MIN ) ", &InvItemId, &VendorId, &UOM, &MaxEffdt);
     &SQL2 = CreateSQL("SELECT C.UNIT_OF_MEASURE FROM PS_ITM_VNDR_UOM_PR C WHERE C.INV_ITEM_ID = :1 AND C.VENDOR_ID = :2 AND C.EFFDT = (SELECT MAX(C1.EFFDT) FROM PS_ITM_VNDR_UOM_PR C1 WHERE C.SETID = C1.SETID AND C.INV_ITEM_ID = C1.INV_ITEM_ID AND C.VENDOR_SETID = C1.VENDOR_SETID AND C.VENDOR_ID = C1.VENDOR_ID AND C.VNDR_LOC = C1.VNDR_LOC AND C.UNIT_OF_MEASURE = C1.UNIT_OF_MEASURE AND C.CURRENCY_CD = C1.CURRENCY_CD AND C.QTY_MIN = C1.QTY_MIN) ", &InvItemId, &VendorId, &UOMResult);
     &RowFound = False;
     While &SQL2.Fetch(&UOMResult)
       If &UOM = &UOMResult Then;
       &RowFound = True;
       SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND (CONVERT(CHAR(10),EFFDT,121)) = :5", &RECITEM2, &InvItemId, &VendorId, &UOM, &MaxEffdt, &RECITEM2);
       /*SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND EFFDT = :5", &RECITEM, &InvItemId, &VendorId, &UOM, &MaxEffdt, &RECITEM); */
       &new_UNIT_OF_MEASURE = &RECITEM2.UNIT_OF_MEASURE.Value;
       &REC2 = CreateRecord(Record.ITM_VNDR_UOM_PR);
       &REC2.SETID.Value = &RECITEM.SETID.Value;
       &REC2.INV_ITEM_ID.Value = &RECITEM2.INV_ITEM_ID.Value;
       &REC2.VENDOR_SETID.Value = &RECITEM2.VENDOR_SETID.Value;
       &REC2.VENDOR_ID.Value = &RECITEM2.VENDOR_ID.Value;
       &REC2.VNDR_LOC.Value = &RECITEM2.VNDR_LOC.Value;
       &REC2.UNIT_OF_MEASURE.Value = &RECITEM2.UNIT_OF_MEASURE.Value;
       &REC2.CURRENCY_CD.Value = &RECITEM2.CURRENCY_CD.Value;
       &REC2.QTY_MIN.Value = &RECITEM2.QTY_MIN.Value;
       &REC2.EFFDT.Value = &Effdt;
       &REC2.EFF_STATUS.Value = &RECITEM.EFF_STATUS.Value;
       &REC2.PRICE_VNDR.Value = &PriceVndr;
       &REC2.UNIT_PRC_TOL.Value = &RECITEM.UNIT_PRC_TOL.Value;
       &REC2.EXT_PRC_TOL.Value = &RECITEM.EXT_PRC_TOL.Value;
       &REC2.PCT_UNIT_PRC_TOL.Value = &RECITEM.PCT_UNIT_PRC_TOL.Value;
       &REC2.PCT_EXT_PRC_TOL.Value = &RECITEM.PCT_EXT_PRC_TOL.Value;
       &REC2.USE_STD_TOLERANCES.Value = &RECITEM.USE_STD_TOLERANCES.Value;
       &REC2.QTY_RECV_TOL_PCT.Value = &RECITEM.QTY_RECV_TOL_PCT.Value;
       &REC2.UNIT_PRC_TOL_L.Value = &RECITEM.UNIT_PRC_TOL_L.Value;
       &REC2.PCT_UNIT_PRC_TOL_L.Value = &RECITEM.PCT_UNIT_PRC_TOL_L.Value;
       &REC2.EXT_PRC_TOL_L.Value = &RECITEM.EXT_PRC_TOL_L.Value;
       &REC2.PCT_EXT_PRC_TOL_L.Value = &RECITEM.PCT_EXT_PRC_TOL_L.Value;
       &REC2.BU_PRICE_STATUS.Value = "2";
       &REC2.STD_PRICE_STATUS.Value = "2";
       &REC2.LEAD_TIME.Value = &RECITEM.LEAD_TIME.Value;
       &REC2.OPRID_MODIFIED_BY.Value = &Oprid;
       &REC2.LAST_DTTM_UPDATE.Value = &CurrentDateTime;
       &REC2.PRICE_CHANGE.Value = &RECITEM.PRICE_CHANGE.Value;
       &REC2.Insert();
       Break;
       End-If;  /*Kevin added this on 10/22 3:55PM */
      End-While;

       /* Else;  */
       /* Continue; */   

       /* Final Else - If all rows have been looped through and there was not a match found then do the following  */
      If Not &RowFound Then;
       SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND (CONVERT(CHAR(10),EFFDT,121)) = :5", &RECITEM3, &InvItemId, &VendorId, &UOM, &MaxEffdt, &RECITEM3);
       &REC3 = CreateRecord(Record.ITM_VNDR_UOM_PR);
       &new_UNIT_OF_MEASURE = &RECITEM2.UNIT_OF_MEASURE.Value;
       &REC2 = CreateRecord(Record.ITM_VNDR_UOM_PR);
       &REC2.SETID.Value = &RECITEM3.SETID.Value;
       &REC2.INV_ITEM_ID.Value = &RECITEM3.INV_ITEM_ID.Value;
       &REC2.VENDOR_SETID.Value = &RECITEM3.VENDOR_SETID.Value;
       &REC2.VENDOR_ID.Value = &RECITEM3.VENDOR_ID.Value;
       &REC2.VNDR_LOC.Value = &RECITEM3.VNDR_LOC.Value;
       &REC2.UNIT_OF_MEASURE.Value = &UOMResult
       &REC2.CURRENCY_CD.Value = &RECITEM3.CURRENCY_CD.Value;
       &REC2.QTY_MIN.Value = &RECITEM3.QTY_MIN.Value;
       &REC2.EFFDT.Value = &Effdt;
       &REC2.EFF_STATUS.Value = &RECITEM3.EFF_STATUS.Value;
       &REC2.PRICE_VNDR.Value = &PriceVndr;
       &REC.UNIT_PRC_TOL.Value = &RECITEM3.UNIT_PRC_TOL.Value;
       &REC2.EXT_PRC_TOL.Value = &RECITEM3.EXT_PRC_TOL.Value;
       &REC2.PCT_UNIT_PRC_TOL.Value = &RECITEM3.PCT_UNIT_PRC_TOL.Value;
       &REC2.PCT_EXT_PRC_TOL.Value = &RECITEM3.PCT_EXT_PRC_TOL.Value;
       &REC2.USE_STD_TOLERANCES.Value = &RECITEM3.USE_STD_TOLERANCES.Value;
       &REC2.QTY_RECV_TOL_PCT.Value = &RECITEM3.QTY_RECV_TOL_PCT.Value;
       &REC2.UNIT_PRC_TOL_L.Value = &RECITEM3.UNIT_PRC_TOL_L.Value;
       &REC2.PCT_UNIT_PRC_TOL_L.Value = &RECITEM3.PCT_UNIT_PRC_TOL_L.Value;
       &REC2.EXT_PRC_TOL_L.Value = &RECITEM3.EXT_PRC_TOL_L.Value;
       &REC2.PCT_EXT_PRC_TOL_L.Value = &RECITEM3.PCT_EXT_PRC_TOL_L.Value;
       &REC2.BU_PRICE_STATUS.Value = "2";
       &REC2.STD_PRICE_STATUS.Value = "2";
       &REC2.LEAD_TIME.Value = &RECITEM3.LEAD_TIME.Value;
       &REC2.OPRID_MODIFIED_BY.Value = &Oprid;
       &REC2.LAST_DTTM_UPDATE.Value = &CurrentDateTime;
       &REC2.PRICE_CHANGE.Value = &RECITEM3.PRICE_CHANGE.Value;
       &REC2.Insert();
     End-If;

    /* End-While; */
   Else;
      &CurrentDateTime = %Datetime;
      &Oprid = %OperatorId;
      &RECITEM = CreateRecord(Record.ITM_VNDR_UOM_PR);

      /*GETTING MAX EFFECTIVE DATED ROW TO COPY EXISTING ROW VALUES FROM INTO NEW ROW */
      SQLExec("SELECT C.EFFDT FROM PS_ITM_VNDR_UOM_PR C WHERE C.INV_ITEM_ID = :1 AND C.VENDOR_ID = :2 AND C.UNIT_OF_MEASURE = :3 AND C.EFFDT = (SELECT MAX(C1.EFFDT) FROM PS_ITM_VNDR_UOM_PR C1 WHERE C.SETID = C1.SETID AND C.INV_ITEM_ID = C1.INV_ITEM_ID AND C.VENDOR_SETID = C1.VENDOR_SETID AND C.VENDOR_ID = C1.VENDOR_ID AND C.VNDR_LOC = C1.VNDR_LOC AND C.UNIT_OF_MEASURE = C1.UNIT_OF_MEASURE AND C.CURRENCY_CD = C1.CURRENCY_CD AND C.QTY_MIN = C1.QTY_MIN)  ", &InvItemId, &VendorId, &UOM, &MaxEffdt);

      SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND (CONVERT(CHAR(10),EFFDT,121)) = :5", &RECITEM, &InvItemId, &VendorId, &UOM, &MaxEffdt, &RECITEM);
      /*SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND EFFDT = :5", &RECITEM, &InvItemId, &VendorId, &UOM, &MaxEffdt, &RECITEM); */
      &new_UNIT_OF_MEASURE = &RECITEM.UNIT_OF_MEASURE.Value;
      &REC = CreateRecord(Record.ITM_VNDR_UOM_PR);
      &REC.SETID.Value = &RECITEM.SETID.Value;
      &REC.INV_ITEM_ID.Value = &RECITEM.INV_ITEM_ID.Value;
      &REC.VENDOR_SETID.Value = &RECITEM.VENDOR_SETID.Value;
      &REC.VENDOR_ID.Value = &RECITEM.VENDOR_ID.Value;
      &REC.VNDR_LOC.Value = &RECITEM.VNDR_LOC.Value;
      &REC.UNIT_OF_MEASURE.Value = &RECITEM.UNIT_OF_MEASURE.Value;
      &REC.CURRENCY_CD.Value = &RECITEM.CURRENCY_CD.Value;
      &REC.QTY_MIN.Value = &RECITEM.QTY_MIN.Value;
      &REC.EFFDT.Value = &Effdt;
      &REC.EFF_STATUS.Value = &RECITEM.EFF_STATUS.Value;
      &REC.PRICE_VNDR.Value = &PriceVndr;
      &REC.UNIT_PRC_TOL.Value = &RECITEM.UNIT_PRC_TOL.Value;
      &REC.EXT_PRC_TOL.Value = &RECITEM.EXT_PRC_TOL.Value;
      &REC.PCT_UNIT_PRC_TOL.Value = &RECITEM.PCT_UNIT_PRC_TOL.Value;
      &REC.PCT_EXT_PRC_TOL.Value = &RECITEM.PCT_EXT_PRC_TOL.Value;
      &REC.USE_STD_TOLERANCES.Value = &RECITEM.USE_STD_TOLERANCES.Value;
      &REC.QTY_RECV_TOL_PCT.Value = &RECITEM.QTY_RECV_TOL_PCT.Value;
      &REC.UNIT_PRC_TOL_L.Value = &RECITEM.UNIT_PRC_TOL_L.Value;
      &REC.PCT_UNIT_PRC_TOL_L.Value = &RECITEM.PCT_UNIT_PRC_TOL_L.Value;
      &REC.EXT_PRC_TOL_L.Value = &RECITEM.EXT_PRC_TOL_L.Value;
      &REC.PCT_EXT_PRC_TOL_L.Value = &RECITEM.PCT_EXT_PRC_TOL_L.Value;
      &REC.BU_PRICE_STATUS.Value = "2";
      &REC.STD_PRICE_STATUS.Value = "2";
      &REC.LEAD_TIME.Value = &RECITEM.LEAD_TIME.Value;
      &REC.OPRID_MODIFIED_BY.Value = &Oprid;
      &REC.LAST_DTTM_UPDATE.Value = &CurrentDateTime;
      &REC.PRICE_CHANGE.Value = &RECITEM.PRICE_CHANGE.Value;
      &REC.Insert();

      /*MessageBox(0, "", 0, 0, &VendorId | " " | &InvItemId | " " | &ItmIdVndr | " " | &UOM | " " | &ConvRate | " " | &PriceVndr | " " | &Effdt);*/

   End-If;

   /* 10/11/2018 - Added field ITEM_FIELD_C10_B (&ItmField) to CSV file layout and insert into PS_MASTER_ITEM_TBL (&MASTER_ITEM_TBL) */
   If None(&ItmField) Then;
      &Error = "Y";
      MessageBox(0, "", 0, 0, &VendorId | " " | &InvItemId | " " | &ItmIdVndr | " " | &UOM | " " | &ConvRate | " " | &PriceVndr | " " | &Effdt | " " | &ItmField);
   Else;
      SQLExec("UPDATE PS_MASTER_ITEM_TBL SET ITEM_FIELD_C10_B = :1 WHERE INV_ITEM_ID = :2 AND SETID = 'SHARE' ", &ItmField, &InvItemId);
      /* Else;
       &REC1 = CreateRecord(Record.MASTER_ITEM_TBL);
       &REC1.ITEM_FIELD_C10_B.Value = &ItmField;
       &REC1.SETID.Value = "SHARE";
       &REC1.INV_ITEM_ID.Value = &InvItemId;
       &REC1.Update();
    End-If;  */

   End-If;

End-While;
MessageBox(0, "", 0, 0, "**** END OF VALIDATION ERRORS ****");
MessageBox(0, "", 0, 0, " ");

You have a lonely 'End-While;' inside your &UOM = &UOMResult tree. I am assuming you want to Break; in this case. Also &RECITEM2 and &REC2 are both created from ITM_VNDR_UOM_PR. You should use the record method RECITEM2.CopyFieldsTo(&REC2) and then set the changed fields in &REC2. This will make the entire thing a lot more readable. Your &RECITEM3 is also never created, so your %SelectAll(:1) can not possibly work. — Based, Oct 22 '18 at 15:21
Not sure I follow you with using the CopyFieldsTo method, can you provide an example where I can use this? Because I am having to process row by row in the SQL fetch, I want to keep checking if &UOM = &UOMResult, if it does then Break out of the loop, if not then I want the loop to continue/check the next row and continue doing this. If after it has processed all the rows in the loop and it has not found a row where &UOM = &UOMResult then (and only then) do I want to process logic under the "Final Else" comment. Hopefully I'm explaining this correctly. How can I accomplish this? — JBinson88, Oct 22 '18 at 16:00
You won't infinite loop because the &SQL2.Fetch will return false after the last row, therefore exiting the loop. There are a number of issues with your code so it won't compile. Each End If needs a starting IF. As for your insert a row if one isn't found. You should set a variable before the while loop, then change it's value when a row is found. After the loop, check if the variable was the original value, if so, do your insert. — Darryls99, Oct 22 '18 at 18:38
@Darryls99 - I like your idea about setting a variable before the loop and then checking the value after the loop. Could I do something like : Local number &RowFound .... (above While loop): In&RowFound = 0 , (in While loop): &RowFound = &RowFound + 1 , (after While loop): If &RowFound > 1 Then ..... Just wanted to make sure the above will increment the number properly — JBinson88, Oct 22 '18 at 19:26
@JBinson88 https://pastebin.com/DVbMYH4V Secord part will make your code a lot more readable and easier to understand. — Based, Oct 23 '18 at 07:07

score 1 · Answer 1 · answered Oct 22 '18 at 22:12

1

You want to set a variable in the while to determine if a record is found, if it's not found then do your alternative insert logic. In this case we'll use the variable &rowFound to determine if there's a match.

Local Boolean &rowFound;
Local SQL &sql;

&sql = createsql("SELECT C.UNIT_OF_MEASURE FROM PS_ITM_VNDR_UOM_PR C WHERE C.INV_ITEM_ID = :1 AND C.VENDOR_ID = :2 AND C.EFFDT = (SELECT MAX(C1.EFFDT) FROM PS_ITM_VNDR_UOM_PR C1 WHERE C.SETID = C1.SETID AND C.INV_ITEM_ID = C1.INV_ITEM_ID AND C.VENDOR_SETID = C1.VENDOR_SETID AND C.VENDOR_ID = C1.VENDOR_ID AND C.VNDR_LOC = C1.VNDR_LOC AND C.UNIT_OF_MEASURE = C1.UNIT_OF_MEASURE AND C.CURRENCY_CD = C1.CURRENCY_CD AND C.QTY_MIN = C1.QTY_MIN)",&InvItemId, &VendorID);

&rowFound = False;
while &sql.fetch(&UOMResult)
    If &UOM = &UOMResult Then
        &rowFound = True;
        /* Do Insert Code here */
        break;
     end-if;
end-while;
&sql.close;

if not &rowFound then 
    /* Insert Logic if it's not found */
end-if

answered Oct 22 '18 at 22:12

Darryls99

921
6
11

Thanks Darryls999! For some reason my updated program is giving me an error: "Syntax error; expecting end-if" I've checked the program and I appear to have the appropriate If / End-If tags so I'm not sure why I keep getting this error. I updated the post above under 10/23 Edit, don't know if you can spot anything obvious? – JBinson88 Oct 23 '18 at 18:56
Ok I made a change - added parenthesis around `If not (&RowFound) then` and the error didn't come back. When I run the program it goes to No Success and there is another error in the Trace file - "SqlExec parameter 2 is not a record object. (2,603) GH_ITM_PR_UP.MAIN.GBL.default.1900-01-01.Step04.OnExecute PCPC:8543 Statement:55 121243 15:48:14.130 0.000000 <<< end Nest=00 GH_ITM_PR_UP.MAIN.GBL.default.1900-01-01.Step04.OnExecute Dur=0.283518 CPU=0.046875 Cycles=197" – JBinson88 Oct 23 '18 at 20:13
@JBinson88 As I mentioned before: in line 71 you are doing a SQLExec(%SelectAll(&RECITEM3)), but you never actually did a CreateRecord() on &RECITEM3. – Based Oct 24 '18 at 09:00
Ok I fixed that and that is working now (thank you!) I seem to now be getting another error: " `First operand of . is not an object, so cannot access member SETID. (180,235) GH_ITM_PR_UP.MAIN.GBL. default.1900-01-01.Step04.OnExecute PCPC:5028 Statement:23 ` If I modify the &SQL2 statement to be (added TOP 1) `&SQL2 = CreateSQL(SELECT TOP 1 C.UNIT_OF_MEASURE FROM PS_ITM_VNDR_UOM_PR C WHERE....` Then the process runs successfully. I don't want to have to use TOP 1 though so not sure why I am getting the error ? Thanks so much for all your help as I'm exploring uncharted territory – JBinson88 Oct 24 '18 at 15:20
What is strange is that if I delete a row (that was just added by the program) in the PS_ITM_VNDR_UOM_PR table and re-run the program then it goes to Success, however if I don't delete the row added and I run the program again (for that same item / row still in table) then I get the above error message... – JBinson88 Oct 24 '18 at 15:51
Think I figured it out - I had an old reference to &REC.SETID , instead of &REC2.SETID. Do you have any suggestions for dealing with NULL values retrieved from a SQLExec function? I have some cases where I am Selecting C.EFFDT FROM PS_ITM_VNDR_UOM_PR however the returned value is NULL in the table. Is there a way to dynamically change the SQL statement in SQLExec if the column LAST_DTTM_UPDATE is Null? If the LAST_DTTM_UPDATE value found is NULL, then I want the SQLExec to use the MAX EFFDT instead in the WHERE clause. Not sure if this is even possible... – JBinson88 Oct 24 '18 at 17:59
@JBinson88 Sounds like a job for [NVL()](https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions105.htm) Or just WHERE (X = LAST_DTTM_UPDATE AND LAST_DTTM_UPDATE IS NOT NULL OR X = (SELECT MAX(EFFDT) FROM ...) AND LAST_DTTM_UPDATE IS NULL – Based Oct 26 '18 at 08:33

Perform logic if last row of SQL Fetch/Loop doesn't find match

1 Answers1