Oracle: single-row search

WarrenM · #1 (**permalink**) 06-30-04, 13:50

Hi all,

Table structured as such:

item_id, serial_num,project_id, type, op_1, op_2, op_3, ..., op_20

and values are:
1234, -, 001, op_code, operation1, operation2, operation2a, null ..., null
1234, A, 001, status, X, X, Queue, null ..., null
1234, B, 001, status, X, X, Queue, null ..., null
1234, C, 001, status, X, X, Run, null ..., null

5555, -, 002, op_code, operation1a, opeartion2a, operation2b, null, ..., null
5555, A, 002, status, X, Queue, null, null, ..., null
5555, B, 002, status, X, Queue, null, null, ..., null

What I need to do is grab all the items which will hit a certain operation, is there an easier way than

Code:

SELECT * FROM TABLE t1, WHERE t1.op_1 = 'operation1' OR t1.op_2 = 'operation1' OR ... t1.op_20 = 'operation1' AND type != 'status'

I can't find a single-row function that accomodates this nor do any of my searches give me any results that help...

Thanks in advance!

-Warren

Pat Phelan · #2 (**permalink**) 06-30-04, 14:54

The best I can think of would be easier to type:

Code:

SELECT *
   FROM TABLE t1
   WHERE 'status' != type
      AND 'operation1' IN (t1.op_1, t1.op_2, t1.op3 ... t1.op_20);

Logically it is the same, just less typing.

-PatP

WarrenM · #3 (**permalink**) 07-01-04, 07:27

Wouldn't it be great if I could type:

'operation1' IN (op_no%)

Thanks for the help...

Warren

andrewst · #4 (**permalink**) 07-01-04, 07:40

It would have been better to have designed the table with one op column and have 20 rows, instead of one row with 20 op columns.

Pat Phelan · #5 (**permalink**) 07-01-04, 08:38

Quote:

Originally Posted by andrewst

It would have been better to have designed the table with one op column and have 20 rows, instead of one row with 20 op columns.

Normalization! Ain't it a wunnerful thing ?!?!

I see so many problems like this that would be a lot simpler if the database was in at least the third normal form. Unfortunately, while I can envision the perfect world, I can't even get a visitors pass!

-PatP

Internet Services

Web Development

Digital Marketing

Consumer Tech