Table I/O Delete

jtlaser

Member
Hello,

I may be misunderstanding how to use the Delete functionality here, but all I want to do is delete from a custom table. I've tried a few things, I really just needed to delete everything with the Record Identifier. I only added the event variables, when I couldn't get the Record Identifier to work.

Every Column in this table is listed in the Fetch Single.
Code: 
0162 !          F596116P.Select
     !             VA evt_mnRecordIdentifier =  TK Self Service Record Identifier
0163 !          F596116P.Fetch Next
     !             VA evt_AddressNumber <- TK Address Number
     !             VA evt_DtWorkJulian <- TK Date - Worked
     !             VA evt_HoursWork <- TK Hours Worked
     !             VA evt_RtHourly <- TK Rate - Hourly
     !             VA evt_PayDeductBenAccType <- TK DBA Code
     !             VA evt_FlsaExemptYN <- TK FLSA Exempt Y/N
     !             VA evt_StatusFlag <- TK Time Entry Status Flag
     !             VA evt_ProgramId <- TK Program ID
     !             VA evt_UserId <- TK User ID
     !             VA evt_DateUpdated <- TK Date - Updated
     !             VA evt_TimeLastUpdated <- TK Time - Last Updated
0164          F596116P.Fetch Single
                 VA evt_AddressNumber <- TK Address Number
                 VA evt_mnRecordIdentifier =  TK Self Service Record Identifier
                 VA evt_DtWorkJulian <- TK Date - Worked
                 VA evt_HoursWork <- TK Hours Worked
                 VA evt_RtHourly <- TK Rate - Hourly
                 VA evt_PayDeductBenAccType <- TK DBA Code
                 VA evt_FlsaExemptYN <- TK FLSA Exempt Y/N
                 VA evt_StatusFlag <- TK Time Entry Status Flag
                 VA evt_ProgramId <- TK Program ID
                 VA evt_UserId <- TK User ID
                 VA evt_DateUpdated <- TK Date - Updated
                 VA evt_TimeLastUpdated <- TK Time - Last Updated
0165          If SV File_IO_Status is equal to CO SUCCESS
0166             While SV File_IO_Status is equal to CO SUCCESS
0167                F596116P.Delete
                       VA evt_AddressNumber =  TK Address Number
                       VA evt_mnRecordIdentifier =  TK Self Service Record Identifier
                       VA evt_DtWorkJulian =  TK Date - Worked
                       VA evt_HoursWork =  TK Hours Worked
                       VA evt_RtHourly =  TK Rate - Hourly
                       VA evt_PayDeductBenAccType =  TK DBA Code
                       VA evt_FlsaExemptYN =  TK FLSA Exempt Y/N
                       VA evt_StatusFlag =  TK Time Entry Status Flag
                       VA evt_ProgramId =  TK Program ID
                       VA evt_UserId =  TK User ID
                       VA evt_DateUpdated =  TK Date - Updated
                       VA evt_TimeLastUpdated =  TK Time - Last Updated
0168                F596116P.Fetch Single
                       VA evt_AddressNumber <- TK Address Number
                       VA evt_mnRecordIdentifier =  TK Self Service Record Identifier
                       VA evt_DtWorkJulian <- TK Date - Worked
                       VA evt_HoursWork <- TK Hours Worked
                       VA evt_RtHourly <- TK Rate - Hourly
                       VA evt_PayDeductBenAccType <- TK DBA Code
                       VA evt_FlsaExemptYN <- TK FLSA Exempt Y/N
                       VA evt_StatusFlag <- TK Time Entry Status Flag
                       VA evt_ProgramId <- TK Program ID
                       VA evt_UserId <- TK User ID
                       VA evt_DateUpdated <- TK Date - Updated
                       VA evt_TimeLastUpdated <- TK Time - Last Updated
0169 !                F596116P.Fetch Next
     !                   VA evt_AddressNumber <- TK Address Number
     !                   VA evt_DtWorkJulian <- TK Date - Worked
     !                   VA evt_HoursWork <- TK Hours Worked
     !                   VA evt_RtHourly <- TK Rate - Hourly
     !                   VA evt_PayDeductBenAccType <- TK DBA Code
     !                   VA evt_FlsaExemptYN <- TK FLSA Exempt Y/N
     !                   VA evt_StatusFlag <- TK Time Entry Status Flag
     !                   VA evt_ProgramId <- TK Program ID
     !                   VA evt_UserId <- TK User ID
     !                   VA evt_DateUpdated <- TK Date - Updated
     !                   VA evt_TimeLastUpdated <- TK Time - Last Updated
0170             End While
0171          End If

If it's not this, then maybe it's the way I created the table? I went back and added a separate key for just Record Identifier, thinking that may be the issue if I didn't use REID and DWK, which is my primary key to find a cell in the grid. I regenerated the Indices and the Table after I did this.

Any help would be appreciated.

-Jonathan
 
Hi Jonathan,

First of all how do you generate record identifier in the table? instead of record identifier better you generate UKID_Unique Key ID using X00022_Generate Next Unique ID Number Business Function in your custom table F596116P. If you pass all the variables/fields to delete, It will consider all the fields and search the recod with all the fields you are passing, if any record is getting match with passing fields, then only it deletes the particular record. assume you are having the table like below given.
Record No.AN8ALPHDEPTUKIDPID
14848AHR1R55XXXX
24848BMARKETING1R55XXXX
34847AHR2R55XXXX
44847BMARKETING2R55XXXX
54850CACCOUNTS1R55XXXX
64850DACCOUNTS3R55XXXX
74851AFINANCE3R55XXXX
84852AFINANCE3R55XXXX
94853BHR1R55XXXX
104854BIT4R55XXXX
114855DIT4R55XXXX

AN8 AND APLH are the primary key in the above table.
If pass an8=4848 then record NOs 1,2 will be deleted.
If pass an8=4848, alph=A, then only 1st record will be delete.
if pass alph=A, then 1,3,7,8 will be deleted.
if pass dept=FINACE, alph=A then 7,8th records will be deleted.
if pass UKID=1, then 1,2,5,9th records will be deleted.
if pass PID=R55XXXX all the records will be deleted

It all depends on what you are passing data (to identify the records) to delete the records.
I believe it will help you to get clear understanding about delete functionality.
still you have any query, you can reach me out at [email protected]
 
You won't loop there.

You need to select then 2 fetch next commands
Replace your fetch singles with the fetch next and add a select at the start

Plus....
If you know what mnRecordIdentifier just delete using that. No need to loop
 
Thanks for the response. As you can see, I actually had the Select, Fetch Nextx2 in there, I just commented it out. The operation is actually looping through, as I just entered 9 additional entries and deleted them and the logs show it worked, see below, however there is still data in the table. I started work on this, this weekend, so I'm asking my DBA now to see if there's anything on the database side.

Code: 
27 Jun 2016 07:48:40,828 [APP   ] JTHERIOT - [RUNTIME]         [B]Number of execution : 9[/B] 
27 Jun 2016 07:48:40,828 [APP   ] JTHERIOT - [JAS]             JDBCProxy.jdbcProxy(): Operation is executed under an auto commit JDBj logical connection for Usersession ID: 5260097675929690112 | UserName : JTHERIOT, Environment : DV910, User Role : *ALL 
27 Jun 2016 07:48:40,828 [APP   ] JTHERIOT - [JDBJ]            DELETE FROM TESTDTA.F596116P WHERE ((PRPHRT = ?  AND PRUPMJ = ?  AND PRAN8 = ?  AND PRPID = ?  AND PRUPMT = ?  AND PRPHRW = ?  AND PRFLSA = ?  AND PRDWK = ?  AND PRSSFL = ?  AND PRSSREID = ?  AND PRPDBA = ?  AND PRUSER = ? )) 
27 Jun 2016 07:48:40,828 [APP   ] JTHERIOT - [JDBJ]             SQL statement parameter marker values are :  
27 Jun 2016 07:48:40,828 [APP   ] JTHERIOT - [JDBJ]             Param1 : 32090_Types.NUMERIC, 
27 Jun 2016 07:48:40,828 [APP   ] JTHERIOT - [JDBJ]             Param2 : 116179_Types.INTEGER, 
27 Jun 2016 07:48:40,828 [APP   ] JTHERIOT - [JDBJ]             Param3 : 712_Types.NUMERIC, 
27 Jun 2016 07:48:40,828 [APP   ] JTHERIOT - [JDBJ]             Param4 : P5951127  _Types.VARCHAR, 
27 Jun 2016 07:48:40,828 [APP   ] JTHERIOT - [JDBJ]             Param5 : 74643_Types.NUMERIC, 
27 Jun 2016 07:48:40,828 [APP   ] JTHERIOT - [JDBJ]             Param6 : 800_Types.NUMERIC, 
27 Jun 2016 07:48:40,828 [APP   ] JTHERIOT - [JDBJ]             Param7 : Y_Types.VARCHAR, 
27 Jun 2016 07:48:40,828 [APP   ] JTHERIOT - [JDBJ]             Param8 : 116183_Types.INTEGER, 
27 Jun 2016 07:48:40,828 [APP   ] JTHERIOT - [JDBJ]             Param9 : 5_Types.VARCHAR, 
27 Jun 2016 07:48:40,828 [APP   ] JTHERIOT - [JDBJ]             Param10 : 489385_Types.VARCHAR, 
27 Jun 2016 07:48:40,828 [APP   ] JTHERIOT - [JDBJ]             Param11 : 200_Types.NUMERIC, 
27 Jun 2016 07:48:40,828 [APP   ] JTHERIOT - [JDBJ]             Param12 : JTHERIOT  _Types.VARCHAR, 
27 Jun 2016 07:48:40,828 [APP   ] JTHERIOT - [JDBJ]            [B]Deleted 1 records from table F596116P [/B]
27 Jun 2016 07:48:40,828 [APP   ] JTHERIOT - [JAS]             JDBCProxy.jdbcProxy()>>>response is good(execute()) -> , status = 0, nextpageid = -1, data = null 
27 Jun 2016 07:48:40,828 [APP   ] JTHERIOT - [RUNTIME]         F596116P.DELETETABLE | TABLE | false | 1 | null | null
  IN: AN8 | VA[824] | DWK | VA[825] | FLSA | VA[829] | PDBA | VA[828] | PHRT | VA[827] | PHRW | VA[826] | PID | VA[830] | SSFL | VA[834] | SSREID | VA[822] | UPMJ | VA[832] | UPMT | VA[833] | USER | VA[831]
  FILTER: AN8 | = | DWK | = | FLSA | = | PDBA | = | PHRT | = | PHRW | = | PID | = | SSFL | = | SSREID | = | UPMJ | = | UPMT | = | USER | =

I think I had tried the Delete by RecordID only, I didn't take a look at the logs, but it'll probably produce something similar as to what I'm seeing here. Any ideas on the RUNTIME parameters?
 
Thanks for the response John.

I dug a little deeper yesterday, and in looking through the logs it looks like it's doing something, or trying to.

Code: 
27 Jun 2016 08:58:22,421 [APP   ] JTHERIOT - [RUNTIME]         Number of execution : 9 
27 Jun 2016 08:58:22,421 [APP   ] JTHERIOT - [JAS]             JDBCProxy.jdbcProxy(): Operation is executed under an auto commit JDBj logical connection for Usersession ID: 3372436419701531648 | UserName : JTHERIOT, Environment : DV910, User Role : *ALL 
27 Jun 2016 08:58:22,421 [APP   ] JTHERIOT - [JDBJ]            DELETE FROM TESTDTA.F596116P WHERE ((PRPHRT = ?  AND PRUPMJ = ?  AND PRAN8 = ?  AND PRPID = ?  AND PRUPMT = ?  AND PRPHRW = ?  AND PRFLSA = ?  AND PRDWK = ?  AND PRSSFL = ?  AND PRSSREID = ?  AND PRPDBA = ?  AND PRUSER = ? )) 
27 Jun 2016 08:58:22,421 [APP   ] JTHERIOT - [JDBJ]             SQL statement parameter marker values are :  
27 Jun 2016 08:58:22,421 [APP   ] JTHERIOT - [JDBJ]             Param1 : 32090_Types.NUMERIC, 
27 Jun 2016 08:58:22,421 [APP   ] JTHERIOT - [JDBJ]             Param2 : 116179_Types.INTEGER, 
27 Jun 2016 08:58:22,421 [APP   ] JTHERIOT - [JDBJ]             Param3 : 712_Types.NUMERIC, 
27 Jun 2016 08:58:22,421 [APP   ] JTHERIOT - [JDBJ]             Param4 : P5951127  _Types.VARCHAR, 
27 Jun 2016 08:58:22,421 [APP   ] JTHERIOT - [JDBJ]             Param5 : 85545_Types.NUMERIC, 
27 Jun 2016 08:58:22,421 [APP   ] JTHERIOT - [JDBJ]             Param6 : 800_Types.NUMERIC, 
27 Jun 2016 08:58:22,421 [APP   ] JTHERIOT - [JDBJ]             Param7 : Y_Types.VARCHAR, 
27 Jun 2016 08:58:22,421 [APP   ] JTHERIOT - [JDBJ]             Param8 : 116183_Types.INTEGER, 
27 Jun 2016 08:58:22,421 [APP   ] JTHERIOT - [JDBJ]             Param9 : 5_Types.VARCHAR, 
27 Jun 2016 08:58:22,421 [APP   ] JTHERIOT - [JDBJ]             Param10 : 489415_Types.VARCHAR, 
27 Jun 2016 08:58:22,421 [APP   ] JTHERIOT - [JDBJ]             Param11 : 200_Types.NUMERIC, 
27 Jun 2016 08:58:22,421 [APP   ] JTHERIOT - [JDBJ]             Param12 : JTHERIOT  _Types.VARCHAR, 
27 Jun 2016 08:58:22,421 [APP   ] JTHERIOT - [JDBJ]            Deleted 1 records from table F596116P 
27 Jun 2016 08:58:22,421 [APP   ] JTHERIOT - [JAS]             JDBCProxy.jdbcProxy()>>>response is good(execute()) -> , status = 0, nextpageid = -1, data = null 
27 Jun 2016 08:58:22,421 [APP   ] JTHERIOT - [RUNTIME]         F596116P.DELETETABLE | TABLE | false | 1 | null | null
  IN: AN8 | VA[824] | DWK | VA[825] | FLSA | VA[829] | PDBA | VA[828] | PHRT | VA[827] | PHRW | VA[826] | PID | VA[830] | SSFL | VA[834] | SSREID | VA[822] | UPMJ | VA[832] | UPMT | VA[833] | USER | VA[831]
  FILTER: AN8 | = | DWK | = | FLSA | = | PDBA | = | PHRT | = | PHRW | = | PID | = | SSFL | = | SSREID | = | UPMJ | = | UPMT | = | USER | =

However, it's still not removing it from the database, I'm having my DBA take a look at it on his side just in case, but he hasn't gotten back to me yet.

Any ideas on what the Parameters are in the [RUNTIME] DELETETABLE line?
 
Jonathan,

I'll give you 1000 to 1 odds its your code, not the database.

As John said you must use Fetch Next in a Select - While loop - not Fetch Single.
You also MUST use the same Key/Index for all those I-Os - if not you're screwing up the loop.
If it was me I'd use a table handle for the Delete statement.
Of course I'd also try to do it all in one Delete statement too ... ;-)

Cheers
 
Larry_Jones said:
Jonathan,

I'll give you 1000 to 1 odds its your code, not the database.

As John said you must use Fetch Next in a Select - While loop - not Fetch Single.
You also MUST use the same Key/Index for all those I-Os - if not you're screwing up the loop.
If it was me I'd use a table handle for the Delete statement.
Of course I'd also try to do it all in one Delete statement too ... ;-)

Cheers
Click to expand...

So it's a copy mod of the F051127, which was a terrible idea in the first place. It was/is my first copy mod that had more than a few text boxes for the user to interact with.

That being said, at the end of the FC Delete Button, it was pressing the FC Save, which was pressing the HC &OK which had my insert/update code it as that's where the ESS Insert/Update function was. So it WAS deleting it, it was also just inserting it back in. Worth a good laugh after several hours of frustration.
 

Similar threads

JohnDanter2
E9.2 BSVW left outer join behaviour
Replies
7
Views
1K
JohnDanter2
JohnDanter2
Can I do date range in Select Table I/O?
Replies
4
Views
5K
Santiago3601
johndanter
SV File I/O status for records locking in NER BSFNs
Replies
4
Views
5K
nareen
B4201070 F42UI11RetrieveSODInfo Read P4210 Cache and loop details in another program
Replies
18
Views
8K
BOster
BOster
Error updating table on button press
Replies
3
Views
2K
shaz68
Back
Top