SQL: Delete empty SIFT records

Hi all, again! Another SQL issue: the standard table optimization is performing a CREATE INDEX … and it deletes empty SIFT records. How could I do this deletion manually by using a TransactSQL script? Any templates available? Regards,

The basic idea is to delete any record where all the ‘s-fields’ are zero… (For a description of the ‘s-fields’, see ‘Naming Conventions’ in section 24.2 of the Application Designer’s Guide.) For example, in an ODBC log taken when optimizing the Item Ledger Entry table (v3.6), I found the following entries related to optimizing the second key:

CREATE UNIQUE NONCLUSTERED INDEX "$1" ON "<db>"."dbo"."<co>$Item Ledger Entry" ("Item No_","Variant Code","Drop Shipment","Location Code", "Bin Code","Posting Date","Entry No_") WITH DROP_EXISTING CREATE UNIQUE CLUSTERED INDEX "<co>$32$0_idx" ON "<db>"."dbo"."<co>$32$0" ("bucket","f2","f5402","f47","f8","f5403","f3") WITH DROP_EXISTING CREATE INDEX "<co>$32$0_hlp_idx" ON "<db>"."dbo"."<co>$32$0" ("f3") WITH DROP_EXISTING DELETE FROM "<db>"."dbo"."<co>$32$0" WHERE (s12=0 AND s14=0)

… and I just need the DELETE part of tat script. The problem is, that every SIFT table has a different amount of s fields, so the trick is, to find out dynamically the table structure to set the correct WHERE statement. Well, finally I solved this (one hour ago). I took some code from the “SQL Resource Kit”, modified it and integrated it into our “SQL Script Generator”. Now I just need to select the tables and the script is created properly [8D]

Don’t get me wrong but, first you ask someone to help you, and now you don’t want to share…, I want to learn

When I’m asking for help, this doesn’t mean I stop my own researches …[:P] But here “my way”: - I copied C50071 “SQL Information” - In this new codeunit, I changed the code in the function BuildSIFTRecStatement BuildSIFTRecStatement(Keys : Record Keys;CompanyNameStr : Text[100];SIFTIndexNo : Integer;DataPerCompany : Boolean) //DEBUG := TRUE; SIFTLevels.SETRANGE(SIFTLevels."Table No.",Keys."Table No."); SIFTLevels.SETRANGE(SIFTLevels."Key No.",Keys."Key No."); SIFTLevels.SETRANGE(SIFTLevels.Enabled,TRUE); IF SIFTLevels.FIND('-') THEN BEGIN REPEAT IF DataPerCompany THEN SIFTCompanyHeader := CompanyNameStr + '$' ELSE CLEAR(SIFTCompanyHeader); GetSIFTStatus.UPDATE(1,SIFTCompanyHeader + FORMAT(Keys."Table No.") + '$' + FORMAT(SIFTIndexNo)); // Delete empty SIFT records SQLStatement[4] := 'DELETE FROM dbo.[' + SIFTCompanyHeader + FORMAT(Keys."Table No.") + '$' + FORMAT(SIFTIndexNo) + ']'; SQLStatement[4] := AppendCRLF(SQLStatement[4]); SQLStatement[4] += 'WHERE ' + BuildSumIndexFields(Keys); SQLStatement[4] += ' AND bucket = ' + FORMAT(SIFTLevels."Bucket No."); SQLStatement[4] := AppendCRLF(AppendCRLF(SQLStatement[4])); // Get record count per SIFT Index by SIFT Level SQLStatement[1] := 'Declare @CountValue integer'; SQLStatement[1] := AppendCRLF(SQLStatement[1]); SQLStatement[1] += 'SELECT @CountValue = count(*) from dbo.[' + SIFTCompanyHeader + FORMAT(Keys."Table No.") + '$' + FORMAT(SIFTIndexNo) + ']' + ' where bucket = ' + FORMAT(SIFTLevels."Bucket No."); SQLStatement[1] := AppendCRLF(AppendCRLF(SQLStatement[1])); // Get blank record count per SIFT Index by SIFT Level SQLStatement[2] := 'Declare @ZeroValue integer'; SQLStatement[2] := AppendCRLF(SQLStatement[2]); SQLStatement[2] += 'SELECT @ZeroValue = count(*) from dbo.[' + SIFTCompanyHeader + FORMAT(Keys."Table No.") + '$' + FORMAT(SIFTIndexNo) + ']'; SQLStatement[2] := AppendCRLF(SQLStatement[2]); SQLStatement[2] += 'Where ' + BuildSumIndexFields(Keys); SQLStatement[2] += ' AND bucket = ' + FORMAT(SIFTLevels."Bucket No."); SQLStatement[2] := AppendCRLF(AppendCRLF(SQLStatement[2])); // Insert Retrieved values into the SIFT Records table SQLStatement[3] := 'INSERT INTO dbo.[' + CompanyNameStr + '$SIFT Records] ([timestamp]'; // Add converted field names Fields.SETRANGE(Fields.TableNo,DATABASE::"SIFT Records"); IF Fields.FIND('-') THEN REPEAT SQLStatement[3] += ',[' + CONVERTSTR(Fields.FieldName,'.','_') + ']' UNTIL (Fields.NEXT = 0); SQLStatement[3] += ')'; SQLStatement[3] := AppendCRLF(SQLStatement[3]); SQLStatement[3] += 'VALUES (NULL,' + FORMAT(Keys."Table No.") + ',' + FORMAT(Keys."Key No.") + ',' + FORMAT(SIFTIndexNo) + ',' + '''' + SIFTCompanyHeader + '''' + ',' + FORMAT(SIFTLevels."Bucket No.") + ',' + '@CountValue,@ZeroValue)'; IF DEBUG THEN BEGIN FilePtr.TEXTMODE(TRUE); FilePtr.WRITEMODE(TRUE); FilePtr.CREATE('C:\' + SIFTCompanyHeader + FORMAT(Keys."Table No.") + '$' + FORMAT(SIFTIndexNo) + '-' + FORMAT(SIFTLevels."Bucket No.") + '.sql'); FilePtr.WRITE(SQLStatement[4] + SQLStatement[1] + SQLStatement[2] + SQLStatement[3]); FilePtr.CLOSE; END; SendSQLStatement(SQLStatement[4] + SQLStatement[1] + SQLStatement[2] + SQLStatement[3]); UNTIL (SIFTLevels.NEXT = 0); END; Here the important thing is SQLStatement[4]. The rest is, just to put a new button on the form to run this … When I’m talking about my “SQL Script Generator”, this is just a small tool to select tables, and then a report is creating a script-file - in this case using similar code of that codeunit. As always: The simple solutions are the best [Yeah!] Regards,