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 = 1

This message has been edited. Last edited by: Mark1,

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 Steve

This message has been edited. Last edited by: Stefaans,

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.

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
...

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.

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

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

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.

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.

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 = ?