Hi everyone,
I've been trying quite a while to reproduce the following query:q2 = text(
"select top 100 s_acctbal, s_name, n_name, p_partkey, p_mfgr, s_address,
s_phone, s_comment from system.part, "
"system.supplier, system.partsupp, system.nation, system.region where
p_partkey = ps_partkey "
"and s_suppkey = ps_suppkey and p_size = 15 and p_type like '%BRASS' and
s_nationkey = n_nationkey "
"and n_regionkey = r_regionkey and r_name = 'EUROPE' and ps_supplycost =
(select min(ps_supplycost) "
"from system.partsupp, system.supplier, system.nation, system.region where
p_partkey = ps_partkey "
"and s_suppkey = ps_suppkey and s_nationkey = n_nationkey and n_regionkey =
r_regionkey and r_name = 'EUROPE')"
" order by s_acctbal desc, n_name, s_name, p_partkey;")
Thats what I have currently:
q2 = session.query(Supplier.s_acctbal, Supplier.s_name, Nation.n_name,
Part.p_partkey, Part.p_mfgr,
Supplier.s_address, Supplier.s_phone,
Supplier.s_comment).filter(Part.p_partkey == Partsupp.ps_partkey,
Supplier.s_suppkey== Partsupp.ps_suppkey, Part.p_size == 15,
Part.p_type.like('%BRASS'),
Supplier.s_nationkey == Nation.n_nationkey,
Nation.n_regionkey == Region.r_regionkey,
Region.r_name == 'Europe', Partsupp.ps_supplycost ==
func.min(Partsupp.ps_supplycost).filter(Part.p_partkey == Partsupp.ps_partkey,
Supplier.s_suppkey == Partsupp.ps_suppkey,
Supplier.s_nationkey == Nation.n_nationkey,
Nation.n_regionkey == Region.r_regionkey, Region.r_name ==
'EUROPE')).order_by(Supplier.s_acctbal.desc(),
Nation.n_name, Part.p_partkey).limit(100)
But I am getting the error:
sqlalchemy.exc.DBAPIError: (hdbcli.dbapi.Error) (257, 'sql syntax error:
incorrect syntax near "FILTER": line 3 col 301 (at pos 649)')
[SQL: u'SELECT supplier.s_acctbal AS supplier_s_acctbal, supplier.s_name AS
supplier_s_name, nation.n_name AS nation_n_name, part.p_partkey AS
part_p_partkey, part.p_mfgr AS part_p_mfgr,
supplier.s_address AS supplier_s_address, supplier.s_phone AS supplier_s_phone,
supplier.s_comment AS supplier_s_comment \nFROM supplier, nation, part,
partsupp, region \nWHERE part.p_partkey = partsupp.ps_partkey
AND supplier.s_suppkey = partsupp.ps_suppkey AND part.p_size = ? AND
part.p_type LIKE ? AND supplier.s_nationkey = nation.n_nationkey AND
nation.n_regionkey = region.r_regionkey AND region.r_name = ? AND
partsupp.ps_supplycost = min(partsupp.ps_supplycost) FILTER (WHERE
part.p_partkey = partsupp.ps_partkey AND supplier.s_suppkey =
partsupp.ps_suppkey AND supplier.s_nationkey = nation.n_nationkey AND
nation.n_regionkey = region.r_regionkey AND region.r_name = ?) ORDER BY
supplier.s_acctbal DESC, nation.n_name, part.p_partkey\n LIMIT ?'] [parameters:
(15, '%BRASS', 'Europe', 'EUROPE', 100)]
(Background on this error at: http://sqlalche.me/e/dbapi)
Which makes sense because inside the generated SQL Query, also the word filter
is present which is not recognised and because the nested select statement is
not generated.
Could anyone of you tell me what I doing wrong?
Sorry if that seems to be a stupid question.
Greetings,
Sugandha
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
