在LibreOffice Base SQL中查询2个字段的串联时出错 [英] Error in Querying Concatenation of 2 fields in LibreOffice Base SQL

问题描述

我为错误的术语表示歉意，我实际上不是程序员，而只是遇到问题的基本用户。

My apologies for wrong terminilogies, I am not actually a programmer, just a Base user confronted with a problem.

我在查询一个应该是两个单独表的两个字段的串联。我使用的是LibreOffice Base版本1：3.6.2，它具有默认的HSQL引擎。

I'm having trouble querying a column which is supposed to be a concatenation of two fields of two separate tables. I'm using LibreOffice Base Version 1:3.6.2 and it has the default HSQL engine.

我的两个表如下：

表1被命名为 Prefectures ，并具有以下字段：ID， Prefecture Name 和州
表2被命名为 Ward ，并具有以下字段： ID，姓氏 和地区ID（是引用表1的外键）。

Table 1 is named "Prefectures" and has the following fields: ID, "Prefecture Name", and "State" Table 2 is named "Ward" and has the following fields: ID, "Ward Name", and Prefecture ID which is a foreign key referencing table 1.

我想要什么我要查询的是这两个列：Ward.WardName和Prefecture.PrefectureName和Ward ID的串联 Ward Name，Prefecture Name

What I want my query to produce is these two colums: "Ward Name, Prefecture Name" as a concatenation of the Ward.WardName and Prefecture.PrefectureName, and the Ward ID

。如果我有一个名为宿雾的县和一个名为 Lahug的病房，该病房的病房ID为0，我希望查询在第1列显示 Lahug，Cebu，在第2列显示 0

For example. If I had a prefecture named "Cebu" and a ward named "Lahug" which has a ward ID of 0, I want the query to come up with "Lahug, Cebu" on column 1, and "0" in column 2

我使用的基础教程与实际的HSQL语言相比似乎具有一种修改的语言，至少基于我的程序员朋友的反应。我知道属性被称为 Table.Attribute ，但是在Base中，它们使用 Attribute ，或者，如果我没有记错的话，当需要指定原始表时，请 Table（ Attribute）。但是，我知道这种修改过的语言是有效的，因为我用它来创建了我的两个表。

The Base tutorial I used seems to have a modified language compared to the actual HSQL language, at least based on my programmer friends reaction. I know that attributes are referred to as Table.Attribute, for example, but in Base, they use "Attribute", or, if I am not mistaken, when one needs to specify the table of origin, "Table"("Attribute"). However, I know that this modified language works because I used it to create the my two tables.

无论如何，在危害我从本教程文档中学到的知识的情况下，我来了

Anyways, hazarding on what I learned from Base the tutorial document, I came up with:

SELECT "Ward Name" || ', ' || "Prefecture Name" AS "Wrd_Pref",
       "Ward ID"
FROM "Prefecture" INNER JOIN "Ward" ON "Prefecture" ("Prefecture ID") = "Ward" ("Prefecture ID")
;

出现的错误消息是：
无法加载数据内容。拒绝访问：语句[以上整个代码中的PREFECTURE]

And the error message that came up was: "The data content could not be loaded. Access is denied: PREFECTURE in statement [the whole code above]"

我怀疑这是由于我的语法错误而导致的误读。也许我在这种情况下使用假肢的猜测是错误的？如果是这样，为什么错误消息访问被拒绝？我检查了两个表的记录和sql代码，两个都非常正常。

I have a suspicion this is a misread due to wrong syntax on my part. Perhaps my guess on using perentheses in this case is wrong? If so, why is the error message "Access denied"? I checked both the records and the sql codes of the two tables, both were perfectly normal.

编辑：不，我不想只拆分两个字段。我需要将它们串联起来，因为我将它们用作我正在制作的表单的下拉列表中的列表项。

No, I don't want to just split the two fields. I need them concatenated as I am going to use them as list items in a dropdown list for a form I am making.

推荐答案

SQL查询就是这个。您说您的表被称为 Prefectures和 Ward，它们在FROM子句中使用。

The SQL query is this one. You say your tables are called "Prefectures" and "Ward", which are used in the FROM clause.

SELECT "Ward Name" || ', ' || "Prefecture Name" AS "Wrd_Pref", "Ward ID"
FROM "Prefectures" INNER JOIN "Ward" ON "Prefectures"."Prefecture ID" = "Ward"."Prefecture ID";

这篇关于在LibreOffice Base SQL中查询2个字段的串联时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！