With子句在Oracle数据库中不能正常工作

Question

我正在使用pypika构建一些查询。它曾经工作得很好，但我有一个问题，对oracle数据库的子查询。查询的内容如下

fake_query = Query().from_(my_table).where(my_table.ID == "12345").select(my_table.ID)
QN = AliasedQuery("fake_query_with")
query = (
Query()
.with_(fake_query, "fake_query_with")
.from_(QN)
.select(
QN.star. # problematic line
)
)
df_temp = claim_conn.read_dataframe(query.get_sql())

所以当我运行脚本时，我得到了一个错误:

DatabaseError: ORA-00904: "fake_query_with": invalid identifier

pypika查询按预期翻译为字符串

WITH fake_query_with AS 
(
SELECT "ID" 
FROM "MYTABLE" 
WHERE "ID"=12345
) 
SELECT "fake_query_with".* 
FROM fake_query_with

所以这个查询失败了，但是如果我用简单的*代替QN.star，它就可以工作了，但是当然我失去了别名的有趣用法。我知道这只是一个伪代码片段，但它只是为了演示错误。

Answer 1

正如@astentx在评论中所说，这看起来像是PyPika的一个bug。

你可以(可能)通过在你的代码中使名字大写来解决它-即"FAKE_QUERY_WITH"在两个地方。

QN = AliasedQuery("FAKE_QUERY_WITH")
query = (
Query()
.with_(fake_query, "FAKE_QUERY_WITH")
.from_(QN)
.select(
QN.star. # problematic line
)
)

那么生成的SQL应该如下所示:

WITH FAKE_QUERY_WITH AS 
(
SELECT "ID" 
FROM "MYTABLE" 
WHERE "ID"=12345
) 
SELECT "FAKE_QUERY_WITH".* 
FROM FAKE_QUERY_WITH

以及引号和未引号标识符之间的区别将没有意义。

显示修改的SQL工作-未在PyPika中测试，但希望这实际上是它将生成的…

Answer 2

我既不懂Python也不懂Pypika，但是-从Oracle的角度来看-这就是这里的问题所在:使用(邪恶!)双引号。

在Oracle中，默认情况下，所有的"标识符";(表名、列名、过程、函数…)以大写形式存储在数据字典中。然后，你可以以任何你想要的方式引用它们:大写，小写或混合大小写-没有区别，一切都可以工作。

但是，如果你用双引号括起名字，那么必须每次都使用双引号，跟在创建对象时使用的相同的字母大小写后面——否则，它将不起作用。

例如:

SQL> create table mytable as
2    select 12345 id,
3           'Littlefoot' "NamE"
4  from dual;
Table created.
SQL> select table_name from user_tables where table_name = 'MYTABLE';
TABLE_NAME
------------------------------
MYTABLE                            --> stored in UPPERCASE
SQL> select column_name from user_tab_columns where table_name = 'MYTABLE';
COLUMN_NAME
------------------------------ 
ID                                --> stored in UPPERCASE because there were no double quotes
NamE                              --> mixed case, as I enclosed the name into double quotes
SQL> select id from mytable;      --> reference it any way you want
ID
----------
12345
SQL> select name from mytable;    --> reference it exactly as it was created
select name from mytable
*
ERROR at line 1:
ORA-00904: "NAME": invalid identifier

SQL> select "NamE" from mytable;
NamE
----------
Littlefoot
SQL>

---

在你的例子中:

SQL> WITH fake_query_with AS         --> CTE named using lower case, but no double quotes
2  (
3     SELECT "ID"
4     FROM "MYTABLE"
5     WHERE "ID"=12345
6  )
7  SELECT "fake_query_with".*      --> double quotes, lower case
8  FROM fake_query_with;
SELECT "fake_query_with".*      
*
ERROR at line 7:
ORA-00904: "fake_query_with": invalid identifier

SQL>

如果你想让它工作，那么要么a)去掉双引号(如果可以的话;也许Py*需要它们，不能告诉)(这是我真诚的建议)或b):

处处使用双引号

a)无双引号

SQL> WITH
2     fake_query_with
3     AS
4        (SELECT id
5           FROM mytable
6          WHERE id = 12345)
7  SELECT fake_query_with.*
8    FROM fake_query_with;
ID
----------
12345
SQL>

b)双引号，处处

SQL> WITH
2     "fake_query_with"
3     AS
4        (SELECT "ID"
5           FROM "MYTABLE"
6          WHERE "ID" = 12345)
7  SELECT "fake_query_with".*
8    FROM "fake_query_with";
ID
----------
12345
SQL>

看看是否有帮助。