Results 1 to 10 of 10
  1. #1
    Join Date
    May 2007
    Posts
    2

    Unanswered: SQL to find if field is low values

    I need assistance with a field in the first five positions is low-values and then has either spaces or values -- I need to get the ones where only the first 5 positions are low values?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Fancy posting some example data and what your expect your results to look like?
    George
    Home | Blog

  3. #3
    Join Date
    May 2007
    Posts
    2
    Here is an example of the data in column addr1 pic x(20):

    123 Cannon Way
    X'0000000000' Nasco place
    X'0000000000' Yellow Rd
    456 Jamison Ave

    In my select I want to get the 2 & 3 data example in my select, when i put the following SQL

    Select addr1
    ,addr2
    From TWIP120
    Where addr1 = X'00'

    I don't get rows 2 & 3 back because my whole data field isn't low values
    Last edited by Pattio; 05-30-07 at 13:57.

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I'm lost in translation...
    Code:
    Where addr1 = X'00'
    This isn't valid SQL.
    And what do you mean by "low values"
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by Pattio
    I need assistance with a field in the first five positions is low-values and then has either spaces or values -- I need to get the ones where only the first 5 positions are low values?
    The syntax varies depending on the SQL dialect... What vendor (DB2, Oracle, Microsoft, MySQL, etc) and version (1.2, 10.5, Rockafeller, Tornado, etc) of SQL are you using?

    -PatP

  6. #6
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Quote Originally Posted by Pattio
    Select ...
    From TWIP120
    Where addr1 = X'00'

    I don't get rows 2 & 3 back because my whole data field isn't low alues
    What about
    Code:
    Select ...
         From TWIP120 
       Where substr(addr1, 1, 5) = X'00'
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    Select ...
         From TWIP120 
       Where substr(addr1, 1, 5) = X'00'
    Won't you hit syntax errors with the single quotes?
    George
    Home | Blog

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    SUBSTR isn't standard sql either
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Change to a LIKE comparison?
    George
    Home | Blog

  10. #10
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    Still a bit platform-specific, but instead of asking for 'equal to "LOW VALUES"' one could maybe ask for 'smaller than any normal text'.
    When the underlying system uses ASCII or UNICODE, and assuming no ASCII values between 1 and 31 are present as first character of the addr1 field, the following would give the wanted rows:
    Code:
    Select ...
         From TWIP120 
       Where addr1 < ' '
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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