Peoplecode Help Needed

577 views Asked by At

I'm trying to write my first peoplecode step in an Application Engine and am lost.

I have 2 tables. Table A is populated with results from several selects and updates in the engine. Table B has 1 row of data that I'm using to store results from a count and math.

I'm trying to write code that will loop through Table A until it hits a certain row (the table is already ordered). Once it lands on that row, I want to update 1 field in Table B with data from Table A. I want to do this 3 times.

The overall idea is.. if you have a count of 100 from Table A, divide by 4, then, count to row 25 in Table A, and grab an id. Then, row 50, then row 75.

Here is the code I currently have. (The first loop) It's giving me an error that "WARNING: NO DEDICATED INSTANCES AVAILABLE FOR K_EBILL_TMP - USING BASE TABLE. (108,544)"

For reference, K_EBILL_TMP is Table B and K_SF_BILL_TAO is Table A.

Local Record &recECQ;
Local SQL &sqlECQ;

Local string &strID;
Local number &intCount;
Local number &intRecCount;
Local number &intRecCount2;
Local number &intRecCount3;
Local number &intProcessNum;

&intCount = 1;


&intProcessNum = K_EBILL_TMP.PROCESS_INSTANCE.Value;
&intRecCount = K_EBILL_TMP.K_IDCNT_SPLT.Value;

/*This run is to get the common_id for the first batch of runs*/

/*What ARE YOU LOOPING THROUGH?*/

While &intCount <= &intRecCount;
   &intCount = &intCount + 1;
   If &intCount = &intRecCount Then
      &strID = K_SF_BILL_TAO.COMMON_ID.Value;
      &recECQ = CreateRecord(Record.PS_K_EBILL_TMP);
      &sqlECQ = CreateSQL("UPDATE ps_k_ebill_tmp set K_FRST_ID = :1 where PROCESS_INSTANCE = :2", &strID, &intProcessNum);
   End-If;
End-While;

1

There are 1 answers

0
jim.marion On

Interesting. First, the "Error" is only a warning. If your App Engine is failing, that is not why. There is nothing wrong with using the base table as long as you properly partition your data by including the PROCESS_INSTANCE in your inserts, updates, etc.

The App Engine may be using the base table because the TMP isn't listed as a temp table in the AE properties or there are several ABENDS in the PeopleTools > App Engine > Manage abends component that have all of the existing TMP tables locked.

You may want to review this section in PeopleBooks: https://docs.oracle.com/cd/F28299_01/pt857pbr3/eng/pt/tape/concept_UnderstandingTemporaryTables-077144.html?pli=ul_d26e120_tape.

Since the App Engine is using the base table, not an instance table, your CreateSQL works. But normally, you would use "UPDATE %Table(k_ebill_tmp)..."; to select the proper instance table.

I also don't see where you are executing the SQL within the loop. Normally you would use CreateSQL outside the loop (no parameters except the SQL) and then &sqlECQ.Execute(&strID, &intProcessNum) within the loop.

It appears this line doesn't do anything either: &recECQ = CreateRecord(Record.PS_K_EBILL_TMP);

FYI, we normally use TAO as the suffix for tables used as temp tables in App Engine.