×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Simple SELECT question

Simple SELECT question

Simple SELECT question

(OP)
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!

RE: Simple SELECT question

(OP)
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

RE: Simple SELECT question

Replace unique with distinct 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

select distinct IDX, BARCODE, PROJECT
from DATACARRIER join SEIS_DISCRIPTION on DATACARRIER.IDX = SEIS_DISCRIPTION.IDX
where LINE like 'U3%';


May be slower or faster depending on product, indexes, amount of data etc.

RE: Simple SELECT question

(OP)
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

RE: Simple SELECT question

(OP)
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

RE: Simple SELECT question

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?

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


Or maybe you have to write INNER JOIN, instead of just JOIN...?

RE: Simple SELECT question

(OP)
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!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close