1. You use a join. Something like this: Untested!
SELECT DISTINCT
CC_STRINGENTEREXIT(ems.passdatetime,emsqi.PLATENUMBER) as DetailEnterExit,
CC_DURATIONENTEREXIT(emsqi.PLATENUMBER, ems.passdatetime) as DurationInZone
FROM EMSINFO ems JOIN EMSQUIRKINFO emsqi
ON emsqi.emsinfoid = ems.emsinfoid
WHERE trunc(ems.passdatetime) = trunc(to_date('26-DEC-13 11.46.56', 'DD-Mon-YY HH24:MI:SS'))
AND CC_GETNEXTENTER(ems.EMSINFOID, emsqi.PLATENUMBER, ems.PASSDATETIME) <> 0
AND CC_CheckForExit(ems.PASSDATETIME, (
SELECT ems2.PASSDATETIME from EMSINFO ems2
WHERE ems2.EMSINFOID = CC_GETNEXTENTER(ems.EMSINFOID,emsqi.PLATENUMBER, ems.PASSDATETIME)
),emsqi.PLATENUMBER) <> 0
AND ems.masterplatenumber = '150444833' and rownum <100 ;
2. Use a CTE, Something like this: Still untested!
WITH bla AS (
SELECT CC_STRINGENTEREXIT(ems.passdatetime,emsqi.PLATENUMBER) as DetailEnterExit,
CC_DURATIONENTEREXIT(emsqi.PLATENUMBER, ems.passdatetime) as DurationInZone
CC_GETNEXTENTER(ems.EMSINFOID,emsqi.PLATENUMBER, ems.PASSDATETIME) GetNextEnter
FROM EMSINFO ems JOIN EMSQUIRKINFO emsqi
ON emsqi.emsinfoid = ems.emsinfoid
WHERE trunc(ems.passdatetime) = trunc(to_date('26-DEC-13 11.46.56', 'DD-Mon-YY HH24:MI:SS'))
AND ems.masterplatenumber = '150444833'
)
SELECT DISTINCT
DetailEnterExit,
DurationInZone
WHERE GetNextEnter <> 0
AND CC_CheckForExit(
ems.PASSDATETIME,
(
SELECT ems2.PASSDATETIME from EMSINFO ems2
WHERE ems2.EMSINFOID = GetNextEnter
)
,emsqi.PLATENUMBER) <> 0
AND rownum <100
;
Now, keep in mind that I don't have a clue what your functions do, so my suggestions is only to show you the methodology, It's up to you to see if it gives the right result. But you want as much of the filtering in the CTE as possible to keep the amount of data down.
Wrong is evil and must be defeated. - Jeff Ello[^]
