登录
当前位置： 首页 > 面试题库 >

MS Access中查询表达式中的语法错误（缺少运算符）

萧波峻
2023-03-14
问题内容：

我收到错误消息：

syntax error (missing operator) in query expression '((dbo_tblBidder.bidder_sale_id = dbo_tblSale.sale_id) AND (dbo_tblSale.sale_id IN (319))) LEFT JOIN
                  dbo_tblItem ON ((dbo_tblBidder.bidder_sale_id = dbo_tblItem.item_sale_id) AND (dbo_tblBidder.bidder_number = dbo_tblItem.item_bidder_number)'.

我以前遇到过此问题，并且知道它不喜欢我的括号设置，但是我所做的一切似乎都无法使MS Access感到满意。

SELECT     dbo_tblMailList.mail_FirstName, dbo_tblMailList.mail_LastName, dbo_tblMailList.mail_Address1, dbo_tblMailList.mail_Address2, 
                      dbo_tblMailList.mail_City, dbo_tblMailList.mail_State, dbo_tblMailList.mail_Zip, dbo_tblMailList.mail_Phone1, dbo_tblMailList.mail_Email1, 
                      dbo_tblMailList.mail_Comp_GenAm, dbo_tblBidder.bidder_number, SUM(dbo_tblItem.item_pr) AS SumOfitem_pr, SUM(dbo_tblItem.item_premium) 
                      AS SumOfitem_premium, dbo_tblBidder.bidder_type, dbo_tblSale.sale_id
FROM         (dbo_tblMailList LEFT JOIN
                      dbo_tblBidder ON ((dbo_tblMailList.mail_ID = dbo_tblBidder.bidder_mail_id) AND (dbo_tblBidder.bidder_sale_id IN (319)))) LEFT JOIN
                      dbo_tblSale ON ((dbo_tblBidder.bidder_sale_id = dbo_tblSale.sale_id) AND (dbo_tblSale.sale_id IN (319))) LEFT JOIN
                      dbo_tblItem ON ((dbo_tblBidder.bidder_sale_id = dbo_tblItem.item_sale_id) AND (dbo_tblBidder.bidder_number = dbo_tblItem.item_bidder_number))
GROUP BY dbo_tblMailList.mail_FirstName, dbo_tblMailList.mail_LastName, dbo_tblMailList.mail_Address1, dbo_tblMailList.mail_Address2, 
                      dbo_tblMailList.mail_City, dbo_tblMailList.mail_State, dbo_tblMailList.mail_Zip, dbo_tblMailList.mail_Phone1, dbo_tblMailList.mail_Email1, 
                      dbo_tblMailList.mail_Comp_GenAm, dbo_tblBidder.bidder_number, dbo_tblBidder.bidder_type, dbo_tblMailList.mail_Comp_Art, 
                      dbo_tblMailList.mail_Comp_IndArt, dbo_tblMailList.mail_Comp_Fire, dbo_tblMailList.mail_Comp_Ceramic, dbo_tblSale.sale_id
HAVING      (dbo_tblMailList.mail_Comp_GenAm = 1)

如果有人有任何想法，请告诉我。

谢谢，詹姆斯

编辑

使用FROM下面的Gareth子句，我现在有：

SELECT     dbo_tblMailList.mail_FirstName, dbo_tblMailList.mail_LastName, dbo_tblMailList.mail_Address1, dbo_tblMailList.mail_Address2, 
                      dbo_tblMailList.mail_City, dbo_tblMailList.mail_State, dbo_tblMailList.mail_Zip, dbo_tblMailList.mail_Phone1, dbo_tblMailList.mail_Email1, 
                      dbo_tblMailList.mail_Comp_GenAm, dbo_tblBidder.bidder_number, SUM(dbo_tblItem.item_pr) AS SumOfitem_pr, SUM(dbo_tblItem.item_premium) 
                      AS SumOfitem_premium, dbo_tblBidder.bidder_type, dbo_tblSale.sale_id
FROM    (
            (   dbo_tblMailList 
                LEFT JOIN dbo_tblBidder 
                    ON dbo_tblMailList.mail_ID = dbo_tblBidder.bidder_mail_id 
                    AND dbo_tblBidder.bidder_sale_id IN (319)
            )
            LEFT JOIN dbo_tblSale 
                ON dbo_tblBidder.bidder_sale_id = dbo_tblSale.sale_id
        )
        LEFT JOIN dbo_tblItem
            ON dbo_tblBidder.bidder_sale_id = dbo_tblItem.item_sale_id 
            AND dbo_tblBidder.bidder_number = dbo_tblItem.item_bidder_number
GROUP BY dbo_tblMailList.mail_FirstName, dbo_tblMailList.mail_LastName, dbo_tblMailList.mail_Address1, dbo_tblMailList.mail_Address2, 
                      dbo_tblMailList.mail_City, dbo_tblMailList.mail_State, dbo_tblMailList.mail_Zip, dbo_tblMailList.mail_Phone1, dbo_tblMailList.mail_Email1, 
                      dbo_tblMailList.mail_Comp_GenAm, dbo_tblBidder.bidder_number, dbo_tblBidder.bidder_type, dbo_tblMailList.mail_Comp_Art, 
                      dbo_tblMailList.mail_Comp_IndArt, dbo_tblMailList.mail_Comp_Fire, dbo_tblMailList.mail_Comp_Ceramic, dbo_tblSale.sale_id
HAVING      (dbo_tblMailList.mail_Comp_GenAm = 1)

但是我得到了错误 Extra ) in query expression ''.

我认为这可能与之有关AND dbo_tblBidder.bidder_sale_id IN (319)。如果将其更改为AND dbo_tblBidder.bidder_sale_id = 319，则会收到错误消息：

JOIN expression not supported.

它突出了dbo_tblBidder.bidder_sale_id = 319。

结论

这是我的最终工作代码：

SELECT     mail_ID, mail_FirstName, mail_LastName, mail_Address1, mail_Address2, 
                      mail_City, mail_State, mail_Zip, mail_Phone1, mail_Email1, 
                      mail_Comp_GenAm, SUM(SumOfitem_pr) AS SumOfitem_price, SUM(SumOfitem_premium) AS SumOfitem_premiums
FROM (
    SELECT     dbo_tblMailList.mail_ID, dbo_tblMailList.mail_FirstName, dbo_tblMailList.mail_LastName, dbo_tblMailList.mail_Address1, dbo_tblMailList.mail_Address2, 
                          dbo_tblMailList.mail_City, dbo_tblMailList.mail_State, dbo_tblMailList.mail_Zip, dbo_tblMailList.mail_Phone1, dbo_tblMailList.mail_Email1, 
                          dbo_tblMailList.mail_Comp_GenAm, SUM(dbo_tblItem.item_pr) AS SumOfitem_pr, SUM(dbo_tblItem.item_premium) 
                          AS SumOfitem_premium
    FROM    (
                (   dbo_tblMailList 
                    LEFT JOIN dbo_tblBidder 
                        ON dbo_tblMailList.mail_ID = dbo_tblBidder.bidder_mail_id 
                )
                LEFT JOIN dbo_tblSale 
                    ON dbo_tblBidder.bidder_sale_id = dbo_tblSale.sale_id
            )
            LEFT JOIN dbo_tblItem
                ON dbo_tblBidder.bidder_sale_id = dbo_tblItem.item_sale_id 
                AND dbo_tblBidder.bidder_number = dbo_tblItem.item_bidder_number
    WHERE      dbo_tblMailList.mail_Comp_GenAm = 1 AND dbo_tblBidder.bidder_sale_id IN (319)
    GROUP BY dbo_tblMailList.mail_ID, dbo_tblMailList.mail_FirstName, dbo_tblMailList.mail_LastName, dbo_tblMailList.mail_Address1, dbo_tblMailList.mail_Address2, 
                          dbo_tblMailList.mail_City, dbo_tblMailList.mail_State, dbo_tblMailList.mail_Zip, dbo_tblMailList.mail_Phone1, dbo_tblMailList.mail_Email1, 
                          dbo_tblMailList.mail_Comp_GenAm, dbo_tblMailList.mail_Comp_Art, 
                          dbo_tblMailList.mail_Comp_IndArt, dbo_tblMailList.mail_Comp_Fire, dbo_tblMailList.mail_Comp_Ceramic
    UNION
    SELECT     dbo_tblMailList.mail_ID, dbo_tblMailList.mail_FirstName, dbo_tblMailList.mail_LastName, dbo_tblMailList.mail_Address1, dbo_tblMailList.mail_Address2, 
                          dbo_tblMailList.mail_City, dbo_tblMailList.mail_State, dbo_tblMailList.mail_Zip, dbo_tblMailList.mail_Phone1, dbo_tblMailList.mail_Email1, 
                          dbo_tblMailList.mail_Comp_GenAm, 0 AS SumOfitem_pr, 0 
                          AS SumOfitem_premium
    FROM    (
                (   dbo_tblMailList 
                    LEFT JOIN dbo_tblBidder 
                        ON dbo_tblMailList.mail_ID = dbo_tblBidder.bidder_mail_id 
                )
                LEFT JOIN dbo_tblSale 
                    ON dbo_tblBidder.bidder_sale_id = dbo_tblSale.sale_id
            )
            LEFT JOIN dbo_tblItem
                ON dbo_tblBidder.bidder_sale_id = dbo_tblItem.item_sale_id 
                AND dbo_tblBidder.bidder_number = dbo_tblItem.item_bidder_number
    WHERE      dbo_tblMailList.mail_Comp_GenAm = 1
    GROUP BY dbo_tblMailList.mail_ID, dbo_tblMailList.mail_FirstName, dbo_tblMailList.mail_LastName, dbo_tblMailList.mail_Address1, dbo_tblMailList.mail_Address2, 
                          dbo_tblMailList.mail_City, dbo_tblMailList.mail_State, dbo_tblMailList.mail_Zip, dbo_tblMailList.mail_Phone1, dbo_tblMailList.mail_Email1, 
                          dbo_tblMailList.mail_Comp_GenAm, dbo_tblMailList.mail_Comp_Art, 
                          dbo_tblMailList.mail_Comp_IndArt, dbo_tblMailList.mail_Comp_Fire, dbo_tblMailList.mail_Comp_Ceramic
    )
GROUP BY mail_ID, mail_FirstName, mail_LastName, mail_Address1, mail_Address2, 
                      mail_City, mail_State, mail_Zip, mail_Phone1, mail_Email1, 
                      mail_Comp_GenAm, SumOfitem_price, SumOfitem_premiums

谢谢加雷斯！


问题答案：

在访问中，不能有多个联接而不用括号将它们分开，即

SELECT  *
FROM    A
        INNER JOIN B
            ON A.ID = B.AID
        INNER JOIN C
            ON B.ID = C.BID;

无效，它必须是：

SELECT  *
FROM    (A
        INNER JOIN B
            ON A.ID = B.AID)
        INNER JOIN C
            ON B.ID = C.BID;

因此，您的from子句将需要为：

FROM    (
            (   dbo_tblMailList 
                LEFT JOIN dbo_tblBidder 
                    ON dbo_tblMailList.mail_ID = dbo_tblBidder.bidder_mail_id 
                    AND dbo_tblBidder.bidder_sale_id IN (319)
            )
            LEFT JOIN dbo_tblSale 
                ON dbo_tblBidder.bidder_sale_id = dbo_tblSale.sale_id)
                AND dbo_tblSale.sale_id IN (319)
        )
        LEFT JOIN dbo_tblItem
            ON dbo_tblBidder.bidder_sale_id = dbo_tblItem.item_sale_id 
            AND dbo_tblBidder.bidder_number = dbo_tblItem.item_bidder_number

注意：我从联接中删除了所有不必要的括号，以减少查询中的混乱情况（不必将每个谓词都括在括号中），并夸大了制表符缩进以清楚显示括号在何处打开和关闭

编辑

我忘记了，您不能在Access的JOIN子句中应用常量表达式，您需要创建一个子选择，

dbo_tblMailList 
LEFT JOIN dbo_tblBidder 
    ON dbo_tblMailList.mail_ID = dbo_tblBidder.bidder_mail_id 
    AND dbo_tblBidder.bidder_sale_id IN (319)

你需要做

dbo_tblMailList AS m
LEFT JOIN (SELECT * FROM dbo_tblBidder WHERE bidder_sale_id IN (319)) AS b
    ON m.mail_ID = b.bidder_mail_id

因此，您的完整查询将是：

SELECT  m.mail_FirstName, 
        m.mail_LastName, 
        m.mail_Address1, 
        m.mail_Address2, 
        m.mail_City, 
        m.mail_State, 
        m.mail_Zip, 
        m.mail_Phone1, 
        m.mail_Email1, 
        m.mail_Comp_GenAm, 
        b.bidder_number, 
        SUM(i.item_pr) AS SumOfitem_pr, 
        SUM(i.item_premium) AS SumOfitem_premium, 
        b.bidder_type, 
        s.sale_id
FROM    (
            (   dbo_tblMailList AS m
                LEFT JOIN 
                (   SELECT  bidder_mail_id , bidder_number, bidder_type, bidder_sale_id
                    FROM    dbo_tblBidder 
                    WHERE   bidder_sale_id IN (319)
                ) AS b
                    ON m.mail_ID = b.bidder_mail_id 
            )
            LEFT JOIN dbo_tblSale AS s
                ON b.bidder_sale_id = s.sale_id
        )
        LEFT JOIN dbo_tblItem AS i
            ON b.bidder_sale_id = i.item_sale_id 
            AND b.bidder_number = i.item_bidder_number
GROUP BY 
        m.mail_FirstName, m.mail_LastName, m.mail_Address1, m.mail_Address2, m.mail_City, m.mail_State,
        m.mail_Zip, m.mail_Phone1, m.mail_Email1, m.mail_Comp_GenAm, b.bidder_number, b.bidder_type, s.sale_id;

（我使用短表别名来尝试压缩代码，而不是必须遵循的约定）



 类似资料：
  • （忽略语句）和（缺少表达式）错误

    我是数据库领域的初学者，我的任务是编写一个在嵌套表中添加记录的过程，这是。我写的sql文件： 当我跑步的时候。sql文件我收到一条警告消息（使用编译错误创建的过程）当我运行时（显示错误过程TT1_ajute_PERSON_DANS_ALBUM；）我得到错误（忽略语句）和（缺少表达式） 我已经在谷歌上搜索过这个问题，大多数情况下都是因为打字错误，但我不认为这是我的情况！以下是表（TT1_相册）和（T

  • ora：00936缺少表达式错误

    问题内容： 我收到以下查询的ORA：00936错误。请让我知道查询中的问题 问题答案： SELECT convert（DATE，r.created_dt）作为created_dt 问题在于错误使用了 CONVERT 函数。请参阅文档。 我猜您正在尝试转换数据类型，可以使用 TO_DATE 将字符串转换为日期。或者， TO_CHAR 反之亦然。

  • 方法[duplicate]中缺少返回语句错误

    } 当我运行这段代码时，我得到以下错误： 我该怎么做来解决这个问题呢？ 见下面的条件：给定一个从1到4（含）的数字，返回该数字的一个单词表示。例如，给定2，返回2。如果数字大于4，则返回短语“太大”。如果数字小于1，则返回短语too small。

  • java.sql.SQLException：ORA-00936：缺少表达式

    问题内容： 下面我正在创建表。 当我尝试更新数据库表时，这是下面的UPSERT_SQL查询，我总是在 获取java.sql.SQLException：ORA-00936：缺少表达式 。我检查了我的SQL，找不到找不到表达式的地方。下面的SQL有问题吗？ 从下面开始，我执行该UPSERT_SQL语句。 问题答案： 是的，SQL有点问题，这是您在 FROM dual 之前写了一个逗号。这导致Oracl

  • laravel sql查询中的语法错误

    我在寻找一个特定点周围的距离。 我在我的数据库中有这些项目，带有lat和long。 我想得到指定半径范围内的所有结果。 我就是这样得到的： 现在它给了我一个错误： SQLSTATE[42000]：语法错误或访问冲突：1064您的SQL语法有错误；查看与MySQL服务器版本对应的手册，以了解第4行（SQL:select*，（6371*acos（cos（radians（51,3））*cos（radia

  • Android中SQLite查询的语法错误

    我试图在Android中创建一个新的SQLite数据库。这是我的onCreate方法： 这会引发语法错误： android.database.sqlite.SQLiteExcture：在"AUTOINCRIMENT"附近：语法错误（代码1）：，编译时：创建表event_table（ID INTEGER PRIMARYKEY AUTOINCRIMENT， EVENTNAME TEXT， UniXTI

相关阅读

访问查询错误（``from子句中的语法错误''）缺少变量值时Django模板中的Javascript语法错误postgresql SELECT查询中的公用表表达式Java中缺少属性语法语法错误：预期的表达式，得到了'<'

相关文章

Spring SpEL表达式语言Go语言正则表达式：regexp包R语言运算符SQLite 表达式PostgreSQL 表达式

相关问答

java错误[重复]中缺少return语句}错误TS2349：无法调用类型缺少调用签名的表达式如何查找错误的中缀表达式？语法错误：“print”调用中缺少括号[重复]“简单”表达式语言-算术运算符？

相关工具

Quartz 表达式生成器正则表达式测试器算法(Rust语言描述)WizTools.org正则表达式测试工具PHP-ip查询

相关文档

java.util.regex 正则表达式 中文教程正则表达式正则表达式（regular expression）Java 正则表达式教程好用的中文速查表
快捷导航： 新手教程 算法原理 架构设计 Java进阶 数据库进阶 大厂专栏 面试经验 编程笔记 编程问答 所有专题 文档资料 工具软件 电子书籍 小牛导航
在线工具： 房贷计算器 个税计算器 Linux命令查询 Json格式化 正则表达式 颜色转换 AES加解密 SHA1加密 MD5加密 毒鸡汤 字数统计 随机密码生成 进制转换 Base64编解码 励志句子
Copyright © 2019-2024 小牛知识库@xnip.cn. All Rights Reserved.