sue Tons
asked on
self join
how can I do a self join on the query below, I ha attached the result set for both queries buy I want it to be one. please expert advise?
select
t1.unit_code,
t1.dls_range,
t1.dls_section,
t1.lowdesc_case as lowdesc
from
(
select
unit_code,
dls_range,
dls_section,
case
lowdesc
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 20, 22, 23, 24, 25, 28, 29, 30, 32, 33, 34, 36, NE, NW, SE, SW'
then
'ALL'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, NE, NW, SE, SW'
then
'ALL'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 15'
then
'SW,SE,9.10,11,12,13,15'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, NE, SE, SW'
then
'ALL'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, NE, SE, SW'
then
'ALL'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15'
then
'SE,SW,NW,9,10,15'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15'
then
'SE,NW,SW,9,10,15'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, SE, SW'
then
'SE,SW'
when
'01, 02, 03, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'SE,NE,NW,3,5,6'
when
'02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'SW,NE,NW,2,7,8'
when
'01, 02, 03, 04, 05, 06, 07, 09, 10, 11, 12, 13, 14, 15, 16'
then
'1,2,7,SW,NW,NE'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, NE, NW'
then
'ALL'
when
'00, 01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'ALL'
when
'01, 02, 03, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'SE,3,6,NW,NE'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'ALL'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 13, 15, 16'
then
'SW,SE,NE'
when
'03, 04, 05, 06, 09, 10, 11, 12, 13, 14, 15, 16, SE'
then
'SW, NW,NE'
when
'01, 02, 03, 05, 06, 07, 08, 09, 10, 15, 16'
then
'SE, NE, 3,5,6'
when
'01, 02, 03, 06, 08, 11, 14, 15, 16'
then
'1,2,3,6,8,11,14,15,16'
when
'01, 02, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16 '
then
'SE,NW, NE'
when
'01, 02, 03, 04, 05, 06, 07, 08, 11, 12, 13, 14'
then
'SE, SW, NW'
when
'01, 02, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, SW'
then
'ALL'
when
'00, 09, 10, 11, 12, 13, 14, 15, 16, SE, SW'
then
'ALL'
when
'00, 01, 02, 03, 04, 05, 06, 07, 08, NE, NW'
then
'ALL'
when
'01, 02, 07, 08, 09, 10, 15, 16'
then
'SE,NE'
when
'01, 02, 03, 04, 05, 06, 07, 08'
then
'SE,SW'
when
'01, 02, 07, 08, 09, 10, 15, 16'
then
'SE,NE'
when
'01, 02, 07, 08, 09, 10, 15, 16'
then
'SE,NE'
when
'09, 10, 11, 12, 13, 14, 15, 16'
then
'NE,NW'
when
'09, 10, 11, 12, 13, 14, 15, 16'
then
'NE,NW'
when
'03, 04, 05, 06, 11, 12, 13, 14'
then
'SW,NW'
when
'01, 02, 07, 08, 09, 10'
then
'SE,9,10'
when
'01, 02, 03, 04, 07, 08'
then
'SE,3,4'
when
'01, 02, 03, 04, 05, 06'
then
'1,2,SW'
when
'02, 03, 04, 05, 12'
then
'2,3,4,5,12'
when
'00, NE, NW, SE, SW'
then
'ALL'
when
'NE, SE, SW'
then
'NE SE SW'
when
'NE, NW, SE, SW'
then
'ALL'
when
'01, 02, 07, 08'
then
'SE'
when
'03, 04, 05, 06'
then
'SW'
when
'09, 10, 15, 16'
then
'NE'
when
'11, 12, 13, 14'
then
'NW'
when
'02, 03'
then
'2,3'
when
'00'
then
'ALL'
when
'01, 02, 03, 04'
then
'1,2,3,4'
when
'01, 02, 07, 08, 09, 10'
then
'SE,9,10'
when
'03, 04, 05, 06, 11, 12'
then
'SW,11,12'
when
'09, 10, 13, 14, 15, 16'
then
'13,14,NE'
when
'07, 08, 09, 10, 11, 12, 15, 16'
then
'NE,7,8,11,12'
when
'01, 02, 03, 04, 07, 08, 09, 10'
then
'SE,3,4,9,10'
when
'03, 04, 05, 06, 07, 08, 09, 10, 11, 12'
then
'SW,7,8,9,10,11,12'
when
'11, 12, 13, 14, 15, 16'
then
'NW,15,16'
when
'05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'NW,NE,5,6,7,8'
when
'01, 02, 03, 04'
then
'1,2,3,4'
when
'09, 10, 13, 14, 15, 16'
then
'NE,13,14'
when
'07, 08, 09, 10, 15, 16'
then
'NE,7,8'
when
'05, 06, 11, 12, 13, 14'
then
'NW,5,6'
when
'03, 04, 05, 06, 09, 10, 11, 12, 13, 14, 15, 16'
then
'SW,NE,NW'
when
'01, 02, 03, 04, 05, 06'
then
'SW,1,2'
when
'03, 04, 05, 06, 09, 10, 11, 12, 13, 14, 15, 16'
then
'SW,NW,NE'
when
'07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'NW,NE,7,8'
when
'01, 02, 03, 04, 05, 06, 07, 08'
then
'SW,SE'
when
'07, 09, 10, 11, 12, 13, 14, 15, 16'
then
'NW,NE,7'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 15, 16'
then
'ALL'
when
'11, 12, 13, 14, NE, SE, SW'
then
'ALL'
when
'05, 06, 07, 08, 09, 10'
then
'5,6,7,8,9,10'
when
'09, 10, 11, 12, 13, 14, 15, 16, SE, SW'
then
'NW,NE'
when
'03, 04, 05, 06, 11, 12, 13, 14'
then
'SW,NW'
when
'03, 04, 05, 06, 11, 12'
then
'SW,11,12'
when
'09, 10, 11, 12, SE, SW'
then
'9, 10, 11, 12, SE, SW'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12'
then
'SE,SW,11,12'
when
'01, 02, 04, 05, 06, 08, 11, 14, NE'
then
'1,2,4,5,6,8,11,14,NE'
when
'01, 02, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'SE,NE,NW'
when
'01, 02, 03, 04, 05, 06, 07, 08, 11, 12, 13, 14'
then
'SE,SW,NW'
when
'01, 02, 07, 10, 15, 16, NW, SW'
then
'NW,SW,1,2,7,10,15,16'
when
'01, 02, 04, 05, 06, 08, NE, NW'
then
'1,2,4,5,6,NE,NW'
when
'01, 02, 03, 04, 05, 06, 11, 12'
then
'SW,1,2,11,12'
when
'01, 02, 03, 06, 08, 09, 10, 16'
then
'1,2,4,5,6,8'
when
'03, 05, 06, NE, NW, SE'
then
'NE,NW,SE,3,5,6'
when
'01, 08, 09, 16'
then
'1,8,9,16'
when
'08, 09, 15, 16'
then
'8,9,15,16'
when
'09, 10, 11, SE, SW'
then
'9,10,11'
when
'01, 08, NE'
then
'NE,1,8'
when
'06, NE, SE'
then
'NE,SE,6'
when
'03, 04, 05, 06, 11, 12, 13'
then
'SW,11,12,13'
when
'08, 09, 16'
then
'8,9,16'
when
'02, 03, 04, 05, 06, 07, 10, 11, 12, 13, 14'
then
'SW,NW,2,7,10'
when
'08, 09, 16'
then
'8,9,16'
when
'09, 15, 16'
then
'9,15,16'
when
'01, 08, 09, 10, 15, 16'
then
'1,8,NE'
when
'01, 02, 06, 07, 08, 09, 10, 14, 15, 16'
then
'SE,NE,6,14'
when
'01, 02, 03, 04, 05, 06, 07, 10, 11, 12, 13, 14'
then
'1,2,7,SW,NW,10'
when
'04, 12, 13'
then
'4,12,13'
when
'01, 06, 08, 11, 12, 13, 14, 15, 16'
then
'1,6,8,NW,15,16'
when
'02, 03, 04, 05, 06, 07, 10, 11, 13'
then
'SW,2,7,10,11,13'
when
'04, 05, 06, 10, 11, 12, 13, 14, 15, 16'
then
'4,5,6,11,15,16,NW'
when
'01, 02, 07, 08, 09, 10, 16'
then
'SE,10,16'
when
'01, 08, 15, 16'
then
'1,8,15,16'
when
'01, 02, 07, 08, 09, 10, 11, 15, 16'
then
'SE,NE,11'
when
'01, 02, 07, 08, 09, 10, 11'
then
'SE,9,10,11'
when
'09, 16'
then
'9,16'
when
'05, 10, 15, 16, NW'
then
'5,10,15,16'
when
'03, 06, 11, 13, 14, NE, SE'
then
'3,6,11,13,14'
when
'03, 04'
then
'3,4'
when
'01, 02, 03, 04, 08'
then
'1,2,3,4,8'
when
'09, 15, 16'
then
'9,15,16'
when
'01, 02, 08, 09, 10, 13, 14, 15'
then
'1,2,8,9,10,13,14,15'
when
'03, 04, 05'
then
'3,4,5'
when
'01, 02, 03, 06, 07, 08, 09, 10, 11, 13, 14'
then
'SE,3,6,9,10,11,13,14'
when
'06, 10, 11, 12, 13, 14, 15'
then
'NW,6,10,15'
when
'01, 02, 03, 04, 05, 06, 07, 08, NE, NW'
then
'ALL'
when
'08, 09, 10, 15, 16'
then
'NE,8'
when
'01, 07, 08, 09, 10, 11, 12, 14, 15, 16'
then
'NE,1,7,8,11,12,14'
when
'09, 10, 14, 15, 16'
then
'9,10,14,15,16'
when
'02, 06, 07, 09, 10, 11, 12, 13, 14, 15, 16'
then
'NW,NE,2,6,7'
when
'01, 02, 03, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'SE,NW,NE,5,6'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 15, 16'
then
'SW,SE,NE,11,12'
when
'01, 02'
then
'1,2'
when
'02, 06, 07, 08, 09, 10, 11, 14, 15, 16'
then
'NE,2,6,7,8,11,14'
when
'03, 04, 05, 06, 07, 08, 10, 11, 12, 13, 14'
then
'SW,NW,7,8,10'
when
'02, 03, 04, 05, 06, 07, 10, 11, 12, 13, 14, 15'
then
'SW,NW,2,7,10,15'
when
'01, 02, 07, 08, 09, 10, 14, 15, 16'
then
'SE,NE,14'
when
'01, 02, 03, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14'
then
'SE,NW,3,5,6,9,10'
when
'03, 04, 05, 06, 11, 12, 13'
then
'SW,11,12,13'
when
'02, 03, 05, 06, 07, 11, 12, 13, 14'
then
'NW,2,3,5,6,7'
when
'08, 09, 10, 14, 15, 16'
then
'NE,9,14'
when
'01, 02, 03, 04, 05, 06, 07, 11, 12, 13, 14'
then
'SW,NW,1,2,7'
when
'01, 02, 03, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'SE,NW,NE,3,5,6'
when
'01, 08, 15, 16'
then
'1,8,15,16'
when
'04, 05, 06, 10, 15, NW'
then
'NW,4,5,6,10,15'
when
'02, 07, SW'
then
'SW,2,7'
when
'02, 07, 10, 15, 16, NW, SW'
then
'NW,SW,2,7,10,15,16'
when
'01, 07, 08, 09, 10, 15, 16'
then
'NE,1,7,8'
when
'09, SE'
then
'SE,9'
when
'03, 04, 05, 06, 10, 11, 12, 14, 15, SE'
then
'SW,NW,SE,10,15'
when
'01, 02, 06, 07, 08, NE, NW'
then
'SE,NE, NW,6'
when
'03, 04, 05, 06, 07, 11, 12, 13, 14'
then
'SW,NW,7'
when
'01, 02, 03, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'SE,NE,NW,3,6'
when
'03, 04, 05, 06, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'SW,NE,NW,8'
when
'05, 06, 09, 10, 11, 12, 13, 14, 15, 16'
then
'NE,NW,5,6'
when
'01, 02, 03, 04, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'ALL'
when
'01, 02, 03, 04, 05, 06, 07, 08, 10, 11, 12, 13, 14'
then
'SW,SE,NW,10'
when
'01, 02, 07, 08, 09, 10, 16'
then
'SE,9,10,16'
when
'01, 02, 03, 07'
then
'1,2,3,7'
when
'01, 02, 04'
then
'1,2,4'
when
'09, 14, 15, 16'
then
'9,14,15,16'
when
'03, 04, 11, 12, 14, SW'
then
'SW,3,4,11,12,14'
when
'01, 02, 03, 08, 09'
then
'1,2,3,8,9'
when
'07, 08, 10, 15, NW'
then
'NW,7,8,10,15'
when
'02, 03, 04, 05, 06'
then
'SW,2'
when
'05, 12'
then
'5,12'
when
'03, 04, 05, 06, 09, 10, 11, 12, 13, 16, SE'
then
'SW,9,10,11,12,13,16'
when
'01, 02, 07, 08, 09'
then
'1,2,7,8,9'
when
'09, 10, 14, 15, 16'
then
'9,10,14,15,16'
when
'02, 03, 04, 05, 06, 07, 08, 09, 10, 15, 16'
then
'SW,NE,2,7,8'
when
'01, 08'
then
'1,8'
when
'04, 05, 12, 13'
then
'4,5,12,13'
when
'01, 02, 03, 04, 05, 06, 07, 08, 10'
then
'SE,SW,10'
when
'05, 06, 12'
then
'5,6,12'
when
'03, 04, 05, 06, 15, 16, NW'
then
'SW,NW,15,16'
when
'03, 06, 11, 14, NE, SE'
then
'NE,SE,3,6,11,14'
when
'03, 04, 06, 09, SE'
then
'SE,3,4,6,9'
when
'04, 05'
then
'4,5'
when
'04, 12, 13'
then
'4,12,13'
when
'05, 15, 16, NW'
then
'NW,5,15,16'
when
'03, 06, 09, 10, 16, SE'
then
'SE,3,6,9,10,16'
when
'03, 05, 06, SE'
then
'SE,3,5,6'
when
'03, 06, SE'
then
'SE,3,6'
when
'09, 10, 14, 15, 16'
then
'9,10,14,15,16'
when
'05, 06, 07, 10, NW'
then
'NW,5,6,7,10'
when
'03, 04'
then
'3,4'
when
'01, 08, 09, 16'
then
'1,8,9,16'
when
'01, 08, 09'
then
'1,8,9'
when
'03, 04'
then
'3,4'
when
'03, 04, 05, 06, 12, SE'
then
'SW,SE,12'
when
'09, 10, 11, SE, SW'
then
'SE,SW,9,10,11'
when
'01, 02, 06, 07, 08, NE, NW'
then
'NE,NW,1,2,6,7,8'
when
'02, 07, 08, 10, 11, 12, 13, 14, 15, SW'
then
'SW,NW,2,7,8,10,15'
when
'01, 02, 07, 11, 12, SW'
then
'SW,1,2,7,11,12'
when
'01, 08'
then
'1,8'
when
'03, 06, 11, 14, NE, SE'
then
'NE,SE,3,6,11,14'
when
'03, 04, 05, 06, 11, 12, 13, 14, NE, SE'
then
'ALL'
when
'04, 05, 12, 13'
then
'4,5,12,13'
when
'09, 16, SE'
then
'9,16,SE'
when
'01, 02, 03, 06, 07, 08, 09'
then
'SE,3,6,9'
when
'01, 02, 03, 04, 05, 06, 07, 08, 11, 12'
then
'SE,SW,11,12'
when
'03, 04, 05, 06, NE, NW, SE'
then
'ALL'
when
'04, 05, 12'
then
'4,5,12'
when
'01, 02, SW'
then
'1,2,SW'
when
'02, 07, 08, 09, 10, 15, NW, SW'
then
'NW,SW,2,7,8,9,10,15'
when
'07, 08, NE, NW, SW'
then
'7,8, NE, NW, SW'
when
'01, 07, 08, NE, NW'
then
'NE,NW,1,7,8'
when
'09, 10, 11, SE, SW'
then
'SE,SW,9,10,11'
when
'07, 10, 15, 16, NW, SW'
then
'NW,SW,7,10,15,16'
when
'06, 11, 14, NE, SE'
then
'SE,NE,6,11,14'
when
'03, 06, 11, 14, NE, SE'
then
'NE,SE,3,6,11,14'
when
'02, 03, 04'
then
'2,3,4'
when
'01, 03, 04'
then
'1,3,4'
when
'01, 02, 03'
then
'1,2,3'
when
'03, 06, 09, 10, SE'
then
'3,6,9,10,SE'
when
'02, 07, 11, 12, 13, SW'
then
'2,7,11,12,13,SW'
when
'07, 08, 11, 13, 14, NE'
then
'NE,7,8,11,12,14'
when
'05, 06, 07, 10, 11, 12, 13, 14, 15'
then
'NW,5,6,7,10,15'
when
'03, 04, 05, 06, 07, 10, 11, 12, 13, 14'
then
'SW,NW,7,10'
when
'01, 02, 03, 04, 05, 06, 07, 08, NE, NW'
then
'ALL'
when
'01, 08, 09, 15, 16'
then
'1,8,9,15,16'
when
'01, 02, 07, 10, 15, NW, SW'
then
'NE,SW,1,2,7,10,15'
when
'04, 05, 12, 13'
then
'4,5,12,13'
when
'05, 12'
then
'5,12'
when
'05, 12, 13'
then
'5,12,13'
when
'08, 09, 10, 15, 16'
then
'8,NE'
when
'06, 11, 14, NE, SE'
then
'6,11,14,NE,SE'
when
'03, 04, 05'
then
'3,4,5'
when
'09, 10, 15, NW, SE, SW'
then
'9,10,15,NW,SW,SE'
when
'03, 06, 11, 13, 14, NE, SE'
then
'NE,SE,3,6,11,13,14'
when
'01, 02, 03'
then
'1,2,3'
when
'09, 15, 16'
then
'9,15,16'
when
'05, 06, 07, 08, NE, NW'
then
'NE,NW,5,6,7,8'
when
'01, 02, 07, SW'
then
'1,2,7,SW'
when
'09, 16'
then
'9,16'
when
'09, 10, 11, 14, 15'
then
'9,10,11,14,15'
when
'01, 02, 07, 08, NE, NW, SW'
then
'ALL'
when
'03, 04'
then
'3,4'
when
'01, 05, 06, 07, 08, NE, NW'
then
'NE,NW,1,5,6,7,8'
when
'01, 02, 03, 04, 07, 08, 09'
then
'SE,3,4,9'
when
'03, 05, 11, 12, SW'
then
'3,5,11,12,SW'
when
'09, 10, 15, 16, NW, SE, SW'
then
'ALL'
when
'01, 02, 07, 08, NW, SW'
then
'SE,NW,SW'
when
'07, 10, 15, NW, SW'
then
'NW,SW,7,10,15'
when
'05, 06, 07, NE, NW'
then
'NE,NW,5,6,7'
when
'06, 07, 08, 11, 14, NE'
then
'NE,6,7,8,11,14'
when
'03, NE, SE'
then
'NE,SE,3'
when
'09, 10, 15, 16, SE'
then
'SE,9,15,15,16'
when
'01, 02, 05, 06, 07, 08, 09, 10, 11, 12, 15, 16'
then
'SE,NE,5,6,11,12'
when
'09, 10, 11, 12'
then
'9,10,11,12'
when
'03, 04, 05, 06, 07, NW'
then
'NW,SW,7'
when
'03, 06, 09, 10, 11, SE'
then
'3,6,9,10,11'
when
'01, 02, 03, 04, 05, 06, 07, 08, 11, 12, 13, 14, NE'
then
'ALL'
when
'02, 07, 10, 15, NW, SW'
then
'NW,SW,2,7,10,15'
when
'03, 04, 06, SE'
then
'SE,3,4,6'
when
'05, 06, NW'
then
'5,6,NW'
when
'01, 02, NW, SW'
then
'NW,SW,1,2'
when
'01, 02, 03, 07, 08, 09, 10, 15, 16'
then
'SE,NE,3'
when
'03, 04, 05, 06, 11, 12, 13, 14, 15, 16'
then
'SW,NW,15,16'
when
'09, 10, NW, SE, SW'
then
'NW,SE,SW,9,10'
when
'03, 04, 05, 06, NW'
then
'NW,SW'
when
'01, 02, 03, 04, 05, 06, 07, 08, 11, 12, 13, 14, NE'
then
'ALL'
when
'01, NE, NW, SE, SW'
then
'ALL'
when
'07, 08, NE'
then
'NE,7,8'
when
'03, 04, 09, 10, SE'
then
'SE,3,4,9,10'
when
'05, 06, NE, NW, SE'
then
'NE,NW,SE,5,6'
when
'07, 08, 09, 10, 11, 12, SW'
then
'SW,7, 8, 9, 10, 11, 12'
when
'01, 02, 08, 09'
then
'1,2,8,9'
when
'05, 06, NE, SE'
then
'NE,SE,5,6'
when
'05, 06, NW'
then
'NW,5,6'
when
'03, 04, 05, 06, 13, 14'
then
'SW,13,14'
when
'01, 02, 03, 07, 08, 09, 15, 16'
then
'SE,3,9,15,16'
when
'05, 06, 13, 14'
then
'5,6,13,14'
when
'01, 02, 09, 10'
then
'1,2,9,10'
when
'05, 06, NE, NW, SE'
then
'NE,NW,SE,5,6'
when
'01, 02, 05, 06, 11, 12, 13, 14, 15, 16'
then
'NW,1,2,5,6'
when
'05, 06, 13, 14'
then
'5,6,13,14'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 13, 14'
then
'SW,SE,9,10,13,14'
when
'01, 02, 03, 04, 05, 06, 11, 12, 13, 14'
then
'SW,NW,1,2'
when
'03, 04, 11, 12, 13, 14'
then
'NW,3,4'
when
'05, 06, 11, 12, 13, 14, 15, 16'
then
'NW,5,6,15,16'
when
'01, 02, 03, 04, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'NW,SE,NE,3,4'
when
'05, 06, 11, 12, 13, 14, 15, 16'
then
'NW,5,6,15,16'
when
'09, 10'
then
'9,10'
when
'01, 02, 03, 04, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'SE,NW,NE,3,4'
when
'03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 15, 16'
then
'SW,NE,7,8,11,12'
when
'01, 02, 03, 04, 07, 08, 15, 16'
then
'SE,3,4,15,16'
when
'01, 02, 07, 08, 13, 14, 15, 16'
then
'SE,13,14,15,16'
when
'05, 06, 13, 14'
then
'5,6,13,14'
when
'03, 04, 05, 06, 13, 14'
then
'SW,13,14'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10'
then
'SW,SE,9,10'
when
'03, 04, 05, 06, 09, 10, 15, 16'
then
'SW,NE'
when
'03, 04, 07, 08, 15, 16'
then
'3,4,7,8,15,16'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 13, 14, 15, 16'
then
'SW,SE,NE,13,14'
when
'03, 04, 05, 06, NW, SE'
then
'NW,SE,SW'
when
'09, 10, 15, 16, SE'
then
'SE,NE'
when
'09, 10, 15, 16, NW, SE, SW'
then
'ALL'
when
'01, 02, 03, 04, 05, 06, 07, 08, 11, 12, NE'
then
'SE,3,4,11,12'
when
'09, 10, SE'
then
'SE,9,10'
when
'05, 06, 07, 08, 09, 10, 11, 12, 15, 16'
then
'NE,5,6,7,8,11,12'
when
'05, 06, 11, 12'
then
'5,6,11,12'
when
'05, 06, 11, 12, 13, 14, 15, 16'
then
'NW,5,6,15,16'
when
'05, 06, 11, 12'
then
'5,6,11,12'
when
'01, 02, 03, 04, 05, 06, NW'
then
'NE.SW,1,2'
when
'05, 06, 07, 08, 11, 12, 13, 14'
then
'NW,5,6,7,8'
when
'03, 04, 05, 06, NW'
then
'NW,SW'
when
'09, 10, 15, 16, SE, SW'
then
'NE,SW,SE'
when
'01, 02, 07, 08, NW, SW'
then
'NE,SW,SE'
when
'07, 08, NE'
then
'NE,7,8'
when
'05, 06'
then
'5,6'
when
'03, 04, 11, 12, NE, SE'
then
'NE,SE,3,4,11,12'
when
'02, 09, 10, 12, NW, SE, SW'
then
'NW,SE,SW,9,10'
when
'09, 10, SE, SW'
then
'SE,SW,9,10'
when
'01, 02, 07, 08, 09, 10, 15, 16, NW, SW'
then
'ALL'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, NW'
then
'NW,SW,SE,9,10'
when
'09, 10, 15, 16, NW'
then
'NE,NW'
when
'05, 06, 11, 12, 13, 14, 15, 16'
then
'NW,5,6,15,16'
when
'04, 05, 06, 11, 12, 13, 14'
then
'NW,4,5,6'
when
'03, 04, 12, 13'
then
'3,4,12,13'
when
'03, 04, 05, 06, 10'
then
'3,4,5,6,10'
when
'01, 02, 03, 04, 05, 06, 07, 08, 11, 12, 13, 14, 15'
then
'NW,SE,SW,15'
when
'02, 03, 04, 05, 06, 09, 10, 11, 12, 13, 14'
then
'SW,NW,2,15'
when
'01, 02, 03, 08, 09, 10, 16'
then
'1,2,3,8,9,10,16'
when
'01, 07, 08, 09, 10, 16'
then
'1,7,8,9,10,16'
when
'01, 06, 07, 08, 09, 10, 11, 13, 14, 15, 16'
then
'NE,1,6,7,8,11,13,14'
when
'01, 02, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'NE,NE,SE,6'
when
'01, 02, 03, 04, 05, 06, 07, 08, 12, 13'
then
'SE,SW,12,13'
when
'01, 02, 03, 04, 05, 06, 11, 12, 13, 14'
then
'SW,NW,1,2'
when
'03, 04, 09, 10, 11, 12, 13, 14, 15, 16'
then
'NE,NE,3,4'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10'
then
'SW,SE,9,10'
when
'01, 02, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'SE,NW,NE,5,6'
when
'07, 08, 09, 10, 13, 14, 15, 16'
then
'NE,7,8,13,14'
when
'09, 10, 11, 12'
then
'9,10,11,12'
when
'09, 10, 15, 16, NW, SE, SW'
then
'NW, SE, SW, 9, 10, 15, 16'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 15, 16, NW'
then
'ALL'
when
'03, 04, 05, 06, 13, 14, NE, NW, SE'
then
'ALL'
when
'03, 04, 05, 13'
then
'3,4,5,13'
when
'03, 04, 05, 06, 09, 10, 13, 14, 15, 16, NW, SE'
then
'ALL'
when
'07, 08, NE, NW'
then
'NE,NW.7,8'
when
'03, 04, 05, 06, 07, 08, NE, NW'
then
'SW,NE,NW,7,8'
when
'01, 02, 05, 06, 07, 08'
then
'SE,5,6'
when
'09, 10, 15, 16, NW'
then
'NE,NW'
when
'01, 02, NW, SW'
then
'NW,SW,1,2'
when
'05, 06, NE, NW, SE'
then
'NE,SW,NW,5,6'
when
'03, 04, 05, 06, 11, 12, 13, 14, 15, 16'
then
'SW,NW,15,16'
when
'01, 02, 05, 06, 07, 08, 09, 10, 15, 16'
then
'SE,NE,5,6'
when
'07, 08'
then
'7,8'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 15, 16, NW'
then
'ALL'
when
'01, 02, 07, 08, 09, 10, NW, SW'
then
'NW,SW,SE,9,10'
when
'07, 08, 09, 10, 15, 16, NW, SW'
then
'NW,NE,SW,7,8'
when
'03, 04, 05, 06, 07, 08, NE'
then
'NE,SW,7,8'
when
'07, 08, 09, 10'
then
'7,8,9,10'
when
'09, 10, NW, SE, SW'
then
'NW,SE,SW,9,10'
when
'07, 08, NE, NW'
then
'NE,NW,7,8'
when
'05, 06, 15, 16, NE, NW, SE'
then
'NE,NW,SE,5,6'
when
'01, 02, 09, 10, 11, 12, 13, 14, 15, 16, SW'
then
'SW,NW,NE,1,2'
when
'09, 10, NW, SE, SW'
then
'NW,SE,SW,9,10'
when
'09, 10, 15, 16, NW, SE, SW'
then
'ALL'
when
'05, 06, NE, NW, SE'
then
'NE,NW,SE,5,6'
when
'01, 02, 07, 08, 13, 14, 15, 16'
then
'SE,13,14,15,16'
when
'01, 02, 07, 08, 09, 10, 15, 16, NW, SW'
then
'ALL'
when
'01, 02, 07, 08, 09, 10, SW'
then
'SE,SW,9,10'
when
'09, 10, 15, 16, NW, SE, SW'
then
'ALL'
when
'03, 04, 05, 06, NW, SE'
then
'NW,SE,SW'
when
'03, 04, SE'
then
'SE,3,4'
when
'01, 02, 07, 08, 09, 10, SW'
then
'SE,SW,9,10'
when
'09, 10, 15, 16, NW, SE, SW'
then
'ALL'
when
'09, 10, 15, 16, NW'
then
'NW,NE'
when
'01, 02, 07, 08, 11, 12, 13, 14, NE, SW'
then
'ALL'
when
'03, 04, 05, 06, NW, SE'
then
'SW,NW,SE'
when
'05, 06, 13, 14'
then
'5,6,13,14'
when
'05, 06, 11, 12, 13, 14, 15, 16'
then
'NW,5,6,15,16'
when
'03, 04, 05, 06, 09, 10, 11, 12, 15, 16, SE'
then
'SW,NE,11,12'
when
'01, 02, 07, 08, 11, 12, 13, 14, NE, SW'
then
'ALL'
when
'09, 10, 15, 16, NW, SE, SW'
then
'ALL'
when
'03, 04, 05, 06, SE'
then
'SW,SE'
when
'01, 02, 08'
then
'1,2,8'
when
'03, 04, 05, 06, 09, 10, NW, SE'
then
'SW,NW,SE,9,10'
when
'01, 02, 09, 10, 15, 16'
then
'1,2,NE'
when
'03, 04, 05, 06, 07, 08, 11, 12, 13, 14'
then
'SW,NE,7,8'
when
'01, 02, 07, 08, 09, 10, 11, 12, 15, 16'
then
'SE,NE,11,12'
when
'03, 04, 05, 06, 13, 14'
then
'SW,13,14'
when
'05, 06, 13, 14'
then
'5,6,13,14'
when
'05, 06, 11, 12, 13, 14, 15, 16'
then
'NW,5,7,15,16'
when
'09, 10, 11, 12'
then
'9,10,11,12'
when
'09, 10, 15, 16, NW, SE, SW'
then
'ALL'
when
'01, 08, 16'
then
'1,8,16'
when
'09, 10, SE, SW'
then
'SE,SW,9,10'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 11, 12, 13, 14, 15, 16'
then
'SE,SW,NW,9,15,16'
when
'01, 03, 04, 05, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'NW,NE,1,3,4,5,7,8'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, NE, SE'
then
'ALL'
when
'01, 02, 03, 04, 05, 06, 07, 09, 10, 11, 12, 13, 14, 15'
then
'SW,NW,1,2,7,15'
when
'01, 05, 06, 07, 08, 09, 10, 11, 13, 14, 15, 16'
then
'NE,7,5,6,7,8,11,13,14'
when
'01, 02, 03, 04, 05, 06, 07, 08, 11, 12, 13, 14, 15, 16'
then
'SE,SW,NW,15,16'
when
'01, 02, 03, 04, 05, 06, 07, 08, 11, 12, 13, 14, 15, 16'
then
'SE,SW,NW,15,16'
when
'01, 02, 03, 04, 05, 06, 07, 08, 11, 12, 13, 14, 15, 16'
then
'SW,SE,NW,15,16'
when
'01, 02, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'SE,NW,NE,5,6'
when
'01, 02, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'SE,NW,NE,5,6'
when
'01, 02, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'SE,NW,NE,5,6'
when
'01, 02, 03, 04, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'SE,NW,NE,3,4'
when
'01, 02, 03, 04, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'SE,NW,NE,3,4'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14'
then
'SE,SW,NW,9,10'
when
'03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'SW,NW,NE,7,8'
when
'01, 02, 03, 04, 05, 06, 07, 08, 11, 12, 15, 16'
then
'SE,SW,11,12,15,16'
when
'01, 02, 05, 06, 07, 08, 09, 10, 11, 12, 15, 16'
then
'SE,NE,5,6,11,12'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12'
then
'SW,SE,9,10,11,12'
when
'01, 02, 03, 04, 07, 08, 09, 10, 13, 14, 15, 16'
then
'SE,NE,3,4,13,14'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 13, 14, 15, 16'
then
'ALL'
when
'01, 02, 05, 06, 09, 10, 11, 12, 13, 14, 15, 16'
then
'NW,NE,1,2,5,6'
when
'05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'5,6,7,8,NE,NW'
when
'05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'5,6,7,8,NW,NE'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11'
then
'SW,SE,9,10,11'
when
'01, 02, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16'
then
'SE,NW,NE'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 15, 16'
then
'SW,SE,NE'
when
'03, 04, 05, 06, 07, 08, 09, 10, 12, 15, 16'
then
'SW,NE,7,8,12'
when
'05, 06, 07, 09, 10, 11, 12, 13, 14, 15, 16'
then
'NW,NE,5,6,7'
when
'03, 04, 05, 06, 11, 12, 13, 14, 15, 16'
then
'SW,NW,15,16'
when
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10'
then
'SE,SW,9,10'
when
'03, 04, 05, 06, 11, 12, 13, 14, 15, 16'
then
'SW,NW,15,16'
when
'02, 03, 04, 05, 06, 07, 08, 11, 12'
then
'SW,2,7,8,11,12'
when
'01, 02, 03, 04, 07, 08, 09, 10, 15, 16'
then
'SE, NE,3,4'
when
'01, 02, 05, 06, 07, 08, 09, 10, 15, 16'
then
'SE,NE,5'
when
'03, 04, 05, 06, 07, 08, 11, 12'
then
'SW,7,8,11,12'
when
'01, 02, 07, 08, 09, 10, 11, 12'
then
'SE,9,10,11,12'
when
'03, 04, 05, 06, 09, 10, NW, SE'
then
'SW,NW,SE,9,10'
when
'03, 04, 05, 06, 11, 12, NE, SE'
then
'SW,11,12'
when
'05, 06, 07, 08, 09, 10, 15, 16'
then
'NE,5,6,7,8'
when
'01, 02, 07, 08, 09, 10, SW'
then
'SW,SE,9,10'
when
'01, 02, 03, 04, 09, 10'
then
'1,2,3,4,9,10'
when
'01, 02, 03, 04, 05, 07, 08'
then
'SE,3,4,5'
when
'02, 03, 06, 07, 08, 09, 10'
then
'SE,3,6,9,10'
when
'03, 04, 05, 06, 11, 12, 13, 14'
then
'SW, NW'
when
'07, 08, 09, 10, 11, 12'
then
'7,8,9,10,11,12'
when
'01, 02, 03, 04, 06, 07, 08'
then
'SE,3,4,6'
when
'09, 10, 15, 16, NW, SE'
then
'NW,NE,SE'
when
'01, 02, 07, 08, NW, SW'
then
'NW,SW,SE'
when
'05, 06, NE, NW, SE'
then
'5,6,NE,NW,SE'
when
'09, 10, 11, 14, 15,16'
then
'NE,11,14'
when
'02, 03, 04, 07, 08'
then
'2,3,4,7,8'
when
'06, NE, NW, SE'
then
'NE,NW,SE,6'
when
'05, 06, 11, 12'
then
'5,6,11,12'
when
'05, 06, NE, NW'
then
'NE,NW,5,6'
when
'09, 10, SE'
then
'SE,9,10'
when
'03, 04, SE'
then
'SE,3,4'
when
'04, 05, NW'
then
'4,5, NW'
when
'03, 04, SE'
then
'SE,3,4'
when
'00, NE, NW'
then
'ALL'
when
'00, NW, SW'
then
'ALL'
when
'05, NW'
then
'5,NW'
when
'07, 08'
then
'7,8'
when
'09, 10'
then
'9,10'
when
'07, 08'
then
'7,8'
when
'00, SW'
then
'ALL'
when
'00, SE'
then
'ALL'
when
'09'
then
'9'
when
'02'
then
'2'
when
'08'
then
'8'
when
'04'
then
'4'
when
'01'
then
'1'
else
lowdesc
end
as lowdesc_case
from
(
select distinct
unit_code,
dls_range,
dls_section,
listagg(comp_loc_substr, ', ') within group (
order by
dls_section, comp_loc_substr, unit_code, dls_range) as lowdesc
from
(
select distinct
unit_code,
substr(computed_location_description, 1, 2) comp_loc_substr,
dls_range,
dls_section
from
(
select
'1' set_seq_no,
'WI'as type,
'WI' || substr(stream_id, 7, 5) as set_id,
substr(stream_id, 1, 2) as province,
substr(stream_id, 7, 5) as unit_code,
unit_short_name as unit_name,
effective_date,
lpad(computed_location_description, 14, '00-') as computed_location_description,
'D' as dlsornts,
ba_name as client_name,
regexp_substr ( ba_name, '[A-z]*' ) client_abbrev,
'SIR' as ppdm_source,
substr(computed_location_description, 14, 1) as dls_meridian,
substr(computed_location_description, 11, 2) as dls_range,
substr(computed_location_description, 8, 2) as dls_township,
substr(computed_location_description, 4, 2) as dls_section,
last_boundary_change,
substr( unit_short_name, 1, replace (instr (unit_short_name, ' ') - 1, - 1, length (unit_short_name))) || ' ' || substr( strat_nuint_name, 1, replace (instr ( strat_nuint_name, ' ') - 1, - 1, length (strat_nuint_name))) as pool_name,
ba_id as ba_code,
to_number(regexp_substr(unit_short_name, '\d+$')) as unit_no,
unit_type as substance_desc,
tract_status,
case
when
tract_status = 'Active'
then
'Y'
else
'N'
end
active_ind , operator_date , ''revisiondate , ''areacode , ''formationcode , ''poolseq , ''operatorid , ''updatedate , ''interestdate1 , ''interestdate2 , ''interestdate3 , ''interestdate4 , ''interestdate5 , ''terminated , ''field , ''unitized_zone , ''pressure_maintenance , ''nts_primary_quad , ''nts_letter_quad , ''nts_sixteenth , ''nts_block
from
sk_units_raw_data
)
)
group by
unit_code, dls_range, dls_section
)
) t1 ;
70000 2 00 ALL
70000 09 03 ALL
70000 09 05 ALL
70000 09 07 ALL
70000 09 08 ALL
70000 09 09 SE,SW
70000 09 19 ALL
70000 09 21 ALL
70000 09 31 ALL
70000 09 33 NW,NE
70000 10 01 ALL
70000 10 03 NW,NE
select
t2.set_seq_no,
t2.set_seq_no,
t2.type,
t2.set_id,
t2.unit_code,
t2.province,
t2.unit_name,
t2.effective_date,
t2.dlsornts,
t2.client_name,
t2.client_abbrev,
t2.computed_location_description,
t2.ppdm_source ,
t2.unit_no,
t2.dls_meridian,
t2.dls_range,
t2.dls_township,
t2.dls_section
,t2.ba_code,
t2.operator_date,
t2.last_boundary_change,
t2.revisiondate,
t2.areacode,
t2.formationcode,
t2.poolseq,
t2.operatorid,
t2.updatedate,
t2.interestdate1,
t2.interestdate2,
t2.interestdate3,
t2.interestdate4,
t2.interestdate5,
t2.terminated,
t2.field,
t2.unitized_zone,
t2.pressure_maintenance,
t2.nts_primary_quad,
t2.nts_letter_quad,
t2.nts_sixteenth,
t2.nts_block
from
(
select
'1' set_seq_no,
'WI'as type,
'WI' || substr(stream_id, 7, 5) as set_id,
substr(stream_id, 1, 2) as province,
substr(stream_id, 7, 5) as unit_code,
unit_short_name as unit_name,
effective_date,
lpad(computed_location_description, 14, '00-') as computed_location_description,
'D' as dlsornts,
ba_name as client_name,
regexp_substr ( ba_name, '[A-z]*' ) client_abbrev,
'SIR' as ppdm_source,
substr(computed_location_description, 14, 1) as dls_meridian,
substr(computed_location_description, 11, 2) as dls_range,
substr(computed_location_description, 8, 2) as dls_township,
substr(computed_location_description, 4, 2) as dls_section,
last_boundary_change,
substr( unit_short_name, 1, replace (instr (unit_short_name, ' ') - 1, - 1, length (unit_short_name))) || ' ' || substr( strat_nuint_name, 1, replace (instr ( strat_nuint_name, ' ') - 1, - 1, length (strat_nuint_name))) as pool_name,
ba_id as ba_code,
to_number(regexp_substr(unit_short_name, '\d+$')) as unit_no,
unit_type as substance_desc,
tract_status,
case
when
tract_status = 'Active'
then
'Y'
else
'N'
end
active_ind , operator_date , ''revisiondate , ''areacode , ''formationcode , ''poolseq , ''operatorid , ''updatedate , ''interestdate1 , ''interestdate2 , ''interestdate3 , ''interestdate4 , ''interestdate5 , ''terminated , ''field , ''unitized_zone , ''pressure_maintenance , ''nts_primary_quad , ''nts_letter_quad , ''nts_sixteenth , ''nts_block
from
sk_units_raw_data
)
t2;1 1 WI WI70000 70000 SK VIEWFIELD BAKKEN V U 4 01-MAY-17 D CRESCENT POINT RESOURCES PARTNERSHIP CRESCENT 15-01-008-10W2 SIR 4 2 10 08 01 12828 01-MAY-17 04-MAY-17
1 1 WI WI70000 70000 SK VIEWFIELD BAKKEN V U 4 01-MAY-17 D CRESCENT POINT RESOURCES PARTNERSHIP CRESCENT 16-01-008-10W2 SIR 4 2 10 08 01 12828 01-MAY-17 04-MAY-17
1 1 WI WI70000 70000 SK VIEWFIELD BAKKEN V U 4 01-MAY-17 D CRESCENT POINT RESOURCES PARTNERSHIP CRESCENT 00-02-008-10W2 SIR 4 2 10 00 12828 01-MAY-17 04-MAY-17
1 1 WI WI70000 70000 SK VIEWFIELD BAKKEN V U 4 01-MAY-17 D CRESCENT POINT RESOURCES PARTNERSHIP CRESCENT 04-21-007-09W2 SIR 4 2 09 07 21 12828 01-MAY-17 04-MAY-17
1 1 WI WI70000 70000 SK VIEWFIELD BAKKEN V U 4 01-MAY-17 D CRESCENT POINT RESOURCES PARTNERSHIP CRESCENT 05-21-007-09W2 SIR 4 2 09 07 21 12828 01-MAY-17 04-MAY-17
1 1 WI WI70000 70000 SK VIEWFIELD BAKKEN V U 4 01-MAY-17 D CRESCENT POINT RESOURCES PARTNERSHIP CRESCENT 12-21-007-09W2 SIR 4 2 09 07 21 12828 01-MAY-17 04-MAY-17
1 1 WI WI70000 70000 SK VIEWFIELD BAKKEN V U 4 01-MAY-17 D CRESCENT POINT RESOURCES PARTNERSHIP CRESCENT 13-21-007-09W2 SIR 4 2 09 07 21 12828 01-MAY-17 04-MAY-17
1 1 WI WI70000 70000 SK VIEWFIELD BAKKEN V U 4 01-MAY-17 D CRESCENT POINT RESOURCES PARTNERSHIP CRESCENT SE-03-008-10W2 SIR 4 2 10 08 03 12828 01-MAY-17 04-MAY-17
1 1 WI WI70000 70000 SK VIEWFIELD BAKKEN V U 4 01-MAY-17 D CRESCENT POINT RESOURCES PARTNERSHIP CRESCENT SW-03-008-10W2 SIR 4 2 10 08 03 12828 01-MAY-17 04-MAY-17
1 1 WI WI70000 70000 SK VIEWFIELD BAKKEN V U 4 01-MAY-17 D CRESCENT POINT RESOURCES PARTNERSHIP CRESCENT 09-03-008-10W2 SIR 4 2 10 08 03 12828 01-MAY-17 04-MAY-17
1 1 WI WI70000 70000 SK VIEWFIELD BAKKEN V U 4 01-MAY-17 D CRESCENT POINT RESOURCES PARTNERSHIP CRESCENT 10-03-008-10W2 SIR 4 2 10 08 03 12828 01-MAY-17 04-MAY-17ASKER
I just gave you randomly but they should be the same, every time i tried to join like you did yesterday, its comes back with this error
ORA-00918: column ambiguously defined
00918. 00000 - "column ambiguously defined"
The computed column is not required since I only wanted it to calculate part of the the lowdesc, so I can remove that columns. But how can I join without this ambiguous columns?
ORA-00918: column ambiguously defined
00918. 00000 - "column ambiguously defined"
The computed column is not required since I only wanted it to calculate part of the the lowdesc, so I can remove that columns. But how can I join without this ambiguous columns?
>>ORA-00918: column ambiguously defined
This means you are using a column that appears in both queries and aren't telling Oracle which one to use.
For example:
I'm selecting dummy but that exists in d1 and d2 so Oracle doesn't know which one to return.
You need to tell it:
As before, if you want us to post tested SQL, we need complete setups to build and test with.
This means you are using a column that appears in both queries and aren't telling Oracle which one to use.
For example:
select dummy from dual d1 join dual d2 on d1.dummy=d2.dummy
*
ERROR at line 1:
ORA-00918: column ambiguously definedI'm selecting dummy but that exists in d1 and d2 so Oracle doesn't know which one to return.
You need to tell it:
SQL> select d1.dummy from dual d1 join dual d2 on d1.dummy=d2.dummy;
D
-
XAs before, if you want us to post tested SQL, we need complete setups to build and test with.
ASKER
Its good now, I was able to fix the error.
wow, what a query ...
i'd use a table with a when and a then column ...
and add an index on the when
and then you can just join ...
i'd use a table with a when and a then column ...
create table lowdescv (lowdesc_when varchar2(500), lowdesc_then varchar2(100));
insert into lowdescv (lowdesc_when, lowdesc_then) values (
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 20, 22, 23, 24, 25, 28, 29, 30, 32, 33, 34, 36, NE, NW, SE, SW',
'ALL');
insert into lowdescv (lowdesc_when, lowdesc_then) values (
'01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, NE, NW, SE, SW',
'ALL');and add an index on the when
and then you can just join ...
This question needs an answer!
Become an EE member today
7 DAY FREE TRIALMembers can start a 7-Day Free trial then enjoy unlimited access to the platform.
View membership options
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
I suggested the self-join to pull in the additional columns. What that does is give you the CSV values for EVERY ROW when you do the join.
If you want a single row returned, you don't need the self-join. Just GROUP BY the additional columns.
If more than one row comes back for a single UNIT_CODE it means there is more than one distinct value for the additional columns.
In your second select the computed_location_descript