On Wednesday, 18 April 2018 15:50:10 UTC+2, Mike Bayer wrote:
>
> 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.
>
> Hi Mike,
'hdbcli' is the official python driver and you are right that it is not
open-source. I also tried with pyhdb:
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, Supplier.s_name, Part.p_partkey).limit(100)
I still get an error similar to my 2nd update:
sqlalchemy.exc.DatabaseError: (pyhdb.exceptions.DatabaseError) invalid column
name: 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: line 5 col 211 (at pos 887)
[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 = (SELECT min(partsupp.ps_supplycost)
AS min_1 \nFROM part \nWHERE 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, supplier.s_name, part.p_partkey\n LIMIT
?']
[parameters: (15, '%BRASS', 'Europe', 'EUROPE', 100)] (Background on this error
at: http://sqlalche.me/e/4xp6)
Do you may be know what the reason could be? Can there be some error in my
query building using SQLAlchemy?
Thanks and Best Regards,
Sugandha
On Wed, Apr 18, 2018 at 6:34 AM, su-sa <sachdeva....@gmail.com <javascript:>>
> 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+...@googlegroups.com <javascript:>.
> > To post to this group, send email to sqlal...@googlegroups.com
> <javascript:>.
> > 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.
