From: mcstockX@Xenquery   
      
   "dbarchitech"  wrote in message   
   news:9d3dc423.0403041227.79c5ef4e@posting.google.com...   
   | "Mark C. Stock"  wrote in message   
   news:...   
   | > "dbarchitech"  wrote in message   
   | > news:9d3dc423.0403021434.18a3078e@posting.google.com...   
   | > | hi,   
   | > |   
   | > | i'm building a query to find narrow ranges of zip codes within broader   
   | > | ranges in a table (for tax purposes). for example:   
   | > |   
   | > | LOW_ZIP    HI_ZIP   
   | > | 23400       23499   
   | > |   
   | > | need to find   
   | > | 23401      23402   
   | > | 23403      23440   
   | > | 23441      23... you get the picture. :)   
   | > |   
   | > | so, each row has a low value and a hi value, then i need to run a   
   | > | subquery to take those values and find narrower ranges. any ideas?   
   | >   
   | > yeah, but you got to show us yours first ;-)   
   | >   
   | > ;-{ mcs   
   |   
   | yeah, well mine doesn't work!!! :-$   
   |   
   | select sales_tax_id,   
   | from_postal_code,   
   | to_postal_code   
   | from ar_sales_tax a,   
   | (select from_postal_code as fpc,   
   |            to_postal_code as tpc   
   |            from ar_sales_tax   
   |            where rowid = rowid) b   
   | where from_postal_code between b.fpc and b.tpc   
   | and to_postal_code between b.fpc and b.tpc   
   | and end_date is null   
   |   
   | i need to somehow scan the entire table row by row looking for records   
   | that fit within the ranges... i'm thinking i may need to do this using   
   | a cursor.   
   |   
   | -cam   
      
   well, know we know that you tried something!   
      
   you won't need a cursor, and you won't need to be one either ;-)   
      
   i'll take a stab at it, but it would be nice if you could elaborate on   
   'doesn't work'   
      
   regarding your query   
      
       select ...   
       from   ar_sales_tax a   
                   ,(   
                       select ...   
                       from   ar_sales_tax   
                       where rowid = rowid   
                    ) b   
      
   the rowid=rowid is always true, don't you think?   
   so there is no added value in that statement   
   and there is no added value in the in-line view (the in-line subquery you've   
   aliased as 'b'   
      
   so, if that's simplified to   
       select ...   
       from  ar_sales_tax a   
                , ar_sales_tax b   
       where  a.from_postal_code between b.from_postal_code and   
   b.to_postal_code   
       and      a.to_postal_code       between b.from_postal_oce and   
   b.to_postal_code   
       and      a.end_date is null   
      
   all that's going to do for you is give you rows in the 'A' pass at your   
   table where rows are found in the 'B' pass with a range of codes that   
   overlap each of the codes from the 'A' pass -- but it doesn't tell you   
   anything about the relationship between the two passes, and doesn't limit   
   the results to any specific pair of codes   
      
   assuming you're looking for ranges narrower than your 23400/23499 sample,   
   how about narrowing one of the passes by that pair of values? or returning   
   the range from the A pass and the range from the B pass in your select   
   clause, so that you've got something to work with?   
      
   -{ mcs   
      
   --- SoupGate-Win32 v1.05   
    * Origin: you cannot sedate... all the things you hate (1:229/2)