GRIMS Insert Atributs

From ICISWiki

Jump to: navigation, search

GRIMS-ICIS


Create Passport Atributs

SQL statement that creates an output file of the sql script that inserts the data into Local ATRIBUTS table. 


spool upload_passport.sql
select 
'-- ' || column_name || '
        insert into atributs
	select atributs_seq.nextval,
		a.gid, 
		' || GET_ATYPE(COLUMN_NAME) || '
		-1,
		a.' || lower(COLUMN_NAME) || ',
		NVL(B.CNTRYID,0),
		0,
		TO_NUMBER(TO_CHAR(SYSDATE,''YYYYMMDD''))
	from passport1 a, iris_central.cntry b
		where a.ori_Country=b.isothree(+)
	and a.' || column_name || ' is not null
;
'
from all_tab_columns
where table_name like upper('passport1')
	  and owner like upper('gbuser')
order by column_name;

spool off;

@upload_passport.sql;

exit;


Content of upload_passport.sql 


--ACQ_DATE
	INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -2, -1, TO_CHAR(a.acq_date,'YYYYMMDD'), NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.ACQ_DATE is not null
;


--COLL_date
	INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, 1136, -1, 	nvl(a.coll_year,'00') || 
							decode(a.coll_month,'JAN','01',
							  'FEB','02',
							  'MAR','03',
							  'APR','04',
							  'MAY','05',
							  'JUN','06',
							  'JUL','07',
							  'AUG','08',
							  'SEP','09',
							  'OCT','10',
							  'NOV','11',
							  'DEC','12','00') || lpad(nvl(coll_day,'00'),2,'0'),							
	 NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and (a.COLL_MONTH is not null or a.coll_day is not null or a.coll_year is not null)
;

--COLL_SOURCE
	INSERT INTO ATRIBUTS 
	select atributs_seq.nextval, a.gid, 1135, -1, a.coll_source, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.COLL_SOURCE is not null
;

--COLL_SOURCE_OTH
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -9, -1, a.coll_source_oth, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.COLL_SOURCE_OTH is not null
;


--CULT_TYPE
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -11, -1, a.cult_type, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.CULT_TYPE is not null
;

--DIRECT_SEED
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -12, -1, a.direct_seed, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.DIRECT_SEED is not null
;

--DISTRICT
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -13, -1, a.district, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.DISTRICT is not null
;
--DOUBLE_TRANS
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -14, -1, a.double_trans, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.DOUBLE_TRANS is not null
;

--DRAINAGE
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -15, -1, a.drainage, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.DRAINAGE is not null
;

--ECOSYS
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -16, -1, a.ecosys, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.ECOSYS is not null
;

--ECOZONE
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -17, -1, a.ecozone, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.ECOZONE is not null
;

--FAO_DATE
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, 1131, -1, a.fao_date, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.FAO_DATE is not null
;

--FAO_PROC
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -20, -1, a.fao_proc, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.FAO_PROC is not null
;

--FAO_REMARKS
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -21, -1, a.fao_remarks, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.FAO_REMARKS is not null
;

--FREQ
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -22, -1, a.freq, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.FREQ is not null
;

--FUND
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -23, -1, a.fund, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.FUND is not null
;

--GROWER
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -24, -1, a.grower, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.GROWER is not null
;

--HARVEST_DATE
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -25, -1, a.harvest_date, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.HARVEST_DATE is not null
;

--HERB
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -26, -1, a.herb, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.HERB is not null
;

--LANG_VAR
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -27, -1, a.lang_var, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.LANG_VAR is not null
;

--MAT
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -28, -1, a.mat, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.MAT is not null
;

--MISSION_CODE
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -29, -1, a.mission_code, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.MISSION_CODE is not null
;
INSERT INTO ATRIBUTS
--MIXED_STAND
	 select atributs_seq.nextval, a.gid, -30, -1, a.mixed_stand, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.MIXED_STAND is not null
;

--MLS_DATE
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, 223, -1, a.mls_date, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.MLS_DATE is not null
;

--MLS_PROC
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -32, -1, a.mls_proc, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.MLS_PROC is not null
;

--MNG_VAR
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -33, -1, a.mng_var, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.MNG_VAR is not null
;

--ORI_COUNTRY
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, 224, -1, a.ori_country, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.ORI_COUNTRY is not null
;

--PREV_NAME
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -35, -1, a.prev_name, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.PREV_NAME is not null
;

--PROV
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -36, -1, a.prov, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.PROV is not null
;

--REID_CODE
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -2165, -1, a.reid_code, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.REID_CODE is not null
;

--REM_GRAIN
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -37, -1, a.rem_grain, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.REM_GRAIN is not null
;

--REM_LAT_LONGI
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -38, -1, a.rem_lat_longi, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.REM_LAT_LONGI is not null
;

--REM_OTHER
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -39, -1, a.rem_other, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.REM_OTHER is not null
;

--REM_PEST
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -40, -1, a.rem_pest, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.REM_PEST is not null
;

--REM_PLANT
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -41, -1, a.rem_plant, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.REM_PLANT is not null
;

--REM_SAMPLE_STAT
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -42, -1, a.rem_sample_stat, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.REM_SAMPLE_STAT is not null
;

--REM_SPECIAL
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -43, -1, a.rem_special, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.REM_SPECIAL is not null
;

--SAMPLE_STAT
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -44, -1, a.sample_stat, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.SAMPLE_STAT is not null
;

--SAMPLE_STAT_OTH
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -45, -1, a.sample_stat_oth, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.SAMPLE_STAT_OTH is not null
;

--SAMPLE_TYPE
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -46, -1, a.sample_type, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.SAMPLE_TYPE is not null
;

--SAMP_METHOD
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -47, -1, a.samp_method, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.SAMP_METHOD is not null
;

--SAMP_METHOD_SP
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -48, -1, a.samp_method_sp, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.SAMP_METHOD_SP is not null
;

--SAMP_ORIG
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -49, -1, a.samp_orig, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.SAMP_ORIG is not null
;

--SEED_FILE
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -50, -1, a.seed_file, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.SEED_FILE is not null
;

--SENDER_CCODE
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -52, -1, a.sender_ccode, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.SENDER_CCODE is not null
;

--SHIFT_CULT
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -58, -1, a.shift_cult, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.SHIFT_CULT is not null
;

--SITE
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -56, -1, a.site, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.SITE is not null
;

--SOIL_TEXT
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -57, -1, a.soil_text, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.SOIL_TEXT is not null
;

--SOURCE_INFO
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -2175, -1, a.source_info, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.SOURCE_INFO is not null
;

--SOW_DATE
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -59, -1, a.sow_date, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.SOW_DATE is not null
;

--SPP_CODE
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, 1133, -1, a.spp_code, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.SPP_CODE is not null
;

--SS_COUNTRY
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -62, -1, a.ss_country, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.SS_COUNTRY is not null
;

--SS_STATION
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -63, -1, a.ss_station, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.SS_STATION is not null
;

--STATUS_ACC
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -65, -1, a.status_acc, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.STATUS_ACC is not null
;

--TAXNO
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -66, -1, a.taxno, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.TAXNO is not null
;

--TERRACED_CULT
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -67, -1, a.terraced_cult, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.TERRACED_CULT is not null
;

--TOPO
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -68, -1, a.topo, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.TOPO is not null
;

--TOPO_OTH
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -69, -1, a.topo_oth, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.TOPO_OTH is not null
;

--TOWN
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -70, -1, a.town, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.TOWN is not null
;

--TRANS_DATE
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -71, -1, a.trans_date, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.TRANS_DATE is not null
;

--USAGE
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -72, -1, a.usage, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.USAGE is not null
;

--VARLINE_TYPE
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -73, -1, a.varline_type, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.VARLINE_TYPE is not null
;

--VAR_SAMP
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -75, -1, a.var_samp, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.VAR_SAMP is not null
;

--VG
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, 1132, -1, a.vg, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.VG is not null
;

--VILLAGE
INSERT INTO ATRIBUTS
	 select atributs_seq.nextval, a.gid, -77, -1, a.village, NVL(B.CNTRYID,0), 0, TO_NUMBER(TO_CHAR(SYSDATE,'YYYYMMDD')) 
	 from passport1 a, iris_central.cntry b
	 where a.ori_Country=b.isothree(+)
	 	   and a.VILLAGE is not null
;

COMMIT;

Retrieved from "http://localhost../../../../articles/g/r/i/GRIMS_Insert_Atributs_0b29.html"
Personal tools