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

score 3 · Answer 1 · answered Oct 16 '18 at 18:22

3

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;

answered Oct 16 '18 at 18:22

Darryls99

921
6
11

Thanks for the feedback, it sounds like I don't need to use any kind of array then to hold the values - So I'm curious what happens when there are multiple rows returned from one instance of the fetch. So my SQL results are stored in the @UOMResult variable, but then if there was say 3 rows returned, how do I access each row, or a specific row? – JBinson88 Oct 17 '18 at 14:52
In other words I would like to be able to know during the fetch if any of the values returned in the &UOMResult variable match my &UOM variable, if it does or doesnt match then I want to ultimately do a &REC.Insert, only with using the variable &UOM if there is a match, and using &UOMResult if there is not a match. The problem I see with using a While loop is that it's going to keep reading each row and create a new record until all rows are processed from the While -SQL Fetch, and I only want to run the SQL once and then evaluate with an If statement. I edited the post above with more clarity – JBinson88 Oct 17 '18 at 18:14
Thinking about it more - Would there be a way to code it something like While the SQL2 Fetch is running if the (1st) value returned to the &UOMResult variable is equal to the &UOM variable then do Rec.Insert logic and break out of the loop, else Re-enter loop (to evaluate next row) and go through same logic until either it finds another row where &UOMResult is equal to &UOM or after all rows have been evaluated and there was not a match found then go through a 2nd set of Rec.Insert logic. Hopefully that makes sense? I appreciate any help! Thanks. – JBinson88 Oct 17 '18 at 19:58
Your logic makes sense and PeopleCode has a break command. So write the if statement and if it's true do the insert and then the break. See PeopleBooks PeopleCode Language Reference for more details. – Darryls99 Oct 18 '18 at 19:53

score 0 · Answer 2 · answered Jan 17 '19 at 09:21

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:

if can I use case statements in the field select clause to achieve my objective, then this is the preferred method.
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.

How to store multiple SQLExec values in variable(s)

2 Answers2