[SOLVED]Delete rows from a SQL Server table using WebFocus
I am writing information to a sql table nightly from a .fex but want to remove all records in the table before loading them. Has anyone done this before?
Using the following two step procedure removes the rows from the table if it finds a match but it doesn't remove rows if there is no match.
MODIFY FILE ACVADVR
FIXFORM FROM ADVRHOLD
MATCH Pidm
ON NOMATCH INCLUDE
ON MATCH DELETE
DATA ON ADVRHOLD
END
MODIFY FILE ACVADVR
FIXFORM FROM ADVRHOLD
MATCH Pidm
ON NOMATCH INCLUDE
ON MATCH REJECT
DATA ON ADVRHOLD
END
This message has been edited. Last edited by: <Emily McAllister>,
WF8 Windows
April 18, 2016, 03:15 PM
BabakNYC
If you're trying to remove all records from the table why not use a SQL command to truncate or delete from the table instead of MODIFY? Also, your code is saying ON NOMATCH INCLUDE which means if there's a Pidm in ADVRHOLD that doesn't exist in ACVADVR it'll get inserted into the table.
What's the source of the data?
WebFOCUS 8206, Unix, Windows
April 18, 2016, 03:21 PM
Tom Flynn
Trudy, May want to bookmark this site, very helpful... Tom
SET HOLDLIST=PRINTONLY
TABLE FILE ACVADVR
PRINT PIDM NOPRINT
BY PIDM
ON TABLE HOLD AS ADVRHOLD
END
You would be pulling all of the records assuming that pidm is your key. I'm guessing that the table is probably keyed on PIDM, ADVISOR_PIDM, ADVISOR_TYPE, and Some Sequence number knowing banner.... So you probably want to match on those keys instead.
Eric Woerle 8.1.05M Gen 913- Reporting Server Unix 8.1.05 Client Unix Oracle 11.2.0.2
April 18, 2016, 04:01 PM
BabakNYC
ENGINE SQLMSS SET DEFAULT_CONNECTION YOUR_CONNECTION_NAME (you can find this in ACVADVR.ACX)
SQL SQLMSS DELETE xxx.ACVADVR; (fully qualified tablename also in the same .ACX file)
COMMIT;
END
WebFOCUS 8206, Unix, Windows
April 18, 2016, 05:07 PM
Dan Satchell
Unless you need a log of records deleted, I would use the SQL TRUNCATE command instead of DELETE because it is faster and does a more thorough job of cleaning up the table space.
WebFOCUS 7.7.05
April 19, 2016, 09:11 AM
Maintain Wizard
The easiest way would be to do this with Maintain!
MAINTAIN FILE ACVADVR FOR ALL NEXT Pidm INTO STK FOR ALL DELETE Pidm FROM STK END
This will remove all of the records from the database without dropping the table.