/* 
 * fixsql  <varlist>
 * This program can fix the problem where data are lost using -odbc load-,
 * because real data values might collide with Stata missing values. 
 *
 * THIS PROGRAM ASSUMES THERE WERE NO NULL VALUES IN THE ORIGINAL SQL DATASET.
 */

program fixsql
version 8
quietly {
	preserve
	syntax varlist
	foreach var of varlist `varlist' {
		local type : type `var'
		if `"`type'"' == "long" {
			count if `var' >= .
			if `r(N)' > 0 {
				// promote this variable
				recast double `var'
				replace `var' = 2147483621 if `var' == .
				replace `var' = 2147483622 if `var' == .a
				replace `var' = 2147483623 if `var' == .b
				replace `var' = 2147483624 if `var' == .c
				replace `var' = 2147483625 if `var' == .d
				replace `var' = 2147483626 if `var' == .e
				replace `var' = 2147483627 if `var' == .f
				replace `var' = 2147483628 if `var' == .g
				replace `var' = 2147483629 if `var' == .h
				replace `var' = 2147483630 if `var' == .i
				replace `var' = 2147483631 if `var' == .j
				replace `var' = 2147483632 if `var' == .k
				replace `var' = 2147483633 if `var' == .l
				replace `var' = 2147483634 if `var' == .m
				replace `var' = 2147483635 if `var' == .n
				replace `var' = 2147483636 if `var' == .o
				replace `var' = 2147483637 if `var' == .p
				replace `var' = 2147483638 if `var' == .q
				replace `var' = 2147483639 if `var' == .r
				replace `var' = 2147483640 if `var' == .s
				replace `var' = 2147483641 if `var' == .t
				replace `var' = 2147483642 if `var' == .u
				replace `var' = 2147483643 if `var' == .v
				replace `var' = 2147483644 if `var' == .w
				replace `var' = 2147483645 if `var' == .x
				replace `var' = 2147483646 if `var' == .y
				replace `var' = 2147483647 if `var' == .z
				di in white `"Transformed variable `var' long to double"'
			}
		}
		if `"`type'"' == "int" {
			count if `var' >= .
			if `r(N)' > 0 {
				// promote this variable
				recast long `var'
				replace `var' = 32741 if `var' == .
				replace `var' = 32742 if `var' == .a
				replace `var' = 32743 if `var' == .b
				replace `var' = 32744 if `var' == .c
				replace `var' = 32745 if `var' == .d
				replace `var' = 32746 if `var' == .e
				replace `var' = 32747 if `var' == .f
				replace `var' = 32748 if `var' == .g
				replace `var' = 32749 if `var' == .h
				replace `var' = 32750 if `var' == .i
				replace `var' = 32751 if `var' == .j
				replace `var' = 32752 if `var' == .k
				replace `var' = 32753 if `var' == .l
				replace `var' = 32754 if `var' == .m
				replace `var' = 32755 if `var' == .n
				replace `var' = 32756 if `var' == .o
				replace `var' = 32757 if `var' == .p
				replace `var' = 32758 if `var' == .q
				replace `var' = 32759 if `var' == .r
				replace `var' = 32760 if `var' == .s
				replace `var' = 32761 if `var' == .t
				replace `var' = 32762 if `var' == .u
				replace `var' = 32763 if `var' == .v
				replace `var' = 32764 if `var' == .w
				replace `var' = 32765 if `var' == .x
				replace `var' = 32766 if `var' == .y
				replace `var' = 32767 if `var' == .z
				di in white `"Transformed variable `var' from int to long"'
			}
		}
		if `"`type'"' == "byte" {
			count if `var' >= .
			if `r(N)' > 0 {
				// promote this variable
				recast int var'
				replace `var' = 101 if `var' == .
				replace `var' = 102 if `var' == .a
				replace `var' = 103 if `var' == .b
				replace `var' = 104 if `var' == .c
				replace `var' = 105 if `var' == .d
				replace `var' = 106 if `var' == .e
				replace `var' = 107 if `var' == .f
				replace `var' = 108 if `var' == .g
				replace `var' = 109 if `var' == .h
				replace `var' = 110 if `var' == .i
				replace `var' = 111 if `var' == .j
				replace `var' = 112 if `var' == .k
				replace `var' = 113 if `var' == .l
				replace `var' = 114 if `var' == .m
				replace `var' = 115 if `var' == .n
				replace `var' = 116 if `var' == .o
				replace `var' = 117 if `var' == .p
				replace `var' = 118 if `var' == .q
				replace `var' = 119 if `var' == .r
				replace `var' = 120 if `var' == .s
				replace `var' = 121 if `var' == .t
				replace `var' = 122 if `var' == .u
				replace `var' = 123 if `var' == .v
				replace `var' = 124 if `var' == .w
				replace `var' = 125 if `var' == .x
				replace `var' = 126 if `var' == .y
				replace `var' = 127 if `var' == .z
				di in white `"Transformed variable `var' from byte to int"'
			}
		}

	}
	restore, not
}
end