Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Location
    NH
    Posts
    87

    Cool Unanswered: Oracle: single-row search

    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 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

  3. #3
    Join Date
    May 2004
    Location
    NH
    Posts
    87
    Wouldn't it be great if I could type:

    'operation1' IN (op_no%)

    Thanks for the help...

    Warren

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    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.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •