Errors when running a tsql query

[barry.marcus]

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.

[Kai]

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.

[barry.marcus]

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.

[barry.marcus]

Any further thought on this one?

Thanks.

[Kai]

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.

[John]

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>