Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Simple SELECT question 1

Status
Not open for further replies.
Feb 12, 2002
80
NO
Hi,

I'm sure this is easy to do - but I'm still building my knowledge as I work so turning to trusty tek-tips forums for help ...

Table 1: SEIS_DISCRIPTION
Code:
SEIS_DISCRIPTION	Null?	  Type
 ------------------------------- ----
 IDX				  NUMBER
 FORMAT 			  VARCHAR2(6)
 START_CDP			  NUMBER
 END_CDP			  NUMBER
 FIRST_RECORD			  NUMBER
 LAST_RECORD			  NUMBER
 SAMPLES			  NUMBER
 SAMP_RT			  NUMBER
 LOGICAL_REEL_NUM		  NUMBER
 CHANNELS			  NUMBER
 RECORD_LENGTH			  NUMBER
 JOB_ID 			  NUMBER
 FIRST_X_COORD			  NUMBER
 LAST_X_COORD			  NUMBER
 FIRST_Y_COORD			  NUMBER
 LAST_Y_COORD			  NUMBER
 LINE				  VARCHAR2(16)

Table 2: DATACARRIER
Code:
 DATACARRIER		Null?	  Type
------------------------------- ----
 IDX				  NUMBER
 BARCODE			  VARCHAR2(12)
 PROJECT			  VARCHAR2(20)
 REEL_NUM			  VARCHAR2(12)
 LOCATION			  NUMBER
 CREATION_DATE			  DATE
 FIRST_LINE			  VARCHAR2(16)
 DENSITY			  VARCHAR2(1)
 KINDCODE			  VARCHAR2(6)
 KINDDIS			  NUMBER
 ARCHIVE_NAME			  VARCHAR2(16)
 QC_STATUS			  VARCHAR2(5)


I am trying to find all unique BARCODE and DATACARRIER values from DATACARRIER where LINE = U3* in SEIS_DISCRIPTION.

I know that te IDX value is the key - jsut not how to link the results!

Simple eh?

All help appreciated,
littleIdiot.

PS Spelling of "DISCRIPTION" is intentionally incorrect!
 
Will this work as I think it should?
Code:
select unique IDX, BARCODE, PROJECT from DATACARRIER where IDX in (select idx from SEIS_DISCRIPTION where LINE like 'U3%');

thanks
 
Replace [tt]unique[/tt] with [tt]distinct[/tt] and it will work.

(Note that you are selecting the distinct combinations of IDX, BARCODE and PROJECT - not only IDX and BARCODE as you mentioned in your first posting.)

You can also write the query as
[tt]
select distinct IDX, BARCODE, PROJECT
from DATACARRIER join SEIS_DISCRIPTION on DATACARRIER.IDX = SEIS_DISCRIPTION.IDX
where LINE like 'U3%';[/tt]

May be slower or faster depending on product, indexes, amount of data etc.
 
Thanks ... but it failed and I'm not sure why ...
Code:
SQL> select distinct IDX, BARCODE, PROJECT from DATACARRIER join SEIS_DISCRIPTION on DATACARRIER.IDX = SEIS_DISCRIPTION.IDX where LINE like 'U3%';
select distinct IDX, BARCODE, PROJECT from DATACARRIER join SEIS_DISCRIPTION on DATACARRIER.IDX = SEIS_DISCRIPTION.IDX where LINE like 'U3%'
                                                            *
ERROR at line 1:
ORA-00933: SQL command not properly ended

 
this might be easier to read:

Code:
SQL> select distinct IDX, BARCODE, PROJECT
  2  from DATACARRIER join SEIS_DISCRIPTION on DATACARRIER.IDX = SEIS_DISCRIPTION.IDX
  3  where LINE like 'U3%';
from DATACARRIER join SEIS_DISCRIPTION on DATACARRIER.IDX = SEIS_DISCRIPTION.IDX
                      *
ERROR at line 2:
ORA-00933: SQL command not properly ended
 
Well, I've never used Oracle, so I’m not really the right person to help you with this...

How do you use to end SQL statements?

Or does Oracle require old style joins?
[tt]
select distinct IDX, BARCODE, PROJECT
from DATACARRIER,
SEIS_DISCRIPTION
where DATACARRIER.IDX = SEIS_DISCRIPTION.IDX
and LINE like 'U3%'[/tt]

Or maybe you have to write INNER JOIN, instead of just JOIN...?
 
I use semi-colon (;) to close ...

and yes, it seems it needs old style joins ... this worked:

Code:
select distinct BARCODE, PROJECT 
from DATACARRIER, SEIS_DISCRIPTION 
where DATACARRIER.IDX = SEIS_DISCRIPTION.IDX 
and LINE like 'U3%';

Thanks for your help!
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top