Results 1 to 5 of 5

Thread: Filtered Query

  1. #1
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195

    Unanswered: Filtered Query

    I need to be able to query the database depending on certain criteria. Here is a statement I use to get all 'ALUMINUM' items from the DB:

    Code:
    SELECT IM_KEY FROM IM WHERE IM_PROD_CODE = 'M' AND IM_CATALOG = 'ALUMINUM'
    Now, I need to take this a step further. I need to be able to look at the IM_KEY and see if there is a two digit code in it. Here are the codes:

    SH = Sheet
    BR = Bar
    PL = Plate
    RD = Round
    AG = Angle
    TB = Tube

    So lets say I need to get all aluminum sheets, I'd do the earlier select statement and then I'd need to check to see if 'SH' was somewhere in the IM_KEY. Can someone help me out? Thanks, Jeremy
    Nothing better than a good ride.

  2. #2
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204

    Re: Filtered Query

    Originally posted by JCScoobyRS
    I need to be able to query the database depending on certain criteria. Here is a statement I use to get all 'ALUMINUM' items from the DB:

    Code:
    SELECT IM_KEY FROM IM WHERE IM_PROD_CODE = 'M' AND IM_CATALOG = 'ALUMINUM'
    Now, I need to take this a step further. I need to be able to look at the IM_KEY and see if there is a two digit code in it. Here are the codes:

    SH = Sheet
    BR = Bar
    PL = Plate
    RD = Round
    AG = Angle
    TB = Tube

    So lets say I need to get all aluminum sheets, I'd do the earlier select statement and then I'd need to check to see if 'SH' was somewhere in the IM_KEY. Can someone help me out? Thanks, Jeremy
    How about just adding an extra check in the where clause?

    Code:
    SELECT IM_KEY 
    FROM IM 
    WHERE IM_PROD_CODE = 'M' 
    AND IM_CATALOG = 'ALUMINUM'
    AND IM_KEY LIKE '%SH%';
    If this won't work, please provide more detailed information about the structure of and data format in the IM_KEY field. Also, as features change between database versions, please always include the database version in your posts.

    HTH,
    Patrick

  3. #3
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Sorry about that. The database is 8.1.7 (Oracle 8i). The datatype for the IM_KEY field is (VarChar2(30)) and these are examples:

    Code:
    AL3003H14.12548120 
    ALU3003H14SH.04048120 
    ALU3003H14SH.05048144 
    ALU3003H14SH.0633636 
    ALU3003H14SH.06348120 
    ALU3003H14SH.08048144 
    ALU3003H14SH.09048120 
    ALU3003H14SH.12548120 
    ALU3003H14SH.12548144 
    ALU5052H32.0204896 
    ALU5052H32.19048144 
    ALU5052H32SH.03248120 
    ALU5052H32SH.04036120 
    ALU5052H32SH.04036144 
    ALU5052H32SH.04048120 
    ALU5052H32SH.04048144 
    ALU5052H32SH.05036120 
    ALU5052H32SH.05036144 
    ALU5052H32SH.05048120 
    ALU5052H32SH.05048144 
    ALU5052H32SH.06336120 
    ALU5052H32SH.06336144 
    ALU5052H32SH.0633624 
    ALU5052H32SH.0633628 
    ALU5052H32SH.0633632 
    ALU5052H32SH.0633636 
    ALU5052H32SH.0633649 
    ALU5052H32SH.06348120 
    ALU5052H32SH.06348144 
    ALU5052H32SH.0634824 
    ALU5052H32SH.0634830 
    ALU5052H32SH.0634836 
    ALU5052H32SH.0634840 
    ALU5052H32SH.0634848 
    ALU5052H32SH.08036120 
    ALU5052H32SH.08036144 
    ALU5052H32SH.08048120 
    ALU5052H32SH.08048144 
    ALU5052H32SH.0804824 
    ALU5052H32SH.0804832 
    ALU5052H32SH.0804836 
    ALU5052H32SH.0804840 
    ALU5052H32SH.0804848 
    ALU5052H32SH.09036120 
    ALU5052H32SH.09036144 
    ALU5052H32SH.0903616 
    ALU5052H32SH.0903624 
    ALU5052H32SH.0903640 
    ALU5052H32SH.09048120 
    ALU5052H32SH.09048144 
    ALU5052H32SH.0904824 
    ALU5052H32SH.0904828 
    ALU5052H32SH.0904836 
    ALU5052H32SH.0904848 
    ALU5052H32SH.0904852 
    ALU5052H32SH.10036120 
    ALU5052H32SH.10048120 
    ALU5052H32SH.10048144 
    ALU5052H32SH.12536120 
    ALU5052H32SH.12536144 
    ALU5052H32SH.12548120 
    ALU5052H32SH.12548144 
    ALU5052H32SH.1254824 
    ALU5052H32SH.1254828.8 
    ALU5052H32SH.1254836 
    ALU5052H32SH.1254840 
    ALU5052H32SH.16048144 
    ALU5052H32SH.19036144 
    ALU5052H32SH.19048144 
    ALU5052H32SH.1904848 
    ALU5052H32SH.25048120 
    ALU5052OSH.05048144 
    ALU6061T6.125X5.625X5.5 
    ALU6061T6.75X15.875X12.375 
    ALU6061T6PL.025X15.375X11.75 
    ALU6061T6PL.75X15.875X12.375 
    ALU6061T6PL1.0X6.0X8.125 
    ALU6061T6PL1.75X15.875X12.375 
    ALU6061T6SH.03248144 
    ALU6061T6SH.04048144 
    ALU6061T6SH.05036120 
    ALU6061T6SH.05048120 
    ALU6061T6SH.05048144 
    ALU6061T6SH.06348120 
    ALU6061T6SH.06348144 
    ALU6061T6SH.08048120 
    ALU6061T6SH.08048144 
    ALU6061T6SH.09048144 
    ALU6061T6SH.10048144 
    ALU6061T6SH.12548144 
    ALU6061T6SH1.75X15.875X12.375
    This is a list of all IM_KEY s that where the IM_CATALOG = 'ALUMINUM' and IM_PROD_CODE = 'M'. Now, I need to look for 'SH' in each one and filter the results. Thanks, Jeremy
    Nothing better than a good ride.

  4. #4
    Join Date
    Sep 2002
    Location
    Ohio
    Posts
    204
    Originally posted by JCScoobyRS
    Sorry about that. The database is 8.1.7 (Oracle 8i). The datatype for the IM_KEY field is (VarChar2(30)) and these are examples:

    Code:
    AL3003H14.12548120 
    ALU3003H14SH.04048120 
    ALU3003H14SH.05048144 
    ALU3003H14SH.0633636 
    <SNIP> 
    ALU6061T6SH.09048144 
    ALU6061T6SH.10048144 
    ALU6061T6SH.12548144 
    ALU6061T6SH1.75X15.875X12.375
    This is a list of all IM_KEY s that where the IM_CATALOG = 'ALUMINUM' and IM_PROD_CODE = 'M'. Now, I need to look for 'SH' in each one and filter the results. Thanks, Jeremy

    The the query in my first post work? If not, why?

    Patrick

  5. #5
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    It did, just wanted to give you more information just in case I needed to come back with another question. Thanks, Jeremy
    Nothing better than a good ride.

Posting Permissions

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