API tools faq
paste
Guest User

Untitled

a guest
May 25th, 2018
57
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 42.44 KB | None | 0 0
raw download clone embed print report
  1. == 94 InstallCdcExportData: migrating =========================================
  2. -- execute("DROP FUNCTION fnTrisanoExport(integer)")
  3. -> 0.0082s
  4. -- execute("DROP FUNCTION fnTrisanoExportNonGenericCdc (iexport_id integer, iexport_name varchar(50))")
  5. -> 0.0011s
  6. -- transaction()
  7. -- execute("alter table export_predicates alter column comparison_value TYPE varchar(2000)")
  8. -> 0.0157s
  9. -- execute("CREATE OR REPLACE FUNCTION fnTrisanoExportBuildPredicate (iexport_id integer) RETURNS varchar(4000) \r\nAS $$\r\n\r\nDECLARE\r\n return_status \tvarchar(50);\r\n thePredicate\tvarchar(4000);\r\n theRow\t\tinteger;\r\n-- to build the predicate\r\nDECLARE preds export_predicates%ROWTYPE;\r\nDECLARE pred \tCURSOR FOR SELECT * \r\n FROM export_predicates \r\n WHERE export_name_id = 1 --iexport_id\r\n ORDER BY id;\r\n\r\nBEGIN\r\n\r\n thePredicate\t:= ' WHERE ';\r\n theRow\t:= 0;\r\n return_status\t:= 'FAILURE';\r\n\r\n OPEN pred\r\n ;\r\n\r\n LOOP\r\n FETCH pred\r\n INTO \r\n preds\r\n ;\r\n EXIT WHEN NOT FOUND;\r\n\r\n theRow\t := theRow + 1;\r\n thePredicate := thePredicate || '( '|| preds.column_name\r\n\t || ' ' || preds.comparison_operator || ' ''' || preds.comparison_value \r\n\t ||''' ' || ' ) '\r\n\t || COALESCE(preds.comparison_logical,' ', preds.comparison_logical) \r\n\t || ' ' ;\r\n\r\n/*\r\n thePredicate := thePredicate || '( '|| preds.table_name || '.' || preds.column_name\r\n\t || ' ' || preds.comparison_operator || ' ''' || preds.comparison_value \r\n\t ||''' ' || ' ) '\r\n\t || COALESCE(preds.comparison_logical,' ', preds.comparison_logical) \r\n\t || ' ' ;\r\n*/\t \r\n\r\n END LOOP;\r\n \r\n -- where there any rows in the predicates\r\n IF theRow = 0\r\n THEN\r\n thePredicate\t:= ' ';\r\n ELSE\r\n thePredicate\t:= thePredicate || ' ; ';\r\n END IF;\r\n\r\n RETURN thePredicate;\r\n\r\nEND;\r\n$$ \r\nLANGUAGE plpgsql;\r\n")
  10. -> 0.0014s
  11. -- execute("CREATE OR REPLACE FUNCTION fnTrisanoExport (integer) RETURNS varchar \r\nAS $$\r\ndeclare \r\n\tiexport_id ALIAS FOR $1;\r\n\tvexport_name varchar(50);\r\n\treturn_status varchar(50);\r\nBEGIN\r\n-- If the passed in value is NULL, then error!\r\n\tIF iexport_id IS NULL \r\n\tTHEN RETURN 'NULL VALUE PASSED';\r\n\tEND IF;\r\n-- If the passed in value is NOT NULL, but is not found in the export_names table, then error!\r\n\tSELECT export_name INTO vexport_name FROM export_names WHERE id = iexport_id;\r\n\tif vexport_name is NULL\r\n\tTHEN RETURN 'Invalid export_name.id';\r\n\tEND IF;\r\n\r\n-- Get all the columns for the export_name that will need to be retrieved from the database.\r\n\tIF (vexport_name = 'CDC')\r\n\tTHEN\r\n\r\n\t return_status = fnTrisanoExportNonGenericCdc(iexport_id, vexport_name);\r\n--\t return_status = fnTrisanoExportCdc(iexport_id, vexport_name);\r\n\t return return_status;\r\n--\t IF substring(return_status,1,7) = 'SUCCESS'\r\n--\t THEN\r\n--\t\treturn 'SUCCESS';\r\n--\t ELSE\r\n--\t\treturn 'FAILURE';\r\n--\t END IF;\r\n\tELSE\r\n\t\treturn vexport_name;\r\n\tEND IF;\r\n\r\nEND;\r\n$$ \r\nLANGUAGE plpgsql;\r\n\r\n")
  12. -> 0.0011s
  13. -- execute("truncate table export_conversion_values cascade;\r\ntruncate table export_columns cascade;\r\ntruncate table export_names cascade;\r\n\r\nselect setval('export_names_id_seq',1);\r\nselect setval('export_columns_id_seq',1);\r\nselect setval('export_conversion_values_id_seq',1);\r\nselect setval('export_predicates_id_seq',1);\r\n\r\n\r\ninsert into export_names (export_name) \r\n\tvalues ('CDC');\r\n\r\nupdate export_names set id=1 where export_name='CDC';\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'FIXED'\r\n, 'RECTYPE'\r\n, NULL\r\n, NULL\r\n, 'Y'\r\n, 1\r\n, 1\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'FIXED'\r\n, 'UPDATE'\r\n, NULL\r\n, NULL\r\n, 'N'\r\n, 2\r\n, 1\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'FIXED'\r\n, 'STATE'\r\n, NULL\r\n, NULL\r\n, 'Y'\r\n, 3\r\n, 2\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'FIXED'\r\n, 'YEAR'\r\n, NULL\r\n, NULL\r\n, 'Y'\r\n, 5\r\n, 2\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'CORE'\r\n, 'CASEID'\r\n, 'events'\r\n, 'id'\r\n, 'Y'\r\n, 7\r\n, 6\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'FIXED'\r\n, 'SITE'\r\n, NULL\r\n, NULL\r\n, 'Y'\r\n, 13\r\n, 3\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'CORE'\r\n, 'WEEK'\r\n, 'events'\r\n, '\"MMWR_week\"'\r\n, 'Y'\r\n, 16\r\n, 2\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'FIXED'\r\n, 'EVENT'\r\n, 'diseases'\r\n, 'id'\r\n, 'Y'\r\n, 18\r\n, 5\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'FIXED'\r\n, 'COUNT'\r\n, NULL\r\n, NULL\r\n, 'Y'\r\n, 23\r\n, 5\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'CORE'\r\n, 'COUNTY'\r\n, 'addresses'\r\n, 'county_id'\r\n, 'N'\r\n, 28\r\n, 3\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'CORE'\r\n, 'BIRTHDATE'\r\n, 'people'\r\n, 'birth_date'\r\n, 'N'\r\n, 31\r\n, 8\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'CORE'\r\n, 'AGE'\r\n, 'events' \r\n, 'age_at_onset'\r\n, 'N'\r\n, 39\r\n, 3\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'CORE'\r\n, 'AGETYPE'\r\n, 'events' \r\n, 'age_type_id'\r\n, 'N'\r\n, 42\r\n, 1\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'CORE'\r\n, 'SEX'\r\n, 'people'\r\n, 'birth_gender_id'\r\n, 'N'\r\n, 43\r\n, 1\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'CORE'\r\n, 'RACE'\r\n, 'people_races'\r\n, 'race_id'\r\n, 'N'\r\n, 44\r\n, 1\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'CORE'\r\n, 'ETHNICITY'\r\n, 'people'\r\n, 'ethnicity_id'\r\n, 'N'\r\n, 45\r\n, 1\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'CORE'\r\n, 'EVENTDATE'\r\n, 'events'\r\n, 'event_onset_date'\r\n, 'Y'\r\n, 46\r\n, 6\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'FIXED'\r\n, 'DATETYPE'\r\n, NULL\r\n, NULL\r\n, 'Y'\r\n, 46\r\n, 6\r\n);\r\n\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'CORE'\r\n, 'CASESTATUS'\r\n, 'events'\r\n, 'event_status'\r\n, 'N'\r\n, 53\r\n, 1\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'CORE'\r\n, 'IMPORTED'\r\n, 'events'\r\n, 'imported_from_id'\r\n, 'N'\r\n, 54\r\n, 1\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'CORE'\r\n, 'OUTBREAK'\r\n, 'events'\r\n, 'outbreak_associated_id'\r\n, 'N'\r\n, 55\r\n, 1\r\n);\r\n\r\ninsert into export_columns ( export_name_id\r\n, type_data\r\n, export_column_name\r\n, table_name\r\n, column_name\r\n, is_required\r\n, start_position\r\n, length_to_output\r\n)\r\nvalues\r\n( 1\r\n, 'FIXED'\r\n, 'FUTURE'\r\n, NULL\r\n, NULL\r\n, 'N'\r\n, 56\r\n, 5\r\n);\r\n\r\n-- This is for loading the conversion codes\r\n-- birthdate if NULL then 99999999 else output as YYYYMMDD\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(12\r\n, NULL\r\n,'99999999'\r\n);\r\n--\tAge\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(13\r\n,NULL\r\n,'999'\r\n);\r\n-- Age Type HAS NOT BEEN DEFINED YET\r\n--insert into export_conversion_values (export_column_id\r\n--, value_from\r\n--, value_to\r\n--)\r\n--values\r\n--(\r\n--,\r\n--,\r\n--)\r\n\r\n\r\n-- Ethnicity ----------------------------------------------------\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(17\r\n,'H'\r\n,'H'\r\n);\r\n\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(17\r\n,'U'\r\n,'U'\r\n);\r\n\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(17\r\n,'NH'\r\n,'N'\r\n);\r\n\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(17\r\n,'O'\r\n,'U'\r\n);\r\n\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(17\r\n,'UNK'\r\n,'U'\r\n);\r\n-- end of Ethnicity ----------------------------------------------\r\n-- Race 16 ----------------------------------------------------------\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(16\r\n,'W'\r\n,'W'\r\n);\r\n\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(16\r\n,'B'\r\n,'B'\r\n);\r\n\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(16\r\n,'AA'\r\n,'N'\r\n);\r\n\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(16\r\n,'A'\r\n,'A'\r\n);\r\n\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(16\r\n,'AK'\r\n,'N'\r\n);\r\n\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(16\r\n,'H'\r\n,'A'\r\n);\r\n\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(16\r\n,'UNK'\r\n,'U'\r\n);\r\n-- end of Ethnicity --------------------------------------------------\r\n--- Case Status ------------------------------------------------------\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(20\r\n,'U'\r\n,'9'\r\n);\r\n\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(20\r\n,'UNK'\r\n,'9'\r\n);\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(20\r\n,'C'\r\n,'1'\r\n);\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(20\r\n,'P'\r\n,'2'\r\n);\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(20\r\n,'S'\r\n,'3'\r\n);\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(20\r\n,'NC'\r\n,'U'\r\n);\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(20\r\n,'CC'\r\n,'N'\r\n);\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(20\r\n,'D'\r\n,'U'\r\n);\r\n\r\n-- End of Case Status ---------------------------------------------------\r\n\r\n-- Imported from ------------------------------------------------------\r\n\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(21\r\n,'U'\r\n,'9'\r\n);\r\n\r\n\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(21\r\n,'UNK'\r\n,'9'\r\n);\r\n\r\n\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(21\r\n,'US'\r\n,'3'\r\n);\r\n\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(21\r\n,'UT'\r\n,'1'\r\n);\r\n\r\ninsert into export_conversion_values (export_column_id\r\n, value_from\r\n, value_to\r\n)\r\nvalues\r\n(21\r\n,'F'\r\n,'2'\r\n);\r\n\r\n-- end of Imported By ----------------------------------------------\r\n-- county code ------------------------------------------------------\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'BV'\r\n , '001' )\r\n;\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'BE'\r\n , '003' )\r\n;\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'CA'\r\n , '005' )\r\n;\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'CR'\r\n , '007' )\r\n;\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'DG'\r\n , '009' )\r\n;\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'DV'\r\n , '011' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'DU'\r\n , '013' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'EM'\r\n , '015' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'GA'\r\n , '017' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'GR'\r\n , '019' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'IR'\r\n , '021' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'JU'\r\n , '023' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'KA'\r\n , '025' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'MI'\r\n , '027' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'MO'\r\n , '029' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'RI'\r\n , '033' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'SL'\r\n , '035' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'SJ'\r\n , '037' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'SP'\r\n , '039' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'SV'\r\n , '041' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'SM'\r\n , '043' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'TL'\r\n , '045' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'UI'\r\n , '047' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'UT'\r\n , '049' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'WS'\r\n , '051' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'WA'\r\n , '053' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'WN'\r\n , '055' );\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (11\r\n , 'WB'\r\n , '057' );\r\n-- end of county ----------------------------------------------\r\n-- start of diseases/event -------------------------------------\r\n insert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'AIDS'\r\n , '10560' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Amebiasis'\r\n , '11040' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Anthrax'\r\n , '10350' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Aseptic meningitis'\r\n , '10010' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Bacterial meningitis, other'\r\n , '10650' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Brucellosis'\r\n , '10020' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Campylobacteriosis'\r\n , '11020' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Chancroid'\r\n , '10273' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Cryptosporidiosis'\r\n , '11580' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Cyclosporiasis'\r\n , '11575' );\r\n \r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Dengue hemorrhagic fever'\r\n , '10685' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Diphtheria'\r\n , '10040' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Encephalitis, post-mumps'\r\n , '10080' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , '10090'\r\n , 'Encephalitis, post-other' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Encephalitis, primary'\r\n , '10050' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Lead poisoning'\r\n , '32010' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , '10490'\r\n , 'Legionellosis' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Malaria'\r\n , '10130' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Mumps'\r\n , '10180' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Neurosyphilis'\r\n , '10317' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Plague'\r\n , '10440' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Q fever'\r\n , '10255' );\r\n\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Rubella'\r\n , '10200' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Smallpox'\r\n , '11800' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Tetanus'\r\n , '10210' );\r\n\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , 'Tularemia'\r\n , '10230' );\r\n\r\n/*\r\ninsert into export_conversion_values (export_column_id\r\n , value_from\r\n , value_to\r\n )\r\n VALUES\r\n (9\r\n , ''\r\n , '' );\r\n*/\r\n\r\n\r\n\r\n\r\n\r\n")
  14. -> 0.0552s
  15. -- execute("--drop view v_export_cdc;\nCREATE VIEW v_export_cdc AS\nSELECT DISTINCT\n cast(exp_rectype as char(1))\n, cast(exp_update as char(1))\n, cast(exp_state as char(2))\n, cast(exp_year as char(2))\n, cast(exp_caseid as char(6))\n, cast(exp_site as char(3))\n, cast(exp_week as char(2))\n, cast(exp_event as char(5))\n, cast(exp_count as char(5))\n, cast(exp_county as char(3))\n, cast(exp_birthdate as char(8) )\n--, cast(COALESCE(exp_birthdate,'99999999',exp_birthdate) as char(8)) AS exp_birthdate\n, cast(exp_age as char(3))\n, cast(exp_agetype as char(1))\n, cast(exp_sex as char(1))\n, cast(exp_race as char(1))\n, cast(exp_ethnicity as char(1))\n-- have to do eventdate and datetype in separate query\n, CASE \n WHEN \tevent_onset_date <= first_reported_ph_date \n\tAND \tevent_onset_date <= collection_date \n\tAND \tevent_onset_date <= lab_test_date\n THEN cast(substr(EXTRACT(YEAR FROM event_onset_date),3,4) \n || LPAD(EXTRACT(MONTH FROM event_onset_date), 2, '0') \n || LPAD(EXTRACT(DAY FROM event_onset_date), 2, '0') as char(6))\n WHEN \tfirst_reported_ph_date <= event_onset_date \n\tAND \tfirst_reported_ph_date <= collection_date \n\tAND \tfirst_reported_ph_date <= lab_test_date\n THEN cast(substr(EXTRACT(YEAR FROM first_reported_ph_date),3,4) \n || LPAD(EXTRACT(MONTH FROM first_reported_ph_date), 2, '0') \n || LPAD(EXTRACT(DAY FROM first_reported_ph_date), 2, '0') as char(6))\n WHEN \tcollection_date <= event_onset_date\n\tAND \tcollection_date <= first_reported_ph_date\n\tAND \tcollection_date <= lab_test_date\n THEN cast(substr(EXTRACT(YEAR FROM collection_date),3,4) \n || LPAD(EXTRACT(MONTH FROM collection_date), 2, '0') \n || LPAD(EXTRACT(DAY FROM collection_date), 2, '0') as char(6))\n WHEN\tlab_test_date <= event_onset_date\n\t AND\tlab_test_date <= first_reported_ph_date\n\tAND\tlab_test_date <= collection_date\n THEN cast(substr(EXTRACT(YEAR FROM lab_test_date),3,4) \n || LPAD(EXTRACT(MONTH FROM lab_test_date), 2, '0') \n || LPAD(EXTRACT(DAY FROM lab_test_date), 2, '0') as char(6))\n END AS exp_eventdate\n, CASE \n WHEN \tevent_onset_date <= first_reported_ph_date \n\tAND \tevent_onset_date <= collection_date \n\tAND \tevent_onset_date <= lab_test_date\n THEN cast('1' as char(1))\n WHEN \tfirst_reported_ph_date <= event_onset_date \n\tAND \tfirst_reported_ph_date <= collection_date \n\tAND \tfirst_reported_ph_date <= lab_test_date\n THEN cast('4' as char(1))\n WHEN \tcollection_date <= event_onset_date\n\tAND \tcollection_date <= first_reported_ph_date\n\tAND \tcollection_date <= lab_test_date\n THEN cast('9' as char(1))\n WHEN\tlab_test_date <= event_onset_date\n\tAND\tlab_test_date <= first_reported_ph_date\n\tAND\tlab_test_date <= collection_date\n THEN cast('3' as char(1))\n ELSE cast('9' as char(1))\n END as exp_datetype\n, cast(exp_casestatus as char(2))\n, cast(exp_imported as char(2))\n, cast(exp_outbreak as char(2))\n, cast(exp_future as char(2))\n, disease_name\n, disease_id\n, mmwr_week\n, mmwr_year\n, udoh_case_status_id\n, event_onset_date\n, event_status\nFROM\n(\nSELECT events.id\n , events.\"MMWR_week\" AS mmwr_week\n , events.\"MMWR_year\" AS mmwr_year\n , events.udoh_case_status_id AS udoh_case_status_id\n , addresses.county_id\n , people.birth_date\n , events.age_at_onset\n , events.age_type_id\n , people.birth_gender_id\n , people_races.race_id\n , people.ethnicity_id\n , coalesce(events.event_onset_date,'12/31/9998', event_onset_date) AS event_onset_date\n , coalesce(events.\"first_reported_PH_date\",'12/31/9998', events.\"first_reported_PH_date\")\n\t AS first_reported_ph_date\n , coalesce(lab_results.collection_date,'12/31/9998', lab_results.collection_date ) AS collection_date\n , coalesce(lab_results.lab_test_date,'12/31/9998', lab_results.lab_test_date ) AS lab_test_date\n , events.event_status\n , events.imported_from_id\n , events.outbreak_associated_id\n , county.the_code AS county_code\n , ageType.the_code AS age_type\n , gender.the_code AS gender\n , CASE \n WHEN events.cdc_update = true THEN 'T'\n ELSE 'F'\n END\n , CASE \n WHEN events.sent_to_cdc = true THEN 'T'\n ELSE 'F'\n END\n-- these columns are to get the conversion values\n , CAST('M' AS char(1)) AS exp_rectype\n , CAST(' ' AS char(1)) AS exp_update\n , CAST('49' AS char(2)) AS exp_state\n , SUBSTR(EXTRACT(YEAR from CURRENT_DATE), 3,2) AS exp_year \n , LPAD(events.id, 6, ' ') AS exp_caseid\n , CAST('S01' AS CHAR(3)) AS exp_site\n , events.\"MMWR_week\" AS exp_week\n , cast(coalesce(valdisease.value_to, '99999', valdisease.value_to) as char(5)) AS exp_event\n , disease_name AS disease_name\n , diseases.id as disease_id\n , CAST('00001' AS CHAR(6)) as exp_count\n , COALESCE(valcounty.value_to, '999', valcounty.value_to) AS exp_county\n , cast(coalesce(EXTRACT(YEAR FROM people.birth_date) \n || LPAD(EXTRACT(MONTH FROM people.birth_date), 2, '0') \n || LPAD(EXTRACT(DAY FROM people.birth_date), 2, '0'),'99999999',\n\tEXTRACT(YEAR FROM people.birth_date) \n\t|| LPAD(EXTRACT(MONTH FROM people.birth_date), 2, '0') \n\t|| LPAD(EXTRACT(DAY FROM people.birth_date), 2, '0')) as char(8)) \n AS exp_birthdate\n , people.approximate_age_no_birthday AS exp_age \n , COALESCE(ageType.the_code, '9', ageType.the_code) AS exp_agetype \n , COALESCE(gender.the_code, 'U', gender.the_code) AS exp_sex\n , COALESCE(valrace.value_to, 'U', valrace.value_to) AS exp_race \n , COALESCE(valethnicity.value_to, 'U', valethnicity.value_to) AS exp_ethnicity \n-- have to do eventdate and datetype in separate query\n-- , 'evdate' AS exp_eventdate\n-- , 'X' as exp_datetype\n , COALESCE(valcasestatus.value_to, '9', valcasestatus.value_to) AS exp_casestatus\n , COALESCE(valimported.value_to, '9', valimported.value_to) AS exp_imported\n , CAST('0' AS CHAR(1)) AS exp_outbreak \n , CAST(' ' AS CHAR(5)) AS exp_future \nFROM \n events \n INNER JOIN participations\n ON events.id = participations.event_id\n INNER JOIN entities\n ON participations.primary_entity_id = entities.id\n LEFT OUTER JOIN entities_locations\n ON entities_locations.entity_id = entities.id\n LEFT OUTER JOIN addresses\n ON addresses.location_id = entities_locations.location_id\n INNER JOIN disease_events\n ON events.id = disease_events.event_id\n INNER JOIN diseases\n ON disease_events.disease_id = diseases.id\n INNER JOIN people\n ON people.entity_id = entities.id\n LEFT OUTER JOIN people_races\n ON people_races.entity_id = people.entity_id\n LEFT OUTER JOIN lab_results\n ON lab_results.participation_id = participations.id\n LEFT OUTER JOIN codes rolecode\n ON rolecode.id = participations.role_id\n AND rolecode.the_code = 'I' and rolecode.code_name = 'participant'\n LEFT OUTER JOIN external_codes locprimary\n ON locprimary.id = entities_locations.primary_yn_id\n AND locprimary.the_code = 'Y' and locprimary.code_name = 'yesno'\n LEFT OUTER JOIN external_codes county\n ON county.id = addresses.county_id\n LEFT OUTER JOIN external_codes ageType\n ON ageType.id = people.age_type_id\n LEFT OUTER JOIN external_codes gender\n ON gender.id = people.birth_gender_id\n LEFT OUTER JOIN external_codes race\n ON race.id = people_races.race_id\n LEFT OUTER JOIN external_codes ethnicity\n ON ethnicity.id = people.ethnicity_id\n LEFT OUTER JOIN external_codes imported\n ON imported.id = events.imported_from_id\n-- now, get the conversion values\n LEFT OUTER JOIN export_conversion_values valgender\n ON valgender.value_from = gender.the_code\n LEFT OUTER JOIN export_conversiorake aborted!
  16. RuntimeError: ERROR C22008 Mdate/time field value out of range: "12/31/9998" HPerhaps you need a different "datestyle" setting. Fdatetime.c L3109RDateTimeParseError: --drop view v_export_cdc;
  17. CREATE VIEW v_export_cdc AS
  18. SELECT DISTINCT
  19. cast(exp_rectype as char(1))
  20. , cast(exp_update as char(1))
  21. , cast(exp_state as char(2))
  22. , cast(exp_year as char(2))
  23. , cast(exp_caseid as char(6))
  24. , cast(exp_site as char(3))
  25. , cast(exp_week as char(2))
  26. , cast(exp_event as char(5))
  27. , cast(exp_count as char(5))
  28. , cast(exp_county as char(3))
  29. , cast(exp_birthdate as char(8) )
  30. --, cast(COALESCE(exp_birthdate,'99999999',exp_birthdate) as char(8)) AS exp_birthdate
  31. , cast(exp_age as char(3))
  32. , cast(exp_agetype as char(1))
  33. , cast(exp_sex as char(1))
  34. , cast(exp_race as char(1))
  35. , cast(exp_ethnicity as char(1))
  36. -- have to do eventdate and datetype in separate query
  37. , CASE
  38. WHEN event_onset_date <= first_reported_ph_date
  39. AND event_onset_date <= collection_date
  40. AND event_onset_date <= lab_test_date
  41. THEN cast(substr(EXTRACT(YEAR FROM event_onset_date),3,4)
  42. || LPAD(EXTRACT(MONTH FROM event_onset_date), 2, '0')
  43. || LPAD(EXTRACT(DAY FROM event_onset_date), 2, '0') as char(6))
  44. WHEN first_reported_ph_date <= event_onset_date
  45. AND first_reported_ph_date <= collection_date
  46. AND first_reported_ph_date <= lab_test_date
  47. THEN cast(substr(EXTRACT(YEAR FROM first_reported_ph_date),3,4)
  48. || LPAD(EXTRACT(MONTH FROM first_reported_ph_date), 2, '0')
  49. || LPAD(EXTRACT(DAY FROM first_reported_ph_date), 2, '0') as char(6))
  50. WHEN collection_date <= event_onset_date
  51. AND collection_date <= first_reported_ph_date
  52. AND collection_date <= lab_test_date
  53. THEN cast(substr(EXTRACT(YEAR FROM collection_date),3,4)
  54. || LPAD(EXTRACT(MONTH FROM collection_date), 2, '0')
  55. || LPAD(EXTRACT(DAY FROM collection_date), 2, '0') as char(6))
  56. WHEN lab_test_date <= event_onset_date
  57. AND lab_test_date <= first_reported_ph_date
  58. AND lab_test_date <= collection_date
  59. THEN cast(substr(EXTRACT(YEAR FROM lab_test_date),3,4)
  60. || LPAD(EXTRACT(MONTH FROM lab_test_date), 2, '0')
  61. || LPAD(EXTRACT(DAY FROM lab_test_date), 2, '0') as char(6))
  62. END AS exp_eventdate
  63. , CASE
  64. WHEN event_onset_date <= first_reported_ph_date
  65. AND event_onset_date <= collection_date
  66. AND event_onset_date <= lab_test_date
  67. THEN cast('1' as char(1))
  68. WHEN first_reported_ph_date <= event_onset_date
  69. AND first_reported_ph_date <= collection_date
  70. AND first_reported_ph_date <= lab_test_date
  71. THEN cast('4' as char(1))
  72. WHEN collection_date <= event_onset_date
  73. AND collection_date <= first_reported_ph_date
  74. AND collection_date <= lab_test_date
  75. THEN cast('9' as char(1))
  76. WHEN lab_test_date <= event_onset_date
  77. AND lab_test_date <= first_reported_ph_date
  78. AND lab_test_date <= collection_date
  79. THEN cast('3' as char(1))
  80. ELSE cast('9' as char(1))
  81. END as exp_datetype
  82. , cast(exp_casestatus as char(2))
  83. , cast(exp_imported as char(2))
  84. , cast(exp_outbreak as char(2))
  85. , cast(exp_future as char(2))
  86. , disease_name
  87. , disease_id
  88. , mmwr_week
  89. , mmwr_year
  90. , udoh_case_status_id
  91. , event_onset_date
  92. , event_status
  93. FROM
  94. (
  95. SELECT events.id
  96. , events."MMWR_week" AS mmwr_week
  97. , events."MMWR_year" AS mmwr_year
  98. , events.udoh_case_status_id AS udoh_case_status_id
  99. , addresses.county_id
  100. , people.birth_date
  101. , events.age_at_onset
  102. , events.age_type_id
  103. , people.birth_gender_id
  104. , people_races.race_id
  105. , people.ethnicity_id
  106. , coalesce(events.event_onset_date,'12/31/9998', event_onset_date) AS event_onset_date
  107. , coalesce(events."first_reported_PH_date",'12/31/9998', events."first_reported_PH_date")
  108. AS first_reported_ph_date
  109. , coalesce(lab_results.collection_date,'12/31/9998', lab_results.collection_date ) AS collection_date
  110. , coalesce(lab_results.lab_test_date,'12/31/9998', lab_results.lab_test_date ) AS lab_test_date
  111. , events.event_status
  112. , events.imported_from_id
  113. , events.outbreak_associated_id
  114. , county.the_code AS county_code
  115. , ageType.the_code AS age_type
  116. , gender.the_code AS gender
  117. , CASE
  118. WHEN events.cdc_update = true THEN 'T'
  119. ELSE 'F'
  120. END
  121. , CASE
  122. WHEN events.sent_to_cdc = true THEN 'T'
  123. ELSE 'F'
  124. END
  125. -- these columns are to get the conversion values
  126. , CAST('M' AS char(1)) AS exp_rectype
  127. , CAST(' ' AS char(1)) AS exp_update
  128. , CAST('49' AS char(2)) AS exp_state
  129. , SUBSTR(EXTRACT(YEAR from CURRENT_DATE), 3,2) AS exp_year
  130. , LPAD(events.id, 6, ' ') AS exp_caseid
  131. , CAST('S01' AS CHAR(3)) AS exp_site
  132. , events."MMWR_week" AS exp_week
  133. , cast(coalesce(valdisease.value_to, '99999', valdisease.value_to) as char(5)) AS exp_event
  134. , disease_name AS disease_name
  135. , diseases.id as disease_id
  136. , CAST('00001' AS CHAR(6)) as exp_count
  137. , COALESCE(valcounty.value_to, '999', valcounty.value_to) AS exp_county
  138. , cast(coalesce(EXTRACT(YEAR FROM people.birth_date)
  139. || LPAD(EXTRACT(MONTH FROM people.birth_date), 2, '0')
  140. || LPAD(EXTRACT(DAY FROM people.birth_date), 2, '0'),'99999999',
  141. EXTRACT(YEAR FROM people.birth_date)
  142. || LPAD(EXTRACT(MONTH FROM people.birth_date), 2, '0')
  143. || LPAD(EXTRACT(DAY FROM people.birth_date), 2, '0')) as char(8))
  144. AS exp_birthdate
  145. , people.approximate_age_no_birthday AS exp_age
  146. , COALESCE(ageType.the_code, '9', ageType.the_code) AS exp_agetype
  147. , COALESCE(gender.the_code, 'U', gender.the_code) AS exp_sex
  148. , COALESCE(valrace.value_to, 'U', valrace.value_to) AS exp_race
  149. , COALESCE(valethnicity.value_to, 'U', valethnicity.value_to) AS exp_ethnicity
  150. -- have to do eventdate and datetype in separate query
  151. -- , 'evdate' AS exp_eventdate
  152. -- , 'X' as exp_datetype
  153. , COALESCE(valcasestatus.value_to, '9', valcasestatus.value_to) AS exp_casestatus
  154. , COALESCE(valimported.value_to, '9', valimported.value_to) AS exp_imported
  155. , CAST('0' AS CHAR(1)) AS exp_outbreak
  156. , CAST(' ' AS CHAR(5)) AS exp_future
  157. FROM
  158. events
  159. INNER JOIN participations
  160. ON events.id = participations.event_id
  161. INNER JOIN entities
  162. ON participations.primary_entity_id = entities.id
  163. LEFT OUTER JOIN entities_locations
  164. ON entities_locations.entity_id = entities.id
  165. LEFT OUTER JOIN addresses
  166. ON addresses.location_id = entities_locations.location_id
  167. INNER JOIN disease_events
  168. ON events.id = disease_events.event_id
  169. INNER JOIN diseases
  170. ON disease_events.disease_id = diseases.id
  171. INNER JOIN people
  172. ON people.entity_id = entities.id
  173. LEFT OUTER JOIN people_races
  174. ON people_races.entity_id = people.entity_id
  175. LEFT OUTER JOIN lab_results
  176. ON lab_results.participation_id = participations.id
  177. LEFT OUTER JOIN codes rolecode
  178. ON rolecode.id = participations.role_id
  179. AND rolecode.the_code = 'I' and rolecode.code_name = 'participant'
  180. LEFT OUTER JOIN external_codes locprimary
  181. ON locprimary.id = entities_locations.primary_yn_id
  182. AND locprimary.the_code = 'Y' and locprimary.code_name = 'yesno'
  183. LEFT OUTER JOIN external_codes county
  184. ON county.id = addresses.county_id
  185. LEFT OUTER JOIN external_codes ageType
  186. ON ageType.id = people.age_type_id
  187. LEFT OUTER JOIN external_codes gender
  188. ON gender.id = people.birth_gender_id
  189. LEFT OUTER JOIN external_codes race
  190. ON race.id = people_races.race_id
  191. LEFT OUTER JOIN external_codes ethnicity
  192. ON ethnicity.id = people.ethnicity_id
  193. LEFT OUTER JOIN external_codes imported
  194. ON imported.id = events.imported_from_id
  195. -- now, get the conversion values
  196. LEFT OUTER JOIN export_conversion_values valgender
  197. ON valgender.value_from = gender.the_code
  198. LEFT OUTER JOIN export_conversion_values valcounty
  199. ON valcounty.value_from = county.the_code
  200. AND valcounty.export_column_id = 11
  201. LEFT OUTER JOIN export_conversion_values valrace
  202. ON valrace.value_from = race.the_code
  203. AND valrace.export_column_id = 16
  204. LEFT OUTER JOIN export_conversion_values valethnicity
  205. ON valethnicity.value_from = ethnicity.the_code
  206. AND valethnicity.export_column_id = 17
  207. LEFT OUTER JOIN export_conversion_values valcasestatus
  208. ON valcasestatus.value_from = events.event_status
  209. AND valcasestatus.export_column_id = 20
  210. LEFT OUTER JOIN export_conversion_values valimported
  211. ON valimported.value_from = imported.the_code
  212. AND valimported.export_column_id = 21
  213. LEFT OUTER JOIN export_conversion_values valdisease
  214. ON valdisease.value_from = diseases.disease_name
  215. AND valdisease.export_column_id = 9
  216. ) AS b
  217. ;
  218.  
  219. (See full trace by running task with --trace)
  220. n_values valcounty\n ON valcounty.value_from = county.the_code\n AND valcounty.export_column_id = 11\n LEFT OUTER JOIN export_conversion_values valrace\n ON valrace.value_from = race.the_code\n AND valrace.export_column_id = 16\n LEFT OUTER JOIN export_conversion_values valethnicity\n ON valethnicity.value_from = ethnicity.the_code\n AND valethnicity.export_column_id = 17\n LEFT OUTER JOIN export_conversion_values valcasestatus\n ON valcasestatus.value_from = events.event_status\n AND valcasestatus.export_column_id = 20\n LEFT OUTER JOIN export_conversion_values valimported\n ON valimported.value_from = imported.the_code\n AND valimported.export_column_id = 21\n LEFT OUTER JOIN export_conversion_values valdisease\n ON valdisease.value_from = diseases.disease_name\n AND valdisease.export_column_id = 9\n ) AS b\n;")
  221. rake aborted!
  222. Command failed with status (1): [/home/mike/opt/jruby-1.1.3/bin/jruby -S ra...]
  223. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:899:in `sh'
  224. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:906:in `call'
  225. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:906:in `sh'
  226. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:989:in `sh'
  227. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:920:in `ruby'
  228. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:989:in `ruby'
  229. /home/mike/projects/trisano-horked/lib/tasks/dev.rake:33
  230. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:546:in `call'
  231. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:546:in `execute'
  232. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:541:in `each'
  233. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:541:in `execute'
  234. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:508:in `invoke_with_call_chain'
  235. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:501:in `invoke_with_call_chain'
  236. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:494:in `invoke'
  237. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:1931:in `invoke_task'
  238. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:1909:in `top_level'
  239. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:1909:in `each'
  240. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:1909:in `top_level'
  241. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:1948:in `standard_exception_handling'
  242. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:1903:in `top_level'
  243. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:1881:in `run'
  244. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:1948:in `standard_exception_handling'
  245. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/lib/rake.rb:1878:in `run'
  246. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/bin/rake:31
  247. /home/mike/opt/jruby-1.1.3/lib/ruby/gems/1.8/gems/rake-0.8.1/bin/rake:19:in `load'
  248. /home/mike/opt/jruby-1.1.3/bin/rake:19
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!