CreateRecord更新方法不更新表

Question

我已经修改了下面的App Engine程序，以使用文件布局更新现有行上的字段。我添加了代码来更新已填充了新供应商项目ID的项目的现有行。虽然程序在运行时不会出错，但它实际上也不会使用ITM_ID_VNDR字段更新表PS_ITM_VENDOR。程序适当地读取文件布局，我可以显示ITM_ID_VNDR的变量。我不确定是否为CreateRecord类正确使用了.Update方法。这些更改由底部的“1/7/2020”注释表示。

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

&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), ITM_ID_VNDR_NEW FROM PS_GH_ITM_PR_UPDT ORDER BY VENDOR_ID, INV_ITEM_ID, ITM_ID_VNDR", &VendorId, &InvItemId, &ItmIdVndr, &UOM, &ConvRate, &PriceVndr, &Effdt, &ItmIdVndrNew);

While &SQL.Fetch(&VendorId, &InvItemId, &ItmIdVndr, &UOM, &ConvRate, &PriceVndr, &Effdt, &ItmIdVndrNew)

   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 | " " | &ItmIdVndrNew);
   Else;
      &CurrentDateTime = %Datetime;
      &Oprid = %OperatorId;
      &RECITEM = CreateRecord(Record.ITM_VNDR_UOM_PR);
      /*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);*/
      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);
      /*CHC_PO_96588 - End */
      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.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();
      /* 10/30/2014 - Writes line if good */
      /*MessageBox(0, "", 0, 0, &VendorId | " " | &InvItemId | " " | &ItmIdVndr | " " | &UOM | " " | &ConvRate | " " | &PriceVndr | " " | &Effdt);*/
   End-If;
End-While;

/* 1/7/2020 BEGIN Changes for ITEM_ID_VNDR */
&SQL2 = CreateSQL("SELECT VENDOR_ID, INV_ITEM_ID, ITM_ID_VNDR, UNIT_OF_MEASURE, CONVERSION_RATE, PRICE_VNDR, %DateOut(EFFDT), ITM_ID_VNDR_NEW FROM PS_GH_ITM_PR_UPDT ORDER BY VENDOR_ID, INV_ITEM_ID, ITM_ID_VNDR", &VendorId, &InvItemId, &ItmIdVndr, &UOM, &ConvRate, &PriceVndr, &Effdt, &ItmIdVndrNew);
While &SQL2.Fetch(&VendorId, &InvItemId, &ItmIdVndr, &UOM, &ConvRate, &PriceVndr, &Effdt, &ItmIdVndrNew)

   If None(&ItmIdVndrNew) Then;
      &Error = "Y";
      MessageBox(0, "", 0, 0, &VendorId | " " | &InvItemId | " " | &ItmIdVndr | " " | &UOM | " " | &ConvRate | " " | &PriceVndr | " " | &Effdt | " " | &ItmField | " " | &ItmIdVndrNew);
      MessageBox(0, "", 0, 0, "**** INSIDE MAIN IF CONDITION ****");
   Else;
      MessageBox(0, "", 0, 0, "**** INSIDE ELSE CONDITION ****");
      &REC1 = CreateRecord(Record.ITM_VENDOR);
      &REC1.ITM_ID_VNDR.Value = &ItmIdVndrNew;
      &REC1.Update();
   End-If;
End-While;
/* 1/7/2020  END Changes for ITEM_ID_VNDR */

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

更新:我可以在Trace文件中看到，我在尝试更新时没有拾取表的键字段。我认为这是问题的一部分……

8830     10:51:28.860     0.000000 Cur#1.23796.FSDEV92C RC=0 Dur=0.000000 COM Stmt=UPDATE PS_ITM_VENDOR SET ITM_ID_VNDR=:1 WHERE SETID=:2 AND INV_ITEM_ID=:3 AND VENDOR_SETID=:4 AND VENDOR_ID=:5
8831     10:51:28.861     0.000000 Cur#1.23796.FSDEV92C RC=0 Dur=0.000000 Bind-1 type=2 length=7 value=1234567
8832     10:51:28.861     0.001000 Cur#1.23796.FSDEV92C RC=0 Dur=0.000000 Bind-2 type=2 length=1 value= 
8833     10:51:28.862     0.000000 Cur#1.23796.FSDEV92C RC=0 Dur=0.000000 Bind-3 type=2 length=1 value= 
8834     10:51:28.862     0.001000 Cur#1.23796.FSDEV92C RC=0 Dur=0.000000 Bind-4 type=2 length=1 value= 
8835     10:51:28.863     0.000000 Cur#1.23796.FSDEV92C RC=0 Dur=0.000000 Bind-5 type=2 length=1 value= 
8836     10:51:28.864     0.001000 Cur#1.23796.FSDEV92C RC=0 Dur=0.001000 EXE
8837     10:51:28.865     0.000000 Cur#1.23796.FSDEV92C RC=0 Dur=0.000000 ROW #rows=0
8838     10:51:28.865     0.001000 Cur#5.23796.FSDEV92C RC=0 Dur=0.000000 Fetch

Answer 1

您只需设置&REC1.ITM_ID_VNDR.Value。

在调用Update()方法之前，您需要设置&REC1中的所有密钥。

Answer 2

我想我已经发现了这个问题，我需要将&REC1绑定到&REC2，如下所示：

&REC1 = CreateRecord(Record.ITM_VENDOR);
&REC2 = CreateRecord(Record.ITM_VENDOR);
.....
&REC2.Update(&REC1);