I think you can translate it as OR, but you have to use some DISTINCT on output rows (because you use UNION and not UNION ALL). Without DISTINCT it is like: SELECT H1.OID HISTORIEOID ,FAHRZEUG.AMTLICHESKENNZEICHEN ,FAHRZEUG.OID ,H1.PRODUKT ,H1.AUFTRAGSPOSITIONSNR ,H1.MYTECHOBJEKT FROM FAHRZEUG, HISTORIE H1 WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+) AND ( (TO_DATE(H1.DATUMSTR,'YYYY-MM-DD') = ( select max(TO_DATE(H1.DATUMSTR,'yyyy-mm-dd')) from historie, FAHRZEUG WHERE FAHRZEUG.OID = HISTORIE.MYTECHOBJEKT AND HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT) AND H1.PRODUKT IN ('HU', 'AU') ) OR H1.PRODUKT IN('DS', 'NFZ','BES', 'DekraSiegel', '700000', '700202')) ;
And I think you can change select max(TO_DATE(H1.DATUMSTR,'yyyy-mm-dd')) to select TO_DATE(MAX(H1.DATUMSTR),'YYYY-MM-DD') it can now use index on column H1.DATUMSTR (or create function based index) JP On Thursday 19 December 2002 08:53, you wrote: > Hi all, > > How do I forumlate the below query without using the UNION clause? > > SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN, > FAHRZEUG.OID, > H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT > FROM FAHRZEUG,HISTORIE H1 > WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+) > AND TO_DATE(H1.DATUMSTR,'YYYY-MM-DD') = (select > max(TO_DATE(H1.DATUMSTR,'yyyy-mm-dd')) > from historie ,FAHRZEUG WHERE FAHRZEUG.OID = HISTORIE.MYTECHOBJEKT AND > HISTORIE.MYTECHOBJEKT=H1.MYTECHOBJEKT) > AND H1.PRODUKT IN('HU', 'AU') > UNION > SELECT H1.OID HISTORIEOID,FAHRZEUG.AMTLICHESKENNZEICHEN, > FAHRZEUG.OID, > H1.PRODUKT,H1.AUFTRAGSPOSITIONSNR,H1.MYTECHOBJEKT > FROM FAHRZEUG,HISTORIE H1 > WHERE FAHRZEUG.OID = H1.MYTECHOBJEKT(+) AND H1.PRODUKT IN('DS', 'NFZ', > 'BES', 'DekraSiegel', '700000', '700202'); > > I can't use the union clause because I am using an Object-Relationship tool > called Cocobase which doesn't support the UNION clause. Any help in this > regard is very much appreciated. > > Thanks and Regards, > > Ranganath > > WARNING: The information in this message is confidential and may be legally > privileged. It is intended solely for the addressee. Access to this > message by anyone else is unauthorised. If you are not the intended > recipient, any disclosure, copying, or distribution of the message, or any > action or omission taken by you in reliance on it, is prohibited and may be > unlawful. Please immediately contact the sender if you have received this > message in error. Thank you. -- Pruner Jan [EMAIL PROTECTED] http://jan.pruner.cz/ ----------------------------- Only Robinson Crusoe had all his work done by Friday -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Jan Pruner INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).