How to store multiple SQLExec values in variable(s)

Question

I am wondering whether in an Application Engine PeopleCode I can run a SQLExec command and if multiple rows are returned how they are stored in the variable?

For example - I am working on adding the following code:

SQLExec("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

In certain cases more than one row (value) may be passed back into the &UOMResult variable from the SQL query and I am not sure how PeopleCode will handle this, but ultimately I want to compare the value(s) from the &UOMResult variable to another variable called &UOM and if they are different (don't have a match) then do certain "stuff", else do "other stuff".

Does the variable get transformed into an array if there is more than 1 value returned from the SQL? Just wondering what this would look like... Thanks for any feedback you have.

10/17/18 EDIT:

Local SQL &SQL;
    Local string &VendorId, &InvItemId, &ItmIdVndr, &UOM, &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);
       Else;
          &CurrentDateTime = %Datetime;
          &Oprid = %OperatorId;
          &RECITEM = CreateRecord(Record.ITM_VNDR_UOM_PR);
          /*CHC Modification - Denise Smith - 8/8/2012 - CHC_PO_96588 - Begin */
          /*SQLExec("SELECT TO_CHAR(C.EFFDT,'DD-MON-YYYY') 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);*/

          /*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);

         &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);
/* This is where I am afraid the While loop will create extra inserts if the SQL above contains more than 1 Row. That is why I only want to evaluate only once if possible. For example if I am wanting to add a new price with a new UOM -'TP' that does not already exist for this item I want to look at the output value(s) from above &SQL2 and compare to my &UOM variable. If I keep this logic in a While loop I'm afraid it will end up creating more insert's then necessary */
         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", &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();

         Else;

    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, &UOMResult, &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, &UOMResult, &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;
         End-While;


       End-If;

       /* KDR - 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, " ");`

Answer 1

SQLExec only ever stores the results of 1 row. If you want to be able to loop through multiple rows returned you need to use a SQL object. Further documentation can be found in the Enterprise PeopleTools PeopleBook: PeopleCode API Reference > SQL Class

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);

while &sql.fetch(&UOMResult)

/* Do you logic here */
end-while;
&sql.close;

Answer 2

When I use App Engine and I have to create 2 or more result sets based on a differing set of criteria, I take 2 approaches:

1. if can I use case statements in the field select clause to achieve my objective, then this is the preferred method.
2. write SQLs in multiple steps that are mutually exclusive (from source table perspective) and populate the same target table.

If possible, I will avoid manipulating SQL with PeopleCode because then you have to deal with PeopleCode quirks as well as the database itself.