[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 389
  • Last Modified:

need to convert range of numbers in 1 row into a column

I have a table
with columns range_zip_code  zone
now the data in the table is as follows
range_zip_code ,  zone
000-004   ,1
5       , 2
006-009, 3
10, 4

so i have to convert this into
range_zip_code ,  zone
000, 1
002,1
003,1
004,1
005, 2
006,3
007,3
008,3
009,9
010, 4
-------------------------------
CREATE TABLE XXPQ_ZONES_CALCULATION
(
  RANGE_ZIP_CODES     VARCHAR2(1000 BYTE),
  SERVICING_STATES    VARCHAR2(1000 BYTE),
  FEDEX_EXPRESS_ZONE  VARCHAR2(100 BYTE),
  FEDEX_GROUND_ZONE   VARCHAR2(100 BYTE)
)
TABLESPACE XXPQ_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;
--------------------------------------
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'000-004', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'5', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'010-016', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'017-029', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'030-037', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'038-049', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'050-054', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'55', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'056-139', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'140-147', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'148-149', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'150-163', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'164-165', 'CONTIGOUS US', '2', '2');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'166-169', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'170-212', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'213', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'214', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'215', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'216-249', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'250-253', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'254', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'255-257', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'258-259', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'260-268', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'269', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'270-283', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'284', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'285-293', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'294', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'295-297', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'298-299', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'300-303', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'304', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'305-307', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'308-310', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'311', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'312-329', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'330-334', 'CONTIGOUS US', '6', '6');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'335-338', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'339-341', 'CONTIGOUS US', '6', '6');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'342', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'343', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'344', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'345', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'346-347', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'348', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'349', 'CONTIGOUS US', '6', '6');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'350-352', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'353', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'354-355', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'356-359', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'360-361', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'362', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'363-369', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'370-386', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'387-398', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'399', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'400-406', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'407-409', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'410-414', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'415-418', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'419', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'420-427', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'428-429', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'430-432', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'433-436', 'CONTIGOUS US', '2', '2');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'437-439', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'440-449', 'CONTIGOUS US', '2', '2');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'450-457', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'458', 'CONTIGOUS US', '2', '2');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'459-464', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'465-468', 'CONTIGOUS US', '2', '2');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'469-475', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'476-477', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'478-479', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'480-496', 'CONTIGOUS US', '2', '2');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'497', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'498-509', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'510-512', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'513-514', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'515-516', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'517-519', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'520-528', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'529', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'530-532', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'533', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'534-535', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'536', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'537', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'538-540', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'541-543', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'544-548', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'549', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'550-551', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'552', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'553-561', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'562', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'563-564', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'565-567', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'568-569', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'570-577', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'578-579', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'580-587', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'588', 'CONTIGOUS US', '6', '6');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'589', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'590-595', 'CONTIGOUS US', '6', '6');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'596-599', 'CONTIGOUS US', '7', '7');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'600-611', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'612-616', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'617-619', 'CONTIGOUS US', '3', '3');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'620', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'621', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'622-631', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'632', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'633-639', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'640-641', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'642-643', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'644-645', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'646', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'647-649', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'650-655', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'656-658', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'659', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'660-662', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'663', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'664-681', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'682', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'683-693', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'694-699', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'700-701', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'702', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'703-708', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'709', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'710-714', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'715', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'716-722', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'723-724', 'CONTIGOUS US', '4', '4');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'725-731', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'732', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'733', 'CONTIGOUS US', '6', '6');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'734-741', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'742', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'743-764', 'CONTIGOUS US', '5', '5');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'765-797', 'CONTIGOUS US', '6', '6');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'798-799', 'CONTIGOUS US', '7', '7');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'800-816', 'CONTIGOUS US', '6', '6');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'817-819', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'820-831', 'CONTIGOUS US', '6', '6');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'832-838', 'CONTIGOUS US', '7', '7');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'839', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'840-847', 'CONTIGOUS US', '7', '7');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'848-849', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'850', 'CONTIGOUS US', '7', '7');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'851', 'CONTIGOUS US', '7', '7');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'852-853', 'CONTIGOUS US', '7', '7');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'854', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'855-857', 'CONTIGOUS US', '7', '7');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'858', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'859-860', 'CONTIGOUS US', '7', '7');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'861-862', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'863-865', 'CONTIGOUS US', '7', '7');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'866-869', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'870-872', 'CONTIGOUS US', '6', '6');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'873', 'CONTIGOUS US', '7', '7');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'874-875', 'CONTIGOUS US', '6', '6');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'876', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'877-878', 'CONTIGOUS US', '6', '6');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'879-880', 'CONTIGOUS US', '7', '7');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'881-884', 'CONTIGOUS US', '6', '6');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'885', 'CONTIGOUS US', '7', '7');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'886-888', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'889-891', 'CONTIGOUS US', '7', '7');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'892', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'893', 'CONTIGOUS US', '7', '7');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'894-895', 'CONTIGOUS US', '8', '8');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'896', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'897', 'CONTIGOUS US', '8', '8');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'898', 'CONTIGOUS US', '7', '7');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'899', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'900-908', 'CONTIGOUS US', '8', '8');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'909', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'910-928', 'CONTIGOUS US', '8', '8');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'929', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'930-966', 'CONTIGOUS US', '8', '8');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'970-977', 'CONTIGOUS US', '8', '8');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'978-979', 'CONTIGOUS US', '7', '7');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'980-986', 'CONTIGOUS US', '8', '8');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'987', 'CONTIGOUS US', 'NA', 'NA');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'988-989', 'CONTIGOUS US', '8', '8');
INSERT INTO XXPQ_ZONES_CALCULATION ( RANGE_ZIP_CODES, SERVICING_STATES, FEDEX_EXPRESS_ZONE,
FEDEX_GROUND_ZONE ) VALUES (
'990-994', 'CONTIGOUS US', '7', '7');
commit;
 
----------------------------



0
pardeshirahul
Asked:
pardeshirahul
  • 6
  • 3
  • 2
1 Solution
 
slightwv (䄆 Netminder) Commented:
See if this works for you
drop table tab1 purge;
create table tab1(range_zip_code varchar2(20), zone number);

insert into tab1 values('000-004',1);
insert into tab1 values('5',2);
insert into tab1 values('006-009',3);
insert into tab1 values('10',4);
commit;


select distinct to_char(min_zip+level-1,'000') new_zip, zone
from
(
select zone, 
	to_number(regexp_substr(range_zip_code,'^[0-9]+')) min_zip,
	to_number(regexp_substr(range_zip_code,'[0-9]+$')) max_zip
from tab1
)
connect by level <= max_zip-min_zip+1
order by 1
/

Open in new window

0
 
sdstuberCommented:
SELECT COLUMN_VALUE range_zip_code, fedex_express_zone, fedex_ground_zone
  FROM (SELECT range_zip_codes,
               TO_NUMBER(REGEXP_SUBSTR(range_zip_codes, '[0-9]+', 1, 1)) l,
               TO_NUMBER(
                   NVL(REGEXP_SUBSTR(range_zip_codes, '[0-9]+', 1, 2),
                       REGEXP_SUBSTR(range_zip_codes, '[0-9]+', 1, 1)
                      ))
                   h,
               fedex_express_zone,
               fedex_ground_zone
          FROM xxpq_zones_calculation) x,
       TABLE(SELECT     COLLECT(LEVEL + l - 1)
                   FROM DUAL
             CONNECT BY LEVEL + l - 1 <= h)
0
 
sdstuberCommented:
pardeshirahul.  

http:#37058604

has a scaling bug in it, that is, as the data volume increases the length of time, cpu, memory, etc needed to process it increases disproportionately.  I'm not entirely sure how bad it is (I haven't let it finish with your data) but it's more than N^2.




I tried http:#37058669 with your table and data, it finished in 50-150 milliseconds.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sdstuberCommented:
pardeshirahul,

Please explain why you chose 37058604
0
 
pardeshirahulAuthor Commented:
what is 37058604  i didnt understand, may be it the query was running for less number of records but when i ran against the main table it is running infinitely
0
 
slightwv (䄆 Netminder) Commented:
>>what is 37058604  i didnt understand

Post with the id:  http:#a37058604  

>>was running for less number of records but when i ran against the main table it is running infinitely

This is what sdstuber was telling you in http:#a37058808

My SQL has a scaling 'bug' that will not work well for more than a few rows.
0
 
sdstuberCommented:
try the solution provided in http:#37058669

as noted above, it completes with in less than a quarter second for the data you presented.
0
 
pardeshirahulAuthor Commented:
when i ran this query
SELECT COLUMN_VALUE range_zip_code, fedex_express_zone, fedex_ground_zone
  FROM (SELECT range_zip_codes,
               TO_NUMBER(REGEXP_SUBSTR(range_zip_codes, '[0-9]+', 1, 1)) l,
               TO_NUMBER(
                   NVL(REGEXP_SUBSTR(range_zip_codes, '[0-9]+', 1, 2),
                       REGEXP_SUBSTR(range_zip_codes, '[0-9]+', 1, 1)
                      ))
                   h,
               fedex_express_zone,
               fedex_ground_zone
          FROM pqinf.xxpq_zones_calculation) x,
       TABLE(SELECT     COLLECT(LEVEL + l - 1)
                   FROM DUAL
             CONNECT BY LEVEL + l - 1 <= h)
the oracle session gets disconnected
0
 
pardeshirahulAuthor Commented:
ORA-03113: end-of-file on communication channel the following error i am getting when ran in toad
0
 
sdstuberCommented:
Then you have an oracle bug.  
I tested that query with your table and your data.

By any chance are you using 10.2.0.2 ?  I was able to replicate the 3113 error in 10.2.0.2.

in 10.2.0.3 and higher it works
0
 
sdstuberCommented:
Something in the parsing of the regular expressions within the subquery is breaking it  (definitely a bug)


However, I was able to get this to run on 10.2.0.2  with your table and data



SELECT COLUMN_VALUE range_zip_code, fedex_express_zone, fedex_ground_zone
  FROM (SELECT range_zip_codes,
               CASE
                   WHEN INSTR(range_zip_codes, '-') = 0 THEN TO_NUMBER(range_zip_codes)
                   ELSE TO_NUMBER(SUBSTR(range_zip_codes, 1, INSTR(range_zip_codes, '-') - 1))
               END
                   l,
               CASE
                   WHEN INSTR(range_zip_codes, '-') = 0 THEN TO_NUMBER(range_zip_codes)
                   ELSE TO_NUMBER(SUBSTR(range_zip_codes, INSTR(range_zip_codes, '-') + 1))
               END
                   h,
               fedex_express_zone,
               fedex_ground_zone
          FROM xxpq_zones_calculation) x,
       TABLE(SELECT     COLLECT(LEVEL + l - 1)
                   FROM DUAL
             CONNECT BY LEVEL + l - 1 <= h);
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 6
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now