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 -> Re: Hopefully Stupid Question

Re: Hopefully Stupid Question

From: Malcolm Dew-Jones <yf110_at_vtn1.victoria.tc.ca>
Date: 4 Jun 2002 13:23:55 -0800
Message-ID: <3cfd21db@news.victoria.tc.ca>


Daniel Morgan (dmorgan_at_exesolutions.com) wrote:
: spence wrote:

: > Hi,
: >
: > Forgive me if I'm having a brain fart, but I can't seem to translate what I
: > want to good SQL - I'm using Oracle 8.1.7. Here's the situation...
: >
: > I have say 3 tables and each of them has a character based timestamp (don't
: > ask) with an identical fieldname, TS. I want to select various fields from
: > each of the 3 tables and order the results based on the timestamp field they
: > all have in common, TS. Perhaps something similar to:
: >
: > select
: > table1.data1,
: > table2.data2,
: > table3.data3,
: > table1.TS,
: > table2.TS,
: > table3.TS
: > from
: > table1, table2, table3
: > order by TS;
: >
: > I realize that the above won't work because the TS in the order-by clause is
: > ambiguous. How can I tell it to combine all the TSs and sort the rows based
: > on that?
: >
: > Help!?!
: >
: > S

: What you wrote won't work for a number of reasons. The most eggregious is the
: fact that you have a Cartesian Join.

: Definitely spend some time learning about the WHERE clause.

: But back to your question.

: SELECT a.data1, b.data2, c.data3, a.ts
: FROM table1 a, table2 b, table3 c
: WHERE a.ts = b.ts
: AND a.ts = c.ts
: ORDER BY a.ts;

: Daniel Morgan

I suspect he means that the timestamp data is not identical in the three tables (just the column name).

In that case do one of the following

-1- all data, same colums of output

	select data1,TS from table1
	union all
	select data2,TS from table2
	union all
	select data3,TS from table3
	order by 2 
	;

I can't check if my "order by" is in the right spot to order the whole union, so look it up to be sure.

-2- all data, but different columns for output

	select data1,'','',TS from table1
	union all
	select '',data2,'',TS from table2
	union all
	select '','',data3,TS from table3
	order by 4
	;

(Same question about my placing of the order by).

In case 2, you might be able to force the data from different columns but with the same timestamp into the same row by wrapping the above query in another query that does a group by on the TS to merge the rows from the same time. (use MAX to get the data for each column). However, that only works correctly if each timestamp is unique within each original table.

        i.e. something like

	select max(data1),max(data2),max(data3),TS 
	from THE-ABOVE-2-
	group by TS
	order by TS
	;

MAX is not really taking the maximum here, it's actually just selecting the non-null data. Note caveat on whether the TS is unique in each original table for this to work correctly. Received on Tue Jun 04 2002 - 16:23:55 CDT

Original text of this message

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