| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: SQL query without UNION clause
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
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')
)
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
jan_at_pruner.cz
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: jan_at_pruner.cz
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: ListGuru_at_fatcity.com (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).
Received on Thu Dec 19 2002 - 02:43:58 CST
 |
 |