Creating 2 tables from one on the fly - mind draws a blank

[jimineep]

Hi guys,

I am currently writing a complicated script (perl) to do something I feel I could do with one SQL command:

I have a table -

EXP_NAME ID CHANGE

A AX 234.23
A AB -23.23
A AC 22.23
B AX -43.25
B BZ 233.75
B AC -77.23

Basically I want to pull out all of the data for the rows where the ID's match between the EXP_NAMEs, so in this case A AX with B AX, A AC with B AC, A AB and B BZ would be ignored since they do not match.

This would be trivial if the EXPs were in different tables, but they're not!

Cheers,

 

[r937]

all you have to do is write the query as though they were ;-)

select one.EXP_NAME   
     , one.ID    
     , one.CHANGE
     , two.CHANGE
  from atable as one
inner
  join atable as two
    on two.EXP_NAME = one.EXP_NAME  
   and two.ID       = one.ID

r937.com | rudy.ca

 

[jimineep]

Hmm not working, its throwing back:

from TABLE_NAME as one
*

ERROR at line 5:
ORA-00933: SQL command not properly ended

Might be an oracle thing...?

 

[r937]

oracle does not support ANSI SQL in this particular instance

remove the "AS" keyword

r937.com | rudy.ca