Re: Hopefully Stupid Question

From: John Claxton <johnclaxton_at_advi.net>
Date: Tue, 4 Jun 2002 22:48:37 +0000 (UTC)
Message-ID: <a4011bca864e4830b63c70e392110e89.96068@mygate.mailgate.org>

Spence,

        There are no stupid questions ... but there are plenty of stupid answers. None of which appear here. The posts in response to your question
are quite good and you should learn a little SQL from them. In the mean time, pick up a good book on SQL (not the Oracle manual ... its not meant
to teach SQL, its more a reference manual) and play around [ with scott/tiger? ].

        I'm glad to see posts like this where basic questions are asked without someone feeding their own egos in response.

        Here's another suggestion: use alias names for the tables. That will make the query a lot easier to type. Reading queries with alias names
will become second nature.

        ie

	Select t1.data1, t2.data2, t3.data3, t1.TS
	  from table1 t1, table2 t2, table3 t3
	 where t1.TS = t2.TS
  	   and t2.TS = t3.TS;

	If the timestamp was consistant across all three inserts, which it 

may be, this will work. See other posts for an explanation of why it might not work. The timestamp could differ even if all three rows were inserted as part of the same transaction if SYSDATE was used in the VALUES clause. If it was, the dates will have to potential to differ as the clock ticks away during the inserts. If you find this to be the case
and have access to the code that loads the data, consider saving a timestamp
into a variable before the triple-insert transaction runs. Then use that
variable in the query instead of SYSDATE.

jc
"spence" <devnull635_at_netscape.com> wrote in message news:Gx730C.2p6_at_research.att.com

> 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

-- 
Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

Received on Tue Jun 04 2002 - 17:48:37 CDT