Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606

    Unanswered: More SQL Clean-Up

    This was leftover from some old Oracle rule-based optimization SQL:
    Code:
    ((SYSDATE BETWEEN
          FROM_DATE AND TO_DATE
            AND FROM_DATE IS NOT NULL
            AND TO_DATE IS NOT NULL)
      OR (FROM_DATE < SYSDATE
            AND TO_DATE IS NULL
            AND FROM_DATE IS NOT NULL)
      OR (FROM_DATE IS NULL
            AND TO_DATE IS NULL))
    FROM_DATE and TO_DATE are, naturally, date types.

    I was thinking something like this, for a start:
    Code:
        (     NVL( FROM_DATE, SYSDATE - 1 ) < SYSDATE
          AND NVL( TO_DATE,   SYSDATE + 1 ) > SYSDATE )
    Would that be logically equivalent to the above mess?

    Another table looks something like this:
    Region( Type, Start_Range, End_Range )
    Address( Zip, City, Stat_Code, Natn_Code )

    Region has rows that look like this:
    { 'COUNTY', 'C1', 'C1' }
    { 'COUNTY', 'C2', 'C2' }
    { 'ZIP', '12345', '12345-9999' }
    { 'ZIP', '12346', '12346-9999' }

    Then:
    Code:
     AND(  
           (     TYPE      = 'CITY'
             AND CITY      = START_RANGE
             AND STAT_CODE = END_RANGE )
    		 
        OR (     TYPE      = 'STATE'
             AND STAT_CODE = START_RANGE )
    		 
        OR (     TYPE      = 'ZIP'
             AND ZIP BETWEEN START_RANGE AND END_RANGE )
    
        OR (     TYPE      = 'COUNTY'
             AND CNTY_CODE = START_RANGE )
    
        OR (     TYPE      = 'NATION'
             AND NATN_CODE = START_RANGE ) )
    Thanks,

    Matt

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    actually, yours is probably safer, in addition to being simpler

    nice job

    dunno about oracle (but if it's old code, it's probably okay), but i see a logical hole in the old code

    doesn't mean there is a logical hole, only that i think i see one

    look at the three parts of the old code, and assume FROM_DATE is null

    the first part starts off SYSDATE BETWEEN null AND... so that'll be UNKNOWN, which is then ANDed with something else, so it'll never be TRUE

    then the second part starts off null < SYSDATE, so that'll be UNKNOWN

    the third part starts off null is null, which is TRUE, but this is then ANDed with TO_DATE is null

    so if FROM_DATE is null and TO_DATE isn't, all those records are skipped

    your second example is missing CNTY_CODE in the Address record, but i get the general idea of what this design is trying to do

    i'm slightly uncomfortable about how it appears to duplicate cities across different states using the state code as the "city" end range value, but it's kind of inventive and a creative solution to that problem

    nevertheless, i don't see the benefit of combining unrelated foreign keys into a common lookup table, and would prefer to use separate city, state, county, and nation table

    zips don't need a lookup -- although a range table might be used as a validation mechanism

    rudy

  3. #3
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    I'll use the new date range part.

    The problem is with the zip code et al lookups.

    This code is trying to see if a particular address is in a given region code. I forgot a column on region:
    Region( Code, Type, Start_Range, End_Range )
    Address( Zip, City, Cnty_Code, Stat_Code, Natn_Code )

    So I could group different types into a logial 'region'. Say 'Southwest Ohio' could consist of the city Cincinnatti and the zips 45053, 45056 and the counties Hamilton and Butler.

    Rows would be:
    Region
    { 'SWOH', 'COUNTY', 'BUTLER', 'BUTLER' }
    { 'SWOH', 'COUNTY', 'HAMILTON', 'HAMILTON' }
    { 'SWOH', 'ZIP', '45053', '45053-9999' }
    { 'SWOH', 'ZIP', '45056', '45056-9999' }
    { 'SWOH', 'CITY', 'CINCY', 'OH' }

    Now, I want to find all addresses which are in the SWOH region. The way it is done now is like this:
    Code:
     SELECT *
       FROM address
      WHERE EXISTS( SELECT 1
                      FROM region
                     WHERE code = 'SWOH'
                      AND(  
                          (     type      = 'CITY'
                            AND city      = start_range
                            AND stat_code = end_range )
                            
                       OR (     type      = 'STATE'
                            AND stat_code = start_range )
                            
                       OR (     type      = 'ZIP'
                            AND zip BETWEEN start_range AND end_range )
                            
                       OR (     type      = 'COUNTY'
                            AND cnty_code = start_range )
                            
                       OR (     type      = 'NATION'
                            AND natn_code = start_range ) )
                   )
    This does not seem like a 'pretty' way to do it.
    Thanks,

    Matt

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    given your structure, it ain't pretty, but it's right



  5. #5
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    My next iteration is:
    Code:
    SELECT *
      FROM address
     WHERE city IN ( SELECT start_range
                       FROM region
                      WHERE code = 'WOH'
                        AND type = 'COUNTY'
                   )
        OR EXISTS ( SELECT 1
                      FROM region
                     WHERE code = 'WOH'
                       AND type = 'ZIP'
                       AND zip BETWEEN start_range AND end_range
                  )
    It seems to run quite a bit faster.
    Thanks,

    Matt

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    nice

    yeah, and EXISTS will always outperform IN, if you can write them that way

Posting Permissions

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