How to delete all the samples for a specific Spotfire Statistica data entry setup
Article ID: KB0138261
Updated On:
| Products | Versions |
|---|---|
| Spotfire Statistica | 14.X and higher |
| Spotfire Statistica - All Servers | 14.x and higher |
Description
If the situation arises where all the data entry samples for a specific data entry setup need to be deleted, follow the steps below.
Resolution
If only a few samples need to be deleted, follow the steps in this knowledge base article: https://support.tibco.com/support-home/kbsearch/article?articleNumber=KB0077430
Note: Using the steps in the above knowledge base article allows for un-deletion.
To remove all the samples for a data entry setup, use the solutions below (sample deletion is permanent).
IMPORTANT: Users are strongly advised to back up the meta-database repository before modifying or deleting data entry setup samples.
Legal Notice: The information and queries contained in this article are provided "as is." Cloud Software Group and Spotfire Support are not responsible for any data loss, corruption, inaccuracies, or any other mishaps that may occur during the implementation of these steps.
A specific data entry setup is identified by the ID associated with it in Enterprise Manager. As an example, the TestOfDeletingSamples data entry setup id is 13, as shown below. The ID is necessary to run the following queries.
ORACLE
DECLARE
-- In Oracle, we don't use @ and we use := for assignment
v_TargetPROFID NUMBER := <Data Entry Setup id as found in Enterprise Manager>;
BEGIN
-- 1. Delete values
DELETE FROM CSVDAT WHERE SAMPID IN (SELECT SAMPID FROM CSSAMP WHERE PROFID = v_TargetPROFID);
-- 2. Delete label values
DELETE FROM CSLDAT WHERE SAMPID IN (SELECT SAMPID FROM CSSAMP WHERE PROFID = v_TargetPROFID);
-- 3. Delete comments
DELETE FROM CSSPCMNT WHERE SAMPID IN (SELECT SAMPID FROM CSSAMP WHERE PROFID = v_TargetPROFID);
-- 4. Delete approval records
DELETE FROM CSSPAPPR WHERE SAMPID IN (SELECT SAMPID FROM CSSAMP WHERE PROFID = v_TargetPROFID);
-- 5. Delete session mappings
DELETE FROM CSDESDAT WHERE SAMPID IN (SELECT SAMPID FROM CSSAMP WHERE PROFID = v_TargetPROFID);
-- 6. Finally, delete the sample headers
DELETE FROM CSSAMP WHERE PROFID = v_TargetPROFID;
-- Optional: Uncomment COMMIT if you want to save changes immediately
-- COMMIT;
DBMS_OUTPUT.PUT_LINE('Deletions completed for PROFID: ' || v_TargetPROFID);
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
DBMS_OUTPUT.PUT_LINE('Error occurred. Transaction rolled back.');
RAISE;
END;
SQL Server T-SQL
BEGIN TRANSACTION;
BEGIN TRY
-- 1. Declare and set the variable
DECLARE @TargetPROFID INT = <Data Entry Setup id as found in Enterprise Manager>;
-- 2. Delete dependent values
DELETE FROM CSVDAT WHERE SAMPID IN (SELECT SAMPID FROM CSSAMP WHERE PROFID = @TargetPROFID);
DELETE FROM CSLDAT WHERE SAMPID IN (SELECT SAMPID FROM CSSAMP WHERE PROFID = @TargetPROFID);
DELETE FROM CSSPCMNT WHERE SAMPID IN (SELECT SAMPID FROM CSSAMP WHERE PROFID = @TargetPROFID);
DELETE FROM CSSPAPPR WHERE SAMPID IN (SELECT SAMPID FROM CSSAMP WHERE PROFID = @TargetPROFID);
DELETE FROM CSDESDAT WHERE SAMPID IN (SELECT SAMPID FROM CSSAMP WHERE PROFID = @TargetPROFID);
-- 3. Finally, delete the sample headers
DELETE FROM CSSAMP WHERE PROFID = @TargetPROFID;
-- Commit if everything succeeded
COMMIT TRANSACTION;
PRINT 'Deletions completed for PROFID: ' + CAST(@TargetPROFID AS VARCHAR(10));
END TRY
BEGIN CATCH
-- Rollback if any error occurs
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
PRINT 'Error occurred. Transaction rolled back.';
-- Rethrow the error for debugging
THROW;
END CATCH;
Alternative SQL Server T-SQL
BEGIN TRANSACTION;
BEGIN TRY
DECLARE @TargetPROFID INT = <Data Entry Setup id as found in Enterprise Manager>;
-- 1. Delete values using JOINs
DELETE v FROM CSVDAT v
INNER JOIN CSSAMP s ON v.SAMPID = s.SAMPID
WHERE s.PROFID = @TargetPROFID;
DELETE l FROM CSLDAT l
INNER JOIN CSSAMP s ON l.SAMPID = s.SAMPID
WHERE s.PROFID = @TargetPROFID;
DELETE c FROM CSSPCMNT c
INNER JOIN CSSAMP s ON c.SAMPID = s.SAMPID
WHERE s.PROFID = @TargetPROFID;
DELETE a FROM CSSPAPPR a
INNER JOIN CSSAMP s ON a.SAMPID = s.SAMPID
WHERE s.PROFID = @TargetPROFID;
DELETE d FROM CSDESDAT d
INNER JOIN CSSAMP s ON d.SAMPID = s.SAMPID
WHERE s.PROFID = @TargetPROFID;
-- 2. Final delete from the parent table
DELETE FROM CSSAMP WHERE PROFID = @TargetPROFID;
COMMIT TRANSACTION;
PRINT 'Deletions completed for PROFID: ' + CAST(@TargetPROFID AS VARCHAR(10));
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION;
-- Output detailed error info
PRINT 'Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10));
PRINT 'Error Message: ' + ERROR_MESSAGE();
THROW;
END CATCH;
Issue/Introduction
How to delete all the samples for a specific Spotfire Statistica data entry setup.
Additional Information
