SQL 语法与 MS Access 的兼容性 [已关闭]-解网

问：

想改进这个问题吗？更新问题，使其仅通过编辑这篇文章来关注一个问题。

9天前关闭。

改进此问题

你能帮帮我吗？这是为了创建G-NAF数据（澳大利亚地址数据库，https://data.gov.au/data/dataset/geocoded-national-address-file-g-naf 公开）的视图。我设法将所有表都放入，但无法执行VIEW。例如，我意识到在MS Access SQL中不能使用“数字”，而必须使用“数字”。我将不胜感激，我需要将 SQL 语法转换为 MS Access 语法，但我不知道如何。

CREATE OR REPLACE 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

sql sql-server ms-access

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
                                        ) INNER 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
                        ) INNER JOIN LOCALITY L ON AD.LOCALITY_PID = L.LOCALITY_PID
                    ) INNER 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
        ) INNER JOIN STATE ST ON L.STATE_PID = ST.STATE_PID
    )
WHERE AD.CONFIDENCE > - 1

正如 HansUp 在下面的评论中指出的那样，Access 还要求指定“INNER”（即您不能只使用“JOIN”）——所以现在已经包含在上面了。

上面看到的格式不是必需的。

正如 AussieAndy 所指出的，Access 不理解“OR REPLACE”，因此需要从第一行中删除。视图代码的其余部分应按原样工作，但您可以删除所有列别名。

非常感谢！！！我注意到要使语法起作用，我需要删除 OR REPLACE 。但即便如此，MS Access也返回了错误“JOIN操作中的语法错误”。我以为我会通过在语法中每个 JOIN 之前删除括号来摆脱它。但它没有多大帮助......有什么进一步的建议吗？顺便说一句：在“文件”->“选项”->对象设计器中，我勾选了SQL Server Compatibel语法（ANSI 92）。不确定这是否有帮助......

1赞 Adrian Maxwell 11/11/2023

是的，“或替换”在 Access 中不起作用，我忘记了。也许您应该尝试通过 gui 构建此视图？

2赞 HansUp 11/11/2023

Access SQL 不接受仅作为同义词---您必须显式指定联接类型。JOININNER JOIN

1赞 AussieAndy 11/12/2023

再次感谢你们!!!非常感谢您的帮助!!一旦我替换了查询，运行完美，我得到了数据库的完整视图......JOININNER JOIN

2赞 Adrian Maxwell 11/12/2023

g'day（澳大利亚语到澳大利亚语）我已经调整了答案，包括你和@HansUp的笔记 - 因为它们肯定会增加答案。谢谢。

上一个：访问查询以提取每个客户/SKU 组合的最新价格 [已关闭]

下一个：简单查询返回“您尝试将 Null 值分配给不是 Variant 数据类型的变量”错误

SQL 语法与 MS Access 的兼容性 [已关闭]

SQL syntax compatibility with MS Access [closed]

评论

评论