The query for 1.8:
Code: Select all
SELECT "OrderNum"
FROM "ViewOfOrderStatusHist"
INNER JOIN "Orders" ON "Orders"."OrderNumber" = "ViewOfOrderStatusHist"."OrderNum"
INNER JOIN "FilterOrderHistory" ON "FilterOrderHistory"."StartDate" <= "Orders"."Date"
AND CAST( YEAR( "FilterOrderHistory"."EndDate" ) || '-' || MONTH( "FilterOrderHistory"."EndDate" ) || '-' || ( DAY( "FilterOrderHistory"."EndDate" ) + 0.9 ) AS "DATE" ) >= "Orders"."Date"
WHERE "ViewOfOrderStatusHist"."Status" = 'Open' AND "FilterOrderHistory"."ID" = 0
Code: Select all
SELECT "OrderNum"
FROM "ViewOfOrderStatusHist"
INNER JOIN "Orders" ON "Orders"."OrderNumber" = "ViewOfOrderStatusHist"."OrderNum"
INNER JOIN "FilterOrderHistory" ON "FilterOrderHistory"."StartDate" <= "Orders"."Date"
AND DATEADD( 'dd',1,"FilterOrderHistory"."EndDate" ) >= "Orders"."Date"
WHERE "ViewOfOrderStatusHist"."Status" = 'Open' AND "FilterOrderHistory"."ID" = 0
Any ideas how to get this query to run as fast in 2.4 as it does in the 1.8 engine?