what the heck is FILTER? I am googling for "hdbcli" and not getting
a clear signal back, it's not even on pypi, seems to be related to SAP
but I can find no source links or anything (there seems to be a
competing driver PyHDB that is more open source friendly). This
would be an issue with that SQLAlchemy dialect that I am not even able
to find on the web, you'd need to ask them.
On Wed, Apr 18, 2018 at 6:34 AM, su-sa <sachdeva.sugandh...@gmail.com> wrote:
>
>
> On Wednesday, 18 April 2018 12:18:51 UTC+2, su-sa wrote:
>>
>>
>>
>> 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
>
>
>
> I also did the following:
>
> 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 ==
> session.query(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').correlate(Partsupp, Supplier, Nation,
> Region)).order_by(Supplier.s_acctbal.desc(),
> Nation.n_name, Part.p_partkey).limit(100)
>
> But this gives me a new error:
>
> The column 'SUPPLIER.S_ACCTBAL' is invalid in the ORDER BY clause because
> the GROUP BY clause or an aggregation function does not contain it.
>
> --
> 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.
--
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.
