I have a relatively large database I need to rebuild from scratch. I can't use the dbunload utility because the database is corrupt, so I have to go table by table.
I also thought that since I have to do this, that I should take this oportunity to trim the database and copy only relevant data. So I thought about using the UNLOAD statement to export a subset of the data.
For example, the table "iTrans":
UNLOAD SELECT * FROM iTrans WHERE TransDate >= '2014-01-01' TO '\\transfer\itrans.dat";
This works perfect. The problem is with "iTrans" child table "iTransRow". The primary key is a two column key (IdLoc, IdSeq).
HOW do I filter the rows in "iTransRow" to only contain those that are in the "iTrans" subset? If the the primary key was a single column it would be easy using the IN clause, but with a two column key?
I came up with this, but I think I'm pretty sure there must be a better way...
UNLOAD SELECT * FROM iTransRow
EXISTS (SELECT * FROM iTrans AS A WHERE A.Fecha >= '2014-01-01' AND iTransRow.IdLoc=A.IdLoc AND iTransRow.IdSeq=A.IdSeq)
These are fairly large tables, and I need to rebuild this asap 😔
Thanks for any ideas,