Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Tricky query

Tricky query

From: Fredrik Wahlgren <fredrik.p.wahlgren_at_mailbox.swipnet.se>
Date: Mon, 28 Jul 2003 10:27:04 +0200
Message-ID: <QTAVa.40436$zL.221@news1.bredband.com>


Hi

I have a table with a few columns like year, period, account,company, currency and so on. This table will be joined with one or several data tables, data00, data01
In order to make a better main query, I want to select the years and the currency for which there is only one currency. Now, If i make a query like this

SELECT DISTINCT thisyear, currency FROM input_table

This is what I get

thisyear currency

94     USD    // Don't want this row, two currencies used for '94
94     GBP    // Don't want this row, two currencies used for '94
95     EUR
96     EUR
97     EUR
98     EUR   // Don't want this row, three currencies used for '98
98     SEK   // Don't want this row, three currencies used for '98
98     USD   // Don't want this row, three currencies used for '98
99     EUR


I need both the year and the currency since I will save these in an array, The idea is to make a main query that looks like this. And , yes, this makes the query
significantly faster

SELECT this, that from data94 a, input_table b

WHERE a.currency = b.currency AND ...                                  //
Non optimised condition
UNION ALL SELECT this, that from data95 a, input_table b
WHERE a.currency = 'EUR' AND ...                                        //
Optimised condition
UNION ALL SELECT this, that from data96 a, input_table b WHERE a.currency = 'EUR' AND ...

Thanks in advance,
Fredrik Received on Mon Jul 28 2003 - 03:27:04 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US