I'm using this code to delete records from a database. The problem is that it comes back and says that only one record was deleted when I know there are many records that match this criteria in my database. Here's the code:
DEFINE FILE CAR VLEVELTYPE/A12 WITH BODYTYPE= '01DETAIL'; END TABLE FILE CAR PRINT VLEVELTYPE WHERE READLIMIT EQ 1 ; ON TABLE HOLD AS ML_ALOSII FORMAT ALPHA END MODIFY FILE DMVALUES FIXFORM FROM ML_ALOSII MATCH VLEVELTYPE ON MATCH DELETE ON NOMATCH REJECT DATA ON ML_ALOSII END
Here is the SQL code generated by the above... I don't understand why it says DELETE FROM Damen.dbo.tblValues WHERE ("ValueID" = ?); when it is not ValueID that I am matching on, but VLEVELTYPE.
SELECT T1."ValueID",T1."CO",T1."VStart",T1."VEnd",T1."VFreq", T1."VIndicator",T1."VValue",T1."VTimeValue",T1."VLevel", T1."VLevelType",T1."VAsOf",T1."VProcess",T1."VComment", T1."VS01",T1."VS02",T1."VS03",T1."VS04",T1."VS05",T1."VT01", T1."VT02",T1."VT03",T1."VT04",T1."VT05" FROM Damen.dbo.tblValues T1 WHERE (T1."VLevelType" = ?) ORDER BY T1."ValueID" ASC; DELETE FROM Damen.dbo.tblValues WHERE ("ValueID" = ?); 0 TRANSACTIONS: TOTAL = 1 ACCEPTED= 1 REJECTED= 0 SEGMENTS: INPUT = 0 UPDATED = 0 DELETED = 1This message has been edited. Last edited by: Mark1,
Windows version 768
December 16, 2009, 12:37 PM
Stefaans
Hi Would you share the DMVALUES master file with us. Is this a multi-segment master file.Further, why is your readlimit set to 1 unless the key to the TOP segment never changes. regards SteveThis message has been edited. Last edited by: Stefaans,
WebFOCUS 7.7.01 Windows, TSO 7.0
December 16, 2009, 12:44 PM
Francis Mariani
The ? represents the multiple values you provide in the HOLD file.
The generated SQL first SELECTs all the rows from the DBMS table that match the VLEVELTYPEs in the HOLD file. Then it DELETEs rows based on the key field ValueID which are stored in a temporary internal file generated by the SELECT statement.
The SQL looks alright to me, though I can't explain the mismatch in the table being processed: DMVALUES vs tblValues.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
December 16, 2009, 01:08 PM
Mark1
Thanks Francis. That makes sense. The tblValues name is what the actual table name is. The DMVALUES is just the synonymn name we use to reference that table in WF. So, are you saying that my code looks correct?
Steve, I'm using readlimit eq 1 because I only need the one value of VLEVELTYPE to be my match. So, I think that I really only need 1 record. Otherwise ML_ALOSII would look like this:
01DETAIL 01DETAIL 01DETAIL 01DETAIL ...
Windows version 768
December 16, 2009, 01:32 PM
Mark1
I did a new fex and just entered this in:
SQL DELETE FROM DMVALUES WHERE (VLEVELTYPE = '01DETAIL'); END
That seemed to delete the records just fine.
Windows version 768
December 16, 2009, 03:08 PM
Tony A
quote:
WHERE READLIMIT EQ 1 ;
Mark,
If you limit your hold file to one record then only expect one record to be matched and deleted
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
December 16, 2009, 04:39 PM
Danny-SRL
quote:
MODIFY FILE DMVALUES FIXFORM FROM ML_ALOSII MATCH VLEVELTYPE ON MATCH DELETE ON NOMATCH REJECT DATA ON ML_ALOSII END
As Tony rightly says, 1 input for 1 match. So you have to make your program loop through the file and search for ALL the records that match that value. This could be a solution:
MODIFY FILE DMVALUES
ACTIVATE VLEVELTYPE
MATCH VLEVELTYPE
ON MATCH DELETE
ON MATCH GOTO TOP
ON NOMATCH GOTO EXIT
CASE AT START
FIXFORM FROM ML_ALOSII
ENDCASE
DATA ON ML_ALOSII
END
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
December 16, 2009, 04:51 PM
Francis Mariani
Tony might be right about 1 input for 1 match, but it's not wrong to expect many deletes for 1 matched value. Can any of you gentlemen point out what's wrong with the generated SQL (I can't):
SELECT T1."ValueID",T1."CO",T1."VStart",T1."VEnd",T1."VFreq",
T1."VIndicator",T1."VValue",T1."VTimeValue",T1."VLevel",
T1."VLevelType",T1."VAsOf",T1."VProcess",T1."VComment",
T1."VS01",T1."VS02",T1."VS03",T1."VS04",T1."VS05",T1."VT01",
T1."VT02",T1."VT03",T1."VT04",T1."VT05" FROM
Damen.dbo.tblValues T1 WHERE (T1."VLevelType" = ?) ORDER BY
T1."ValueID" ASC;
DELETE FROM Damen.dbo.tblValues WHERE ("ValueID" = ?);
Mark, your self-coded SQL is correct as well.
Francis
Give me code, or give me retirement. In FOCUS since 1991
Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
December 16, 2009, 05:19 PM
GinnyJakes
If you want to do set-based processing, you need to use MAINTAIN. This is from the MODIFY documentation: [QUOTE]Set-based Processing To fully appreciate set-based processing, it is helpful to remember how MODIFY deals with groups of records. MODIFY's basic approach is segment-at-a-time processing: you use MATCH or NEXT to retrieve an individual segment instance (for example, a row in a relational table), and then INCLUDE, UPDATE, or DELETE to process one or more segment instances within a single record. You go through the entire data source one segment at a time, one record at a time.
MODIFY uses the Scratch Pad Area (SPA) to enable you to perform these individual operations multiple times by repeating them in a loop. You stack retrieved values in the SPA, and then loop through the stack to process them.
For example, the following portion of a MODIFY request retrieves the PAY_DATE, DED_CODE, and DED_AMT fields from the EMPLOYEE data source, writes them to the SPA, increments counters used later to scroll the SPA in a CRTFORM, loops through the first case once for each instance of PAY_DATE, and loops through the second case once for each instance of DED_CODE: /QUOTE] I didn't include the code example, but you can do an IBI search on set-based processing to get the whole entry.
MODIFY only deletes one row at a time. If you were doing SQL Passthru which your code snippets don't suggest you are doing, you should be able to delete more than one row at a time because the data base engine will be doing it, not WebFOCUS.
Interesting that, based on the generated SQL, WebFOCUS appears to have determined that VLEVELTYPE was not the key to the table and created SQL code to select the appropriate record(s) and use the real key (ValueID) to process the delete(s). I agree with Francis that the SQL code would appear to be correct for deleting multiple records. But we do not know exactly what SQL code was actually passed to the database. The SQL traces only provide a close approximation. The ? marks in the SQL may indicate that WebFOCUS first retrieved a set of data based on the VLEVELTYPE filter and then passed only the first ValueID from the set for the delete step.
WebFOCUS 7.7.05
December 17, 2009, 01:18 PM
Mark1
Thank you all for your help. The database that we input data to is shared by many users and as a general rule, nothing should ever be deleted (just overwritten). This incident was a special exception to that rule. So, the self-coded SQL will work for my purposes.
However, it would seem that the trace only gave a close approximation and not the actual code that was passed. Otherwise, I would think that code would delete any records where valueID = ?