This is my view

CREATE VIEW LASTEST_INVENTORY_PRICE(
ID,
"Name",
"InvNo",
"InvDate",
"Price",
"PurchaseUnit",
"Conversion",
B1,
"InventoryUnit")
AS
select m.ID, m."Name", m3."InvNo", m3."InvDate", m2."Price", m."Unit1",
m."Conversion", m.b1, m."Unit2"
from "items" m
left join "invoicedetails" m2 on m.ID = m2."ItemID"
join "invoice" m3 on m2.ID = m3.ID
and not exists(select * from "invoice" id3
join "invoicedetails" id2 on id2.ID = id3.ID
where m."ID" = id2."ItemID"
and id3."InvDate" > m3."InvDate")
where m3."Status" != 'CAN' and m."Type" in ('RAW', 'PLS', 'LBL');

The plan analyzer said ....
PLAN JOIN (ID3 NATURAL,ID2 INDEX (invoicedetails_IDX1))
PLAN JOIN (JOIN (M NATURAL,M2 NATURAL),M3 INDEX (RDB$PRIMARY13))

Here are the ddl for the three tables involve

CREATE TABLE "items" (
ID INTEGER NOT NULL,
"General" INTEGER,
"Name" VARCHAR(60),
"Unit1" VARCHAR(10),
"Unit2" VARCHAR(10),
"Unit3" VARCHAR(10),
"Conversion" NUMERIC(15,6),
"Conversion2" NUMERIC(15,6),
B1 SMALLINT,
B2 SMALLINT,
"IngredientID" INTEGER,
"Base" NUMERIC(15,2),
"Type" CHAR(3),
"Left" NUMERIC(15,2),
"Produce" NUMERIC(15,2),
"Use" NUMERIC(15,2),
"Adjustment" NUMERIC(15,2),
"Minimum" NUMERIC(15,2),
"AveragePurchase" NUMERIC(15,2),
"LastPurchase" NUMERIC(15,2),
"Status" CHAR(3),
"CreateBy" VARCHAR(15),
"CreateOn" TIMESTAMP,
"EditBy" VARCHAR(15),
"EditOn" TIMESTAMP
);





/******************************************************************************/
/**** Primary
Keys ****/
/******************************************************************************/

ALTER TABLE "items" ADD CONSTRAINT "PK_items" PRIMARY KEY (ID);


/******************************************************************************/
/****
Indices ****/
/******************************************************************************/

CREATE INDEX "items_IDX1" ON "items" ("Name");


CREATE TABLE "invoice" (
ID INTEGER NOT NULL,
"InvNo" VARCHAR(15),
"SupplierID" INTEGER,
"Name" VARCHAR(40),
"InvDate" DATE,
"TermsType" VARCHAR(10),
"Terms" VARCHAR(10),
"TotalAmount" NUMERIC(15,2),
"Less" NUMERIC(15,2),
"Adjustment" NUMERIC(15,2),
"Discount" NUMERIC(4,2),
"Discount2" NUMERIC(4,2),
"Discount3" NUMERIC(4,2),
"Type" CHAR(3),
"ExpenseCategory" INTEGER,
"Status" CHAR(3),
"PaidDate" DATE,
"Remarks" VARCHAR(300),
"CreateBy" VARCHAR(15),
"CreateOn" TIMESTAMP,
"EditBy" VARCHAR(15),
"EditOn" TIMESTAMP
);





/******************************************************************************/
/**** Primary
Keys ****/
/******************************************************************************/

ALTER TABLE "invoice" ADD CONSTRAINT "PK_invoice" PRIMARY KEY (ID);

CREATE TABLE "invoicedetails" (
"Key" INTEGER,
ID INTEGER NOT NULL,
"Qty" NUMERIC(15,2),
"Unit" VARCHAR(10),
"ItemID" INTEGER,
"Name" VARCHAR(30),
"Unit2" VARCHAR(10),
"Conversion" NUMERIC(15,6),
"Price" NUMERIC(15,2),
"Less" NUMERIC(15,2),
"Discount" NUMERIC(4,2),
"Discount2" NUMERIC(4,2),
"Discount3" NUMERIC(4,2),
"Adjustment" NUMERIC(15,2),
"PoID" INTEGER,
"LackingID" INTEGER,
"CreateBy" VARCHAR(15),
"CreateOn" TIMESTAMP,
"EditBy" VARCHAR(15),
"EditOn" TIMESTAMP
);





/******************************************************************************/
/****
Indices ****/
/******************************************************************************/

CREATE INDEX "invoicedetails_IDX1" ON "invoicedetails" (ID);

I hope there is some place of improvement for this view. I find it very
slow. The items table has around 1000 records, the invoice has 2700
records, and the invoicedetails has 3400 records.

Thank you for your time and effort.

regards,
James