Results 1 to 2 of 2

Thread: DB2 Query help

  1. #1
    Join Date
    May 2015
    Posts
    4

    Unanswered: DB2 Query help

    Hi,

    I have a table a that contains range start and range end columns as varchar. This can contain number ranges and alphabet ranges. How do I write a query to validate if an input number/string is within the range?

    Sample values in the tableA

    range_start range_end
    100000 199999
    300000 599999
    010000000000 030000000000
    050000000000 080000000000

    The below query fails for input value 600000 which should return as 0 rather it returns as 1.

    SELECT count(*) FROM TableA
    WHERE
    input_no >= RANGE_START AND
    input_no <= RANGE_END
    FETCH FIRST 1 ROW ONLY;

    This query works fine but if the tableA contains any ranges that has alphabets, this throws an exception.

    SELECT count(*) FROM TableA
    WHERE LENGTH(RTRIM(TRANSLATE(input_no, '*', ' 0123456789'))) = 0 AND
    input_no >= CAST(RANGE_START AS BIGINT) AND
    input_no <= CAST(RANGE_END AS BIGINT)
    FETCH FIRST 1 ROW ONLY;


    Can you please help me on this? I am trying to have a query that can handle both alphabet and number comparison.

    Thanks

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    that's going to be a performance issue no matter how you do it. An alpha number is ranged/ordered(1,10,100,1000,2,20,200,2000) differently than an actual number(1,10,20,100,200,1000,2000). So, to deal with it you are going to have to perform functions as you did above in your second query. Now you just need to wrap that portion up in parenthesis and put an OR condition to do similar for just the alpha characters. I hope you don't have a mix of alphanumeric in the same string as that will take on a whole other set of stringing and ranging, that I don't want to deal with myself. Your pretty lucky that Joe Celko normally only trolls the SQL Server side of this forum or he might be giving you a good lashing about the mixed use of a field and how programming and design should be done.
    Dave

Posting Permissions

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