Thunderstone Support Forums

I am attempting to run the following query in order to locate those records that have duplicates in a specific column:

SELECT PATN_WKU, COUNT(*) N
FROM PATN
WHERE PATN_ISD > '20031231' AND PATN_ISD < '20050101' GROUP BY PATN_WKU HAVING N > 1;

I am getting the following errors (there are many more, but since they are all so similra, I'm listing only a few)

005 Corrupt block header at 0x401B8004 in KDBF file /usr/tmp/T04702a in the function read_head
005 Corrupt block header at 0x401DB6FA in KDBF file /usr/tmp/T04702a in the function read_head
005 Corrupt block header at 0x401E7D14 in KDBF file /usr/tmp/T04702a in the function read_head
005 Corrupt block header at 0x401F1A42 in KDBF file /usr/tmp/T04702a in the function read_head

etc.

This is very similar to errors that people reported in past posts, and that those bugs were fixed an upgrade. Here is the output to texis -version on our server:

Texis Web Script (Vortex) Copyright (c) 1996-2004 Thunderstone - EPI, Inc.
Commercial Version 5.00.1090358162 20040720 (i686-unknown-linux2.4.2-64-32)

Thanks for your help.

If you repeat the query several times by removing one trailing clause at a time (eg. run with `HAVING N > 1' removed, then run with `GROUP BY PATN_WKU' removed too, etc.) is there a query that does not produce the KDBF errors? That will help isolate the stage where it occurs for us.

I removed the HAVING clause and re-ran the query. Same errors. Then I removed the GROUP BY clause and the query ran *without* error. Of course, the resultset from that query contained a value for PATN_WKU (last? first? arbitrary?) and the total row count meeting the conditions specified by the WHERE clause. This is useless for me, and arguably meaningless. Anyway, I hope this helps pinpoint what might be causing my problem.

Thanks again.

Any further thought on this one?

Thanks.

The temp file created seems rather large. Is there at least several GB of space free on /usr/tmp? If not, try re-running the original query with TMP set to a temp dir with more free space.

What type is PATN_WKU, and how many records are in the date range?

You might create a vortex script to break it into steps:

<sql novars "create table PATN_WKU as
SELECT PATN_WKU FROM PATN WHERE PATN_ISD > '20031231' AND PATN_ISD < '20050101'"></sql>
<sql "create index PATN_WKU_x on PATN_WKU(PATN_WKU)"></sql>
<$LastRead=><$LastCount=0>
<SQL ROW "select PATN_WKU from PATN_WKU ORDER BY PATN_WKU">
<if $PATN_WKU neq $LastRead>
<if $LastCount gt 1>
$LastRead
<$LastRead=$PATN_WKU>
<$LastCount = 1>
<else>
<$LastCount=(1 + $LastCount)>
</if>
</SQL>