请求将G-NAF数据的SQL视图语句转换为MS Access兼容语法

关键转换要点 #

• MS Access不支持CREATE OR REPLACE VIEW语法，需先删除已存在的同名视图（若有），再执行CREATE VIEW
• 原SQL中的HTML转义字符>需替换为Access可识别的>
• Access对多表JOIN的语法要求更严格，需用括号明确关联顺序，避免解析错误
• 关于numeric转number：此调整仅需在表结构导入时完成（将原表的numeric类型字段改为Access的Number类型），查询语句中无需额外修改字段引用

转换后的完整SQL语句 #

-- 若视图已存在，先删除（Access中需确保视图存在再执行，或直接忽略错误）
DROP VIEW ADDRESS_VIEW;

CREATE VIEW ADDRESS_VIEW
AS
SELECT
    AD.ADDRESS_DETAIL_PID as ADDRESS_DETAIL_PID,
    AD.STREET_LOCALITY_PID as STREET_LOCALITY_PID,
    AD.LOCALITY_PID as LOCALITY_PID,
    AD.BUILDING_NAME as BUILDING_NAME,

    AD.LOT_NUMBER_PREFIX as LOT_NUMBER_PREFIX,
    AD.LOT_NUMBER as LOT_NUMBER,
    AD.LOT_NUMBER_SUFFIX as LOT_NUMBER_SUFFIX,

    FTA.NAME as FLAT_TYPE,
    AD.FLAT_NUMBER_PREFIX as FLAT_NUMBER_PREFIX,
    AD.FLAT_NUMBER as FLAT_NUMBER,
    AD.FLAT_NUMBER_SUFFIX as FLAT_NUMBER_SUFFIX,

    LTA.NAME as LEVEL_TYPE,
    AD.LEVEL_NUMBER_PREFIX as LEVEL_NUMBER_PREFIX,
    AD.LEVEL_NUMBER as LEVEL_NUMBER,
    AD.LEVEL_NUMBER_SUFFIX as LEVEL_NUMBER_SUFFIX,

    AD.NUMBER_FIRST_PREFIX as NUMBER_FIRST_PREFIX,
    AD.NUMBER_FIRST as NUMBER_FIRST,
    AD.NUMBER_FIRST_SUFFIX as NUMBER_FIRST_SUFFIX,
    AD.NUMBER_LAST_PREFIX as NUMBER_LAST_PREFIX,
    AD.NUMBER_LAST as NUMBER_LAST,
    AD.NUMBER_LAST_SUFFIX as NUMBER_LAST_SUFFIX,

    SL.STREET_NAME as STREET_NAME,
    SL.STREET_CLASS_CODE as STREET_CLASS_CODE,
    SCA.NAME as STREET_CLASS_TYPE,
    SL.STREET_TYPE_CODE as STREET_TYPE_CODE,
    SL.STREET_SUFFIX_CODE as STREET_SUFFIX_CODE,
    SSA.NAME as STREET_SUFFIX_TYPE,

    L.LOCALITY_NAME as LOCALITY_NAME,

    ST.STATE_ABBREVIATION as STATE_ABBREVIATION,

    AD.POSTCODE as POSTCODE,

    ADG.LATITUDE as LATITUDE,
    ADG.LONGITUDE as LONGITUDE,
    GTA.NAME as GEOCODE_TYPE,

    AD.CONFIDENCE as CONFIDENCE,

    AD.ALIAS_PRINCIPAL as ALIAS_PRINCIPAL,
    AD.PRIMARY_SECONDARY as PRIMARY_SECONDARY,

    AD.LEGAL_PARCEL_ID as LEGAL_PARCEL_ID,

    AD.DATE_CREATED as DATE_CREATED

FROM
    ((ADDRESS_DETAIL AD 
    LEFT JOIN FLAT_TYPE_AUT FTA ON AD.FLAT_TYPE_CODE=FTA.CODE)
    LEFT JOIN LEVEL_TYPE_AUT LTA ON AD.LEVEL_TYPE_CODE=LTA.CODE)
    JOIN STREET_LOCALITY SL ON AD.STREET_LOCALITY_PID=SL.STREET_LOCALITY_PID
    LEFT JOIN STREET_SUFFIX_AUT SSA ON SL.STREET_SUFFIX_CODE=SSA.CODE
    LEFT JOIN STREET_CLASS_AUT SCA ON SL.STREET_CLASS_CODE=SCA.CODE 
    LEFT JOIN STREET_TYPE_AUT STA ON SL.STREET_TYPE_CODE=STA.CODE
    JOIN LOCALITY L ON AD.LOCALITY_PID = L.LOCALITY_PID
    JOIN ADDRESS_DEFAULT_GEOCODE ADG ON AD.ADDRESS_DETAIL_PID=ADG.ADDRESS_DETAIL_PID
    LEFT JOIN GEOCODE_TYPE_AUT GTA ON ADG.GEOCODE_TYPE_CODE=GTA.CODE
    LEFT JOIN GEOCODED_LEVEL_TYPE_AUT GLTA ON AD.LEVEL_GEOCODED_CODE=GLTA.CODE
    JOIN STATE ST ON L.STATE_PID=ST.STATE_PID

WHERE 
    AD.CONFIDENCE > -1;

额外说明 #

• 执行前需确认所有导入的G-NAF表中，原numeric类型字段已转换为Access的Number类型（可通过Access表设计视图修改）
• 若执行DROP VIEW时提示视图不存在，可跳过该语句直接执行CREATE VIEW

内容的提问来源于stack exchange，提问作者AussieAndy