asked on

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;

----------------------------

slightwv (䄆 Netminder)

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

ASKER CERTIFIED SOLUTION

Sean Stuber

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Sean Stuber

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.

Sean Stuber

pardeshirahul,

Please explain why you chose 37058604

pardeshirahul

ASKER

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

slightwv (䄆 Netminder)

>>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.

Sean Stuber

try the solution provided in http:#37058669

as noted above, it completes with in less than a quarter second for the data you presented.

pardeshirahul

ASKER

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

pardeshirahul

ASKER

ORA-03113: end-of-file on communication channel the following error i am getting when ran in toad

Sean Stuber

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

Sean Stuber

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);