title: How to store multiple SQLExec values in variable(s)
body: <p>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? </p>
  
  <p>For example - I am working on adding the following code:</p>
  
  <p><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)" , &amp;InvItemId, &amp;VendorID, &amp;UOMResult</code></p>
  
  <p>In certain cases more than one row (value) may be passed back into the &amp;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 &amp;UOMResult variable to another variable called &amp;UOM and if they are different (don't have a match) then do certain "stuff", else do "other stuff". </p>
  
  <p>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.</p>
  
  <p><strong>10/17/18 EDIT:</strong></p>
  
  <pre><code>Local SQL &amp;SQL;
      Local string &amp;VendorId, &amp;InvItemId, &amp;ItmIdVndr, &amp;UOM, &amp;ConvRate, &amp;PriceVndr, &amp;Oprid, &amp;PriceStatus, &amp;VendorId_check, &amp;insert_cols, &amp;insert_select, &amp;insert_where, &amp;insert_sql, &amp;Error, &amp;MaxEffdt, &amp;ItmField;
      Local date &amp;Effdt;
      Local Record &amp;ITM_VNDR_UOM_PR, &amp;REC, &amp;MASTER_ITEM_TBL;
  
      &amp;Oprid = "'BATCH'";
      &amp;PriceStatus = "2";
      &amp;Error = "N";
  
      MessageBox(0, "", 0, 0, "**** BEGINING OF VALIDATION ERRORS ****");
  
      &amp;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", &amp;VendorId, &amp;InvItemId, &amp;ItmIdVndr, &amp;UOM, &amp;ConvRate, &amp;PriceVndr, &amp;Effdt, &amp;ItmField);
      MessageBox(0, "", 0, 0, &amp;VendorId | " " | &amp;InvItemId | " " | &amp;ItmIdVndr | " " | &amp;UOM | " " | &amp;ConvRate | " " | &amp;PriceVndr | " " | &amp;Effdt | " " | &amp;ItmField | " " | &amp;Error);
      While &amp;SQL.Fetch(&amp;VendorId, &amp;InvItemId, &amp;ItmIdVndr, &amp;UOM, &amp;ConvRate, &amp;PriceVndr, &amp;Effdt, &amp;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", &amp;VendorId, &amp;InvItemId, &amp;ItmIdVndr, &amp;UOM, &amp;ConvRate, &amp;VendorId_check);
  
         If None(&amp;VendorId_check) Then;
            &amp;Error = "Y";
            MessageBox(0, "", 0, 0, &amp;VendorId | " " | &amp;InvItemId | " " | &amp;ItmIdVndr | " " | &amp;UOM | " " | &amp;ConvRate | " " | &amp;PriceVndr | " " | &amp;Effdt | " " | &amp;ItmField | " " | &amp;Error);
         Else;
            &amp;CurrentDateTime = %Datetime;
            &amp;Oprid = %OperatorId;
            &amp;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)  ", &amp;InvItemId, &amp;VendorId, &amp;UOM, &amp;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)  ", &amp;InvItemId, &amp;VendorId, &amp;UOM, &amp;MaxEffdt);
  
           &amp;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) ", &amp;InvItemId, &amp;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 &amp;SQL2 and compare to my &amp;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 &amp;SQL2.Fetch(&amp;UOMResult)
            If &amp;UOM = &amp;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", &amp;RECITEM, &amp;InvItemId, &amp;VendorId, &amp;UOM, &amp;MaxEffdt, &amp;RECITEM);
            /*SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND EFFDT = :5", &amp;RECITEM, &amp;InvItemId, &amp;VendorId, &amp;UOM, &amp;MaxEffdt, &amp;RECITEM); */
            &amp;new_UNIT_OF_MEASURE = &amp;RECITEM.UNIT_OF_MEASURE.Value;
            &amp;REC = CreateRecord(Record.ITM_VNDR_UOM_PR);
            &amp;REC.SETID.Value = &amp;RECITEM.SETID.Value;
            &amp;REC.INV_ITEM_ID.Value = &amp;RECITEM.INV_ITEM_ID.Value;
            &amp;REC.VENDOR_SETID.Value = &amp;RECITEM.VENDOR_SETID.Value;
            &amp;REC.VENDOR_ID.Value = &amp;RECITEM.VENDOR_ID.Value;
            &amp;REC.VNDR_LOC.Value = &amp;RECITEM.VNDR_LOC.Value;
            &amp;REC.UNIT_OF_MEASURE.Value = &amp;RECITEM.UNIT_OF_MEASURE.Value;
            &amp;REC.CURRENCY_CD.Value = &amp;RECITEM.CURRENCY_CD.Value;
            &amp;REC.QTY_MIN.Value = &amp;RECITEM.QTY_MIN.Value;
            &amp;REC.EFFDT.Value = &amp;Effdt;
            &amp;REC.EFF_STATUS.Value = &amp;RECITEM.EFF_STATUS.Value;
            &amp;REC.PRICE_VNDR.Value = &amp;PriceVndr;
            &amp;REC.UNIT_PRC_TOL.Value = &amp;RECITEM.UNIT_PRC_TOL.Value;
            &amp;REC.EXT_PRC_TOL.Value = &amp;RECITEM.EXT_PRC_TOL.Value;
            &amp;REC.PCT_UNIT_PRC_TOL.Value = &amp;RECITEM.PCT_UNIT_PRC_TOL.Value;
            &amp;REC.PCT_EXT_PRC_TOL.Value = &amp;RECITEM.PCT_EXT_PRC_TOL.Value;
            &amp;REC.USE_STD_TOLERANCES.Value = &amp;RECITEM.USE_STD_TOLERANCES.Value;
            &amp;REC.QTY_RECV_TOL_PCT.Value = &amp;RECITEM.QTY_RECV_TOL_PCT.Value;
            &amp;REC.UNIT_PRC_TOL_L.Value = &amp;RECITEM.UNIT_PRC_TOL_L.Value;
            &amp;REC.PCT_UNIT_PRC_TOL_L.Value = &amp;RECITEM.PCT_UNIT_PRC_TOL_L.Value;
            &amp;REC.EXT_PRC_TOL_L.Value = &amp;RECITEM.EXT_PRC_TOL_L.Value;
            &amp;REC.PCT_EXT_PRC_TOL_L.Value = &amp;RECITEM.PCT_EXT_PRC_TOL_L.Value;
            &amp;REC.BU_PRICE_STATUS.Value = "2";
            &amp;REC.STD_PRICE_STATUS.Value = "2";
            &amp;REC.LEAD_TIME.Value = &amp;RECITEM.LEAD_TIME.Value;
            &amp;REC.OPRID_MODIFIED_BY.Value = &amp;Oprid;
            &amp;REC.LAST_DTTM_UPDATE.Value = &amp;CurrentDateTime;
            &amp;REC.PRICE_CHANGE.Value = &amp;RECITEM.PRICE_CHANGE.Value;
            &amp;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", &amp;RECITEM, &amp;InvItemId, &amp;VendorId, &amp;UOMResult, &amp;MaxEffdt, &amp;RECITEM);
            /*SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND EFFDT = :5", &amp;RECITEM, &amp;InvItemId, &amp;VendorId, &amp;UOMResult, &amp;MaxEffdt, &amp;RECITEM); */
            &amp;new_UNIT_OF_MEASURE = &amp;RECITEM.UNIT_OF_MEASURE.Value;
            &amp;REC = CreateRecord(Record.ITM_VNDR_UOM_PR);
            &amp;REC.SETID.Value = &amp;RECITEM.SETID.Value;
            &amp;REC.INV_ITEM_ID.Value = &amp;RECITEM.INV_ITEM_ID.Value;
            &amp;REC.VENDOR_SETID.Value = &amp;RECITEM.VENDOR_SETID.Value;
            &amp;REC.VENDOR_ID.Value = &amp;RECITEM.VENDOR_ID.Value;
            &amp;REC.VNDR_LOC.Value = &amp;RECITEM.VNDR_LOC.Value;
            &amp;REC.UNIT_OF_MEASURE.Value = &amp;RECITEM.UNIT_OF_MEASURE.Value;
            &amp;REC.CURRENCY_CD.Value = &amp;RECITEM.CURRENCY_CD.Value;
            &amp;REC.QTY_MIN.Value = &amp;RECITEM.QTY_MIN.Value;
            &amp;REC.EFFDT.Value = &amp;Effdt;
            &amp;REC.EFF_STATUS.Value = &amp;RECITEM.EFF_STATUS.Value;
            &amp;REC.PRICE_VNDR.Value = &amp;PriceVndr;
            &amp;REC.UNIT_PRC_TOL.Value = &amp;RECITEM.UNIT_PRC_TOL.Value;
            &amp;REC.EXT_PRC_TOL.Value = &amp;RECITEM.EXT_PRC_TOL.Value;
            &amp;REC.PCT_UNIT_PRC_TOL.Value = &amp;RECITEM.PCT_UNIT_PRC_TOL.Value;
            &amp;REC.PCT_EXT_PRC_TOL.Value = &amp;RECITEM.PCT_EXT_PRC_TOL.Value;
            &amp;REC.USE_STD_TOLERANCES.Value = &amp;RECITEM.USE_STD_TOLERANCES.Value;
            &amp;REC.QTY_RECV_TOL_PCT.Value = &amp;RECITEM.QTY_RECV_TOL_PCT.Value;
            &amp;REC.UNIT_PRC_TOL_L.Value = &amp;RECITEM.UNIT_PRC_TOL_L.Value;
            &amp;REC.PCT_UNIT_PRC_TOL_L.Value = &amp;RECITEM.PCT_UNIT_PRC_TOL_L.Value;
            &amp;REC.EXT_PRC_TOL_L.Value = &amp;RECITEM.EXT_PRC_TOL_L.Value;
            &amp;REC.PCT_EXT_PRC_TOL_L.Value = &amp;RECITEM.PCT_EXT_PRC_TOL_L.Value;
            &amp;REC.BU_PRICE_STATUS.Value = "2";
            &amp;REC.STD_PRICE_STATUS.Value = "2";
            &amp;REC.LEAD_TIME.Value = &amp;RECITEM.LEAD_TIME.Value;
            &amp;REC.OPRID_MODIFIED_BY.Value = &amp;Oprid;
            &amp;REC.LAST_DTTM_UPDATE.Value = &amp;CurrentDateTime;
            &amp;REC.PRICE_CHANGE.Value = &amp;RECITEM.PRICE_CHANGE.Value;
            &amp;REC.Insert();
  
  
           End-If;
           End-While;
  
  
         End-If;
  
         /* KDR - 10/11/2018 - Added field ITEM_FIELD_C10_B (&amp;ItmField) to CSV file layout and insert into PS_MASTER_ITEM_TBL (&amp;MASTER_ITEM_TBL) */
         If None(&amp;ItmField) Then;
            &amp;Error = "Y";
            MessageBox(0, "", 0, 0, &amp;VendorId | " " | &amp;InvItemId | " " | &amp;ItmIdVndr | " " | &amp;UOM | " " | &amp;ConvRate | " " | &amp;PriceVndr | " " | &amp;Effdt | " " | &amp;ItmField);
         Else;
            SQLExec("UPDATE PS_MASTER_ITEM_TBL SET ITEM_FIELD_C10_B = :1 WHERE INV_ITEM_ID = :2 AND SETID = 'SHARE' ", &amp;ItmField, &amp;InvItemId);
            /* Else;
             &amp;REC1 = CreateRecord(Record.MASTER_ITEM_TBL);
             &amp;REC1.ITEM_FIELD_C10_B.Value = &amp;ItmField;
             &amp;REC1.SETID.Value = "SHARE";
             &amp;REC1.INV_ITEM_ID.Value = &amp;InvItemId;
             &amp;REC1.Update();
          End-If;  */
  
         End-If;
  
      End-While;
      MessageBox(0, "", 0, 0, "**** END OF VALIDATION ERRORS ****");
      MessageBox(0, "", 0, 0, " ");`
  </code></pre>