SQL2GMS tricks

Using SQL2GMS to read a large set of CSV files has the big advantage to be able to repair things on the fly. We had a few NULL values in one of the CSV files and I fixed this by skipping these records:

Q39=select 'faotqcntry_'&intFAOLVSTQ_CNTRY,'faolivestp_'&intFAOLVST2_CODE,[dblFAOLVSTPRICE_$S/MT] from LivestockPrices.csv where not isnull([dblFAOLVSTPRICE_$S/MT])

Then I got the request to use a value of 99999 for those records. This can be easily done by:

Q39=select 'faotqcntry_'&intFAOLVSTQ_CNTRY,'faolivestp_'&intFAOLVST2_CODE,iif(isnull([dblFAOLVSTPRICE_$S/MT]),99999,[dblFAOLVSTPRICE_$S/MT]) from LivestockPrices.csv

Note: we generate more meaningful set elements than 1, 2, 3,… by prefixing them with an ID string so they become faotqcntry_1, faotqcntry_2, faotqcntry_3,… This helps when debugging GAMS models.

See also [link].