Results 1 to 6 of 6

Thread: Query Question

  1. #1
    Join Date
    Aug 2004
    Posts
    13

    Unanswered: Query Question

    I have this query, but I need to tune it. I am very new to SQL and I think I need to create a procedure, but not sure where to start, as I have never created one before. Here is the query

    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%A%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%B%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%C%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%D%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%E%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%F%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%G%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%H%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%I%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%J%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%K%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%L%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%M%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%N%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%O%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%P%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%Q%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%R%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%S%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%T%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%U%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%V%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%W%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%X%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%Y%'
    UNION
    SELECT * FROM T_HU_MASTER WHERE HU_ID LIKE '%Z%'

    as you can see, I need a to validate that the HU_ID(varchar2) does not contain a letter. Is there a better way to a) structure this query or b) create a stored procedure?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    It appears to me that you were not paying close enough attention in class.
    > I need a to validate that the HU_ID(varchar2) does not contain a letter.
    What you put togther won't come close to accomplishing this goal.
    Your SQL will do 26 full table scans on the table to get the wrong answer.
    What are you supposed to do when HU_ID(varchar2) does not contain a letter?
    What are you supposed to do when HU_ID(varchar2) does contain a letter?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2004
    Posts
    13
    I am not in class. Just was assingned to a new task which I have never done before. (ie. SQl)

    What are you supposed to do when HU_ID(varchar2) does not contain a letter?

    It is valid, and will not do anything with it.

    What are you supposed to do when HU_ID(varchar2) does contain a letter?

    return these rows.

    It appears to me that you were not paying close enough attention in class.
    > I need a to validate that the HU_ID(varchar2) does not contain a letter.
    What you put togther won't come close to accomplishing this goal.

    True. Sorry, I want to return all rows that contain a letter.

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Two alternatives:

    1) Use the TRANSLATE function:

    WHERE TRANSLATE(hu_id,'#ABCDEFGHIJKLMNOPQRSTUVWXYZ','#') != hu_id

    i.e. if hu_id different when you remove all the letters, then it must contain letter(s).

    2) WHERE UPPER(hu_id) != LOWER(hu_id)

    ... since only letters are affected by UPPER and LOWER.

  5. #5
    Join Date
    Aug 2004
    Posts
    13
    Thanks a bunch. I had not even though of that.

    2) WHERE UPPER(hu_id) != LOWER(hu_id)

    ... since only letters are affected by UPPER and LOWER.

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Quote Originally Posted by anacedent
    It appears to me that you were not paying close enough attention in class.
    > I need a to validate that the HU_ID(varchar2) does not contain a letter.
    What you put togther won't come close to accomplishing this goal.
    Your SQL will do 26 full table scans on the table to get the wrong answer.
    What are you supposed to do when HU_ID(varchar2) does not contain a letter?
    What are you supposed to do when HU_ID(varchar2) does contain a letter?
    There's no need to be quite so aggressive. Obviously, the reason a lot of people come to this forum is because they are new to Oracle. There is a lot to learn, and a lot of docs to read. If you find these people annoying, you don't have to answer their questions.

Posting Permissions

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