Here are the create table and create index statements that will reproduce the problem: create table cc_check (Status integer NOT NULL, CheckInstructions integer NULL, UpdateTime datetime NOT NULL, MailToAddress varchar(255) NULL, PendEscalationForBulk bit NULL, InvoiceNumber varchar(255) NULL, CreateTime datetime NOT NULL, DeliveryMethod integer NULL, CheckBatching integer NULL, BeanVersion integer NULL, CheckSetID integer NOT NULL, GroupID integer NULL, Retired integer NOT NULL, LoadCommandID integer NULL, extractready integer NULL, IssueDate datetime NULL, CheckNumber varchar(255) NULL, CreateUserID integer NULL, ScheduledSendDate datetime NULL, EnteredTime datetime NULL, DateOfService datetime NULL, PayToDenorm varchar(255) NULL, ClaimContactID integer NULL, PublicID varchar(20) NOT NULL, ServicePdEnd datetime NULL, ClaimID integer NOT NULL, BankAccount integer NULL, PaymentMethod integer NULL, Memo varchar(255) NULL, PortionID integer NULL, DeductionType integer NULL, BulkInvoiceItemInfoID integer NULL, CheckType integer NOT NULL, PayTo varchar(255) NULL, MailTo varchar(255) NULL, Comments varchar(255) NULL, ReportableAmount decimal(18,2) NULL, RecurringCheckID integer NULL, UpdateUserID integer NULL, Reportability integer NULL, ServicePdStart datetime NULL, ID integer NOT NULL); ALTER TABLE cc_check ADD PRIMARY KEY (ID); CREATE INDEX cc000000h9N0 ON cc_check (ScheduledSendDate); CREATE INDEX cc000000h9N1 ON cc_check (extractready); CREATE UNIQUE INDEX cc000000h9U2 ON cc_check (Status, Retired, ID, ScheduledSendDate); CREATE UNIQUE INDEX cc000000h9U3 ON cc_check (Status, Retired, EnteredTime, ID); CREATE UNIQUE INDEX cc000000h9U4 ON cc_check (CheckNumber, Retired, ClaimID, ID); CREATE UNIQUE INDEX cc000000h9U5 ON cc_check (InvoiceNumber, Retired, ClaimID, ID); CREATE UNIQUE INDEX cc000000h9U6 ON cc_check (CreateUserID, EnteredTime, Retired, ClaimID, PayToDenorm, Status, ID); CREATE UNIQUE INDEX cc000000h9U7 ON cc_check (GroupID, Retired, ID); CREATE UNIQUE INDEX cc000000h9U8 ON cc_check (ID, Retired, EnteredTime, ClaimID); CREATE UNIQUE INDEX cc000000h9U9 ON cc_check (PublicID, Retired); CREATE INDEX cc000000h9N10 ON cc_check (CheckSetID, Retired); CREATE INDEX cc000000h9N11 ON cc_check (ClaimContactID, Retired); CREATE INDEX cc000000h9N12 ON cc_check (ClaimID, Retired); CREATE INDEX cc000000h9N13 ON cc_check (PortionID, Retired); CREATE INDEX cc000000h9N14 ON cc_check (BulkInvoiceItemInfoID, Retired); CREATE INDEX cc000000h9N15 ON cc_check (RecurringCheckID, Retired); CREATE UNIQUE INDEX cc000000h9U16 ON cc_check (ID, extractready);
create table cc_transaction (Status integer NOT NULL, PaymentType integer NULL, CheckID integer NULL, UpdateTime datetime NOT NULL, CreateTime datetime NOT NULL, Matter integer NULL, BeanVersion integer NULL, TransToClaimExchangeRate integer NULL, Currency integer NOT NULL, LoadCommandID integer NULL, Retired integer NOT NULL, extractready integer NULL, CostCategory integer NOT NULL, DoesNotErodeReserves bit NULL, TransactionSetID integer NOT NULL, CreateUserID integer NULL, ClaimContactID integer NULL, CostType integer NOT NULL, ClaimID integer NOT NULL, PublicID varchar(20) NOT NULL, ClaimToReportingExchangeRate integer NULL, ExposureID integer NULL, RecoveryCategory integer NULL, LifeCycleState integer NOT NULL, Subtype integer NOT NULL, ReserveLineID integer NOT NULL, SubmitDate datetime NULL, PayerDenormID integer NULL, CloseClaim bit NULL, Comments varchar(255) NULL, CloseExposure bit NULL, UpdateUserID integer NULL, ID integer NOT NULL); ALTER TABLE cc_transaction ADD PRIMARY KEY (ID); CREATE INDEX cc000000hvN0 ON cc_transaction (extractready); CREATE INDEX cc000000hvN1 ON cc_transaction (TransactionSetID, ClaimID); CREATE INDEX cc000000hvN2 ON cc_transaction (ExposureID, ClaimID); CREATE INDEX cc000000hvN3 ON cc_transaction (ExposureID, Subtype, Status); CREATE INDEX cc000000hvN4 ON cc_transaction (ClaimID, Retired, UpdateTime); CREATE UNIQUE INDEX cc000000hvU5 ON cc_transaction (CreateUserID, Retired, Subtype, ClaimID, ID); CREATE INDEX cc000000hvN6 ON cc_transaction (Subtype, CreateUserID, Retired, CreateTime, ClaimID); CREATE UNIQUE INDEX cc000000hvU7 ON cc_transaction (LifeCycleState, Subtype, Retired, ID); CREATE UNIQUE INDEX cc000000hvU8 ON cc_transaction (PublicID, Retired); CREATE UNIQUE INDEX cc000000hvU9 ON cc_transaction (PayerDenormID, Retired, Subtype, CreateTime, ClaimID, ID); CREATE INDEX cc000000hvN10 ON cc_transaction (CheckID, Retired); CREATE INDEX cc000000hvN11 ON cc_transaction (Matter, Retired); CREATE INDEX cc000000hvN12 ON cc_transaction (TransToClaimExchangeRate, Retired); CREATE INDEX cc000000hvN13 ON cc_transaction (TransactionSetID, Retired); CREATE INDEX cc000000hvN14 ON cc_transaction (ClaimContactID, Retired); CREATE INDEX cc000000hvN15 ON cc_transaction (ClaimToReportingExchangeRate, Retired); CREATE INDEX cc000000hvN16 ON cc_transaction (ExposureID, Retired); CREATE INDEX cc000000hvN17 ON cc_transaction (ReserveLineID, Retired); CREATE UNIQUE INDEX cc000000hvU18 ON cc_transaction (ID, extractready); create table cc_transactionoffsetonset (extractready integer NULL, OnsetID integer NULL, OffsetID integer NOT NULL, PublicID varchar(20) NOT NULL, BeanVersion integer NULL, TransactionID integer NOT NULL, ID integer NOT NULL, LoadCommandID integer NULL); ALTER TABLE cc_transactionoffsetonset ADD PRIMARY KEY (ID); CREATE UNIQUE INDEX cc000000j0U0 ON cc_transactionoffsetonset (OffsetID); CREATE UNIQUE INDEX cc000000j0U1 ON cc_transactionoffsetonset (PublicID); CREATE INDEX cc000000j0N2 ON cc_transactionoffsetonset (OnsetID); CREATE INDEX cc000000j0N3 ON cc_transactionoffsetonset (extractready); CREATE UNIQUE INDEX cc000000j0U4 ON cc_transactionoffsetonset (TransactionID); CREATE UNIQUE INDEX cc000000j0U5 ON cc_transactionoffsetonset (TransactionID, OffsetID, OnsetID); CREATE UNIQUE INDEX cc000000j0U6 ON cc_transactionoffsetonset (OffsetID, OnsetID); CREATE UNIQUE INDEX cc000000j0U7 ON cc_transactionoffsetonset (ID, extractready); On Jan 28, 11:02 pm, Thomas Mueller <[email protected]> wrote: > Hi, > > Could you post your CREATE TABLE and CREATE INDEX statements as well please? > > Regards, > Thomas > > On Tue, Jan 27, 2009 at 7:40 AM, Wes Clark <[email protected]> wrote: > > > Could this be another manifestation of the problem with nested outer > > joins reported in > >http://groups.google.com/group/h2-database/browse_thread/thread/af436... > > --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "H2 Database" group. To post to this group, send email to [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/h2-database?hl=en -~----------~----~----~----~------~----~------~--~---
