You are viewing a plain text version of this content. The canonical link for it is here.
Posted to torque-user@db.apache.org by Graham Leggett <mi...@sharp.fm> on 2008/10/06 14:17:00 UTC
ORDER BY and torque generated column names
Hi all,
I am struggling with a database query that is giving an error about the
presence of specific column names, and as torque takes control over the
column names I am in the dark as to what torque is or isn't doing.
The query generated by torque attempts to order results by two columns,
like this:
SELECT DISTINCT FROM a, b WHERE a.policy_id=b.policy_id ORDER BY
b.underwriting_year DESC, a.policydetail_id DESC;
Being printed out from a Criteria object, the column names are missing,
as torque fills these in for you.
Postgres fails on this query with the error "ERROR: for SELECT DISTINCT,
ORDER BY expressions must appear in select list"
The trouble is, I do not know what the select list is, because torque
fills that bit in during the query - I have no mechanism (that I know
of) to see the final query that hits the database.
If I use a select list of "*" (as in "SELECT DISTINCT * FROM...") the
query works and postgres is happy, so the query itself seems fine, it
seems that torque is leaving out the columns specified in orderby.
Does torque give the user any control over the columns being selected in
the query?
Am I doing something wrong in the following code?
criteria.addJoin(BPeer.POLICYDETAIL_ID, APeer.POLICYDETAIL_ID);
criteria.addDescendingOrderByColumn(BPeer.UNDERWRITING_YEAR);
criteria.addDescendingOrderByColumn(APeer.POLICYDETAIL_ID);
APeer.doSelect(criteria);
Regards,
Graham
--
RE: Unsigned integers
Posted by Greg Monroe <mo...@dukece.com>.
FWIW, I seem to remember jdbc task issues with getting foreign
key constraints, indices, and recognizing autoincrement columns.
Plus, I think there is a fair amount of variation in what you get
depending on the DB you're connecting to. I've always considered
the XML from this as a starting point to avoid typing a lot ;) but
not the end product. But then I use my XML to generate the SQL
statements for replicating my Application DBs, so it has to be full
featured with all that stuff.
That said, you might want to take a look at the DDL UtilS project:
http://db.apache.com/ddlutils
This is a "sister" project to torque that uses the same schema
(with some exceptions around external-schema tags). They have
been the focus of getting the jdbc task more robust. In fact,
the Torque 4.0 design discussions talked about converting the
tasks relating to DB creation, etc to use the ddlutils project.
FWIW, they have a nice set of easy to use classes to do a lot of
the same tasks via code. They have a nice feature that can compare
an existing DB to an XML schema (or another DB) and generate the
SQL code needed to update the destination DB to match an source
schema/DB. I've been using this one to generate version
update SQL scripts. NOTE that this class is kind of "militant"
in that if there are table NOT in the XML, it will generate
drop statements for them. But with a little quick manual editing,
I can produce an upgrade script in about 30 minutes or less,
including testing.
Finally, you asked about Torque 4.0 features, etc. Here are a
couple of resources that you might want to check out:
http://wiki.apache.org/db-torque/NextRelease
This is the result of an extended discussion about Torque 4.0 that
took place starting in Nov 2006 (and on into early Feb or so). If
you truly want to understand what the Wiki is talking about, I
suggest going thru the torque-dev list archives and reading the
threads related to Torque 4.0.
> -----Original Message-----
> From: Thomas Fischer [mailto:fischer@seitenbau.net]
> Sent: Wednesday, December 03, 2008 9:46 AM
> To: Apache Torque Users List
> Subject: Re: Unsigned integers
>
> > > IMHO torque:jdbc is no good as a totally automatic way to create a
> > > schema.xml. It is good as a first guess, but nothing more, because
> the
>
> > > informations from the jdbc driver are not complete enough to extract
> a
>
> > > good schema.
> > > If it worked for you so far, you were lucky.
> > >
> > Or maybe Torque have lots of features I don't know about.
> >
> > But what kind of features in my database could be a problem for
> torque:jdbc?
> >
> > I love torque, because I can just define my database in sql and then
> run
>
> > maven torque:jdbc to get a schema.xml file, that maps
> > all the tables to classes, with primary and foreign keys, and allow me
> > to retrieve data from the database.
>
> The problem might be that the mapping is different from what you expect.
> I
> do not have an example ready because I do not use torque:jdbc myself, but
> searching the mailing list should find some complaints.
>
> Thomas
DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: Unsigned integers
Posted by Thomas Fischer <fi...@seitenbau.net>.
> > IMHO torque:jdbc is no good as a totally automatic way to create a
> > schema.xml. It is good as a first guess, but nothing more, because the
> > informations from the jdbc driver are not complete enough to extract a
> > good schema.
> > If it worked for you so far, you were lucky.
> >
> Or maybe Torque have lots of features I don't know about.
>
> But what kind of features in my database could be a problem for
torque:jdbc?
>
> I love torque, because I can just define my database in sql and then run
> maven torque:jdbc to get a schema.xml file, that maps
> all the tables to classes, with primary and foreign keys, and allow me
> to retrieve data from the database.
The problem might be that the mapping is different from what you expect. I
do not have an example ready because I do not use torque:jdbc myself, but
searching the mailing list should find some complaints.
Thomas
Re: Unsigned integers
Posted by Martin Tilsted <ti...@daimi.au.dk>.
Thomas Fischer wrote:
>>> That said, the best bet would be to manually define the column
>>> type to be an UNSIGNED INTEGER in the DB and then use BIGINT
>>> as the type in the schema. This will make the Record object
>>> use the long type and avoid having large binary int values
>>> become negative.
>>>
>>>
>> That sound like a good solution. The only small problem is that I
>> generate the schema.xml directly from the database,
>> so next time i change the database and need a new schema.xml file, I
>> will have to change the schema.xml file again.
>>
>
> IMHO torque:jdbc is no good as a totally automatic way to create a
> schema.xml. It is good as a first guess, but nothing more, because the
> informations from the jdbc driver are not complete enough to extract a
> good schema.
> If it worked for you so far, you were lucky.
>
Or maybe Torque have lots of features I don't know about.
But what kind of features in my database could be a problem for torque:jdbc?
I love torque, because I can just define my database in sql and then run
maven torque:jdbc to get a schema.xml file, that maps
all the tables to classes, with primary and foreign keys, and allow me
to retrieve data from the database.
Martin
>> Oh and to be total off-topic: Does anyone have a description/status for
>> "torque 4.0" I am thinking about things such as
>> "New features","Status"(Is anyone really working on it at all) and
>> "expected release date").
>>
>
> Hm, yes, "we are thinking about it" is a good description of the status.
> The problem is that everybody was very busy with other stuff the last half
> of a year. But now with the economy crisis coming, probably there will be
> more time in the near future.
>
> Thomas
>
Re: Unsigned integers
Posted by Thomas Fischer <fi...@seitenbau.net>.
> > That said, the best bet would be to manually define the column
> > type to be an UNSIGNED INTEGER in the DB and then use BIGINT
> > as the type in the schema. This will make the Record object
> > use the long type and avoid having large binary int values
> > become negative.
> >
> That sound like a good solution. The only small problem is that I
> generate the schema.xml directly from the database,
> so next time i change the database and need a new schema.xml file, I
> will have to change the schema.xml file again.
IMHO torque:jdbc is no good as a totally automatic way to create a
schema.xml. It is good as a first guess, but nothing more, because the
informations from the jdbc driver are not complete enough to extract a
good schema.
If it worked for you so far, you were lucky.
> Oh and to be total off-topic: Does anyone have a description/status for
> "torque 4.0" I am thinking about things such as
> "New features","Status"(Is anyone really working on it at all) and
> "expected release date").
Hm, yes, "we are thinking about it" is a good description of the status.
The problem is that everybody was very busy with other stuff the last half
of a year. But now with the economy crisis coming, probably there will be
more time in the near future.
Thomas
Re: Unsigned integers
Posted by Martin Tilsted <ti...@daimi.au.dk>.
> That said, the best bet would be to manually define the column
> type to be an UNSIGNED INTEGER in the DB and then use BIGINT
> as the type in the schema. This will make the Record object
> use the long type and avoid having large binary int values
> become negative.
>
That sound like a good solution. The only small problem is that I
generate the schema.xml directly from the database,
so next time i change the database and need a new schema.xml file, I
will have to change the schema.xml file again.
But I guess I will just make a small script, to do a string replace on
the schema.xml file, after running maven torque:jdbc so it's not a big
problem :}
And thanks for the help.
Oh and to be total off-topic: Does anyone have a description/status for
"torque 4.0" I am thinking about things such as
"New features","Status"(Is anyone really working on it at all) and
"expected release date").
Martin
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: Unsigned integers
Posted by Greg Monroe <mo...@dukece.com>.
The UNSIGNED type is not in the DTD because it is not part
of any SQL Standard. The SQL standard for this type of data
is to use one of the following column types: BIT, BINARY, or
VARBINARY column type (with possible CHAR and DECIMAL options).
Unfortunately, it looks like Village doesn't properly support
BIT (it assumes a 1 byte Boolean.. need to fix this now that
we have control of Village...). And BINARY and VARBINARY
are mapped to BLOBS which aren't very efficient.
That said, the best bet would be to manually define the column
type to be an UNSIGNED INTEGER in the DB and then use BIGINT
as the type in the schema. This will make the Record object
use the long type and avoid having large binary int values
become negative.
As to ensuring the proper values get placed into this, you can
either depend on JDBC or SQL to throw an exception if the value
is too large (e.g. a negative number of type long SHOULD be
considered as too many bits). Or you can just add some checks
to the set<Column>(long) method in the stub.
Hope this makes sense..
> -----Original Message-----
> From: Martin Tilsted [mailto:tiller@daimi.au.dk]
> Sent: Saturday, November 29, 2008 11:52 AM
> To: Apache Torque Users List
> Subject: Unsigned integers
>
> How do i handle unsigned integers with torque (Using MySql 5.0)?
>
> I use maven torque:jdbc to generate my schema.xml file, but i have a
> field(Ip) that is an unsigned int in the database,
> but that generate a
> <column name="Ip" type="INTEGER"/>
> in the schema file.
> So I have 2 questions:
>
> 1: How do I get maven torque:jdbc to generate a schema for an unsigned
> int? Is this even possible? I could not find the documentation for the
> schema.xml format.
>
> 2: If i keep the signed integer version in my schema, but the field is
> unsigned in the database, what happens if call setInt() with a negative
> value.?
>
> Martin
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
> For additional commands, e-mail: torque-user-help@db.apache.org
>
DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.
Unsigned integers
Posted by Martin Tilsted <ti...@daimi.au.dk>.
How do i handle unsigned integers with torque (Using MySql 5.0)?
I use maven torque:jdbc to generate my schema.xml file, but i have a
field(Ip) that is an unsigned int in the database,
but that generate a
<column name="Ip" type="INTEGER"/>
in the schema file.
So I have 2 questions:
1: How do I get maven torque:jdbc to generate a schema for an unsigned
int? Is this even possible? I could not find the documentation for the
schema.xml format.
2: If i keep the signed integer version in my schema, but the field is
unsigned in the database, what happens if call setInt() with a negative
value.?
Martin
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: how can i write torque Group By Query
Posted by Sasikumar Natarajan <ns...@yahoo.com>.
Thanks for your response........ if i have complex join query, i have been implemented to SQL VIEW. this issues i tried the following way.
I did like the following way,
Step 1: I introduced one variable as well as set/get methods (noOfEmployee) in the Department.java.
Step 2: i have been developed the following torque query . this is working for me.
Criteria criteria = new Criteria();
criteria.addSelectColumn(DepartmentPeer.DEPT_NUMBER);
criteria.addSelectColumn(DepartmentPeer.DEPT_NAME);
criteria.addSelectColumn(" COUNT(DEPARTMENTS.DEPT_CODE) NUMBER_OF_EMPLOYEES ");
criteria.addGroupByColumn(DepartmentPeer.DEPT_NUMBER);
criteria.addGroupByColumn(DepartmentPeer.DEPT_NAME);
criteria.addJoin(EmployeePeer.EMP_DEPT_NUMBER, DepartmentPeer.DEPT_NUMBER);
List deptList = DepartmentPeer.doSelectVillageRecords(criteria);
Thanks
Sasikumar
--- On Mon, 1/12/08, Greg Monroe <mo...@dukece.com> wrote:
From: Greg Monroe <mo...@dukece.com>
Subject: RE: how can i write torque Group By Query
To: "'Apache Torque Users List'" <to...@db.apache.org>
Date: Monday, 1 December, 2008, 3:29 PM
Sorry for the delay... Last week was the Thanksgiving
holiday in the US...
The easy way to do this (once it's set up), would be to use the new
SummaryHelper class. However, this was not finished in time to make
the current distribution and is only available in the SVN repository.
Here is a link to the email talking about this:
http://mail-archives.apache.org/mod_mbox/db-torque-dev/200803.mbox/%3C8F5843B903F59D4C8C6806BB49A3911906016692@dukece-mail3.dukece.com%3E
You can also look at the Javadoc comments of this class to see if you
want to build/use a snapshot Runtime. AFAIK, there have been no other
major submits to the 3.x build tree (since 4.0 is separate). So, I'd
say that it would be production ready (I'm using it that way now).
An alternative way to do this is via Torque is to use the BasePeer
executeQuery methods. This is a lower level way to use Torque to
execute queries that don't fit into the record object model. It
returns a set of Record objects that are a lot like the standard JDBC
classes with getValue methods for getting data by ordinal or columnName.
Finally, if this particular query is something you do a lot and still
want the convenience of a Torque record, you could create an SQL view
and then create a pseudo-table in the schema (use the noSql option) to
match the columns the view will create. It may not fit your need but I
thought I'd mention it.
> -----Original Message-----
> From: Sasikumar Natarajan [mailto:nsasi_cts@yahoo.com]
> Sent: Monday, December 01, 2008 12:30 AM
> To: Apache Torque Users List
> Subject: Re: how can i write torque Group By Query
>
> Hello ALL,
> i have been developed a oracle query for getting
employees
> count in each department as given below. This query i want to change
> Torque Query as Similar functionality by using Peer Class Joint. Can u
> Please help me for converting by using Peer Class Joint.
>
> SELECT DEPT_NUMBER, DEPT_NAME, COUNT(DEPT_NUMBER) NUMBER_OF_EMPLOYEES
> FROM EMPLOYEES EMP, DEPARTMENTS DEPT
> WHERE EMP.EMP_DEPT_NUMBER = DEPT.DEPT_NUMBER GROUP BY DEPT_NUMBER,
> DEPT_NAME
>
> Thanks In Advance
> Sasikumar
>
> --- On Wed, 26/11/08, Sasikumar Natarajan <ns...@yahoo.com>
wrote:
>
> From: Sasikumar Natarajan <ns...@yahoo.com>
> Subject: how can i write torque Group By Query
> To: "Apache Torque Users List"
<to...@db.apache.org>
> Date: Wednesday, 26 November, 2008, 12:39 PM
>
> Hi All
>
> The following query i need to change by using torque query.
>
> SELECT DEPT_NUMBER, DEPT_NAME, COUNT(DEPT_NUMBER) NUMBER_OF_EMPLOYEES
> FROM
> EMPLOYEES EMP, DEPARTMENTS DEPT
> WHERE EMP.EMP_DEPT_NUMBER = DEPT.DEPT_NUMBER GROUP BY DEPT_NUMBER,
> DEPT_NAME
>
> i did the following by using torque query. but this is wrong.
>
> Please help me to correct the following query.
>
> Criteria criteria = new Criteria();
> criteria.addSelectColumn(DepartmentPeer.DEPT_NUMBER);
> criteria.addSelectColumn(DepartmentPeer.DEPT_NAME);
>
> criteria.addGroupByColumn(DepartmentPeer.DEPT_NUMBER);
> criteria.addGroupByColumn(DepartmentPeer.DEPT_NAME);
>
> criteria.addJoin(EmployeePeer.EMP_DEPT_NUMBER,
> DepartmentPeer.DEPT_NUMBER);
> List deptList = DepartmentPeer.doSelect(criteria);
>
> Thanks in Advance
> Sasikumar
>
>
> Unlimited freedom, unlimited storage. Get it now, on
> http://help.yahoo.com/l/in/yahoo/mail/yahoomail/tools/tools-08.html/
>
>
> Add more friends to your messenger and enjoy! Go to
> http://messenger.yahoo.com/invite/
DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.
Cricket on your mind? Visit the ultimate cricket website. Enter http://beta.cricket.yahoo.com
RE: how can i write torque Group By Query
Posted by Greg Monroe <mo...@dukece.com>.
Sorry for the delay... Last week was the Thanksgiving
holiday in the US...
The easy way to do this (once it's set up), would be to use the new
SummaryHelper class. However, this was not finished in time to make
the current distribution and is only available in the SVN repository.
Here is a link to the email talking about this:
http://mail-archives.apache.org/mod_mbox/db-torque-dev/200803.mbox/%3C8F5843B903F59D4C8C6806BB49A3911906016692@dukece-mail3.dukece.com%3E
You can also look at the Javadoc comments of this class to see if you
want to build/use a snapshot Runtime. AFAIK, there have been no other
major submits to the 3.x build tree (since 4.0 is separate). So, I'd
say that it would be production ready (I'm using it that way now).
An alternative way to do this is via Torque is to use the BasePeer
executeQuery methods. This is a lower level way to use Torque to
execute queries that don't fit into the record object model. It
returns a set of Record objects that are a lot like the standard JDBC
classes with getValue methods for getting data by ordinal or columnName.
Finally, if this particular query is something you do a lot and still
want the convenience of a Torque record, you could create an SQL view
and then create a pseudo-table in the schema (use the noSql option) to
match the columns the view will create. It may not fit your need but I
thought I'd mention it.
> -----Original Message-----
> From: Sasikumar Natarajan [mailto:nsasi_cts@yahoo.com]
> Sent: Monday, December 01, 2008 12:30 AM
> To: Apache Torque Users List
> Subject: Re: how can i write torque Group By Query
>
> Hello ALL,
> i have been developed a oracle query for getting employees
> count in each department as given below. This query i want to change
> Torque Query as Similar functionality by using Peer Class Joint. Can u
> Please help me for converting by using Peer Class Joint.
>
> SELECT DEPT_NUMBER, DEPT_NAME, COUNT(DEPT_NUMBER) NUMBER_OF_EMPLOYEES
> FROM EMPLOYEES EMP, DEPARTMENTS DEPT
> WHERE EMP.EMP_DEPT_NUMBER = DEPT.DEPT_NUMBER GROUP BY DEPT_NUMBER,
> DEPT_NAME
>
> Thanks In Advance
> Sasikumar
>
> --- On Wed, 26/11/08, Sasikumar Natarajan <ns...@yahoo.com> wrote:
>
> From: Sasikumar Natarajan <ns...@yahoo.com>
> Subject: how can i write torque Group By Query
> To: "Apache Torque Users List" <to...@db.apache.org>
> Date: Wednesday, 26 November, 2008, 12:39 PM
>
> Hi All
>
> The following query i need to change by using torque query.
>
> SELECT DEPT_NUMBER, DEPT_NAME, COUNT(DEPT_NUMBER) NUMBER_OF_EMPLOYEES
> FROM
> EMPLOYEES EMP, DEPARTMENTS DEPT
> WHERE EMP.EMP_DEPT_NUMBER = DEPT.DEPT_NUMBER GROUP BY DEPT_NUMBER,
> DEPT_NAME
>
> i did the following by using torque query. but this is wrong.
>
> Please help me to correct the following query.
>
> Criteria criteria = new Criteria();
> criteria.addSelectColumn(DepartmentPeer.DEPT_NUMBER);
> criteria.addSelectColumn(DepartmentPeer.DEPT_NAME);
>
> criteria.addGroupByColumn(DepartmentPeer.DEPT_NUMBER);
> criteria.addGroupByColumn(DepartmentPeer.DEPT_NAME);
>
> criteria.addJoin(EmployeePeer.EMP_DEPT_NUMBER,
> DepartmentPeer.DEPT_NUMBER);
> List deptList = DepartmentPeer.doSelect(criteria);
>
> Thanks in Advance
> Sasikumar
>
>
> Unlimited freedom, unlimited storage. Get it now, on
> http://help.yahoo.com/l/in/yahoo/mail/yahoomail/tools/tools-08.html/
>
>
> Add more friends to your messenger and enjoy! Go to
> http://messenger.yahoo.com/invite/
DukeCE Privacy Statement:
Please be advised that this e-mail and any files transmitted with
it are confidential communication or may otherwise be privileged or
confidential and are intended solely for the individual or entity
to whom they are addressed. If you are not the intended recipient
you may not rely on the contents of this email or any attachments,
and we ask that you please not read, copy or retransmit this
communication, but reply to the sender and destroy the email, its
contents, and all copies thereof immediately. Any unauthorized
dissemination, distribution or copying of this communication is
strictly prohibited.
Re: how can i write torque Group By Query
Posted by Sasikumar Natarajan <ns...@yahoo.com>.
Hello ALL,
i have been developed a oracle query for getting employees count in each department as given below. This query i want to change Torque Query as Similar functionality by using Peer Class Joint. Can u Please help me for converting by using Peer Class Joint.
SELECT DEPT_NUMBER, DEPT_NAME, COUNT(DEPT_NUMBER) NUMBER_OF_EMPLOYEES FROM EMPLOYEES EMP, DEPARTMENTS DEPT
WHERE EMP.EMP_DEPT_NUMBER = DEPT.DEPT_NUMBER GROUP BY DEPT_NUMBER, DEPT_NAME
Thanks In Advance
Sasikumar
--- On Wed, 26/11/08, Sasikumar Natarajan <ns...@yahoo.com> wrote:
From: Sasikumar Natarajan <ns...@yahoo.com>
Subject: how can i write torque Group By Query
To: "Apache Torque Users List" <to...@db.apache.org>
Date: Wednesday, 26 November, 2008, 12:39 PM
Hi All
The following query i need to change by using torque query.
SELECT DEPT_NUMBER, DEPT_NAME, COUNT(DEPT_NUMBER) NUMBER_OF_EMPLOYEES FROM
EMPLOYEES EMP, DEPARTMENTS DEPT
WHERE EMP.EMP_DEPT_NUMBER = DEPT.DEPT_NUMBER GROUP BY DEPT_NUMBER, DEPT_NAME
i did the following by using torque query. but this is wrong.
Please help me to correct the following query.
Criteria criteria = new Criteria();
criteria.addSelectColumn(DepartmentPeer.DEPT_NUMBER);
criteria.addSelectColumn(DepartmentPeer.DEPT_NAME);
criteria.addGroupByColumn(DepartmentPeer.DEPT_NUMBER);
criteria.addGroupByColumn(DepartmentPeer.DEPT_NAME);
criteria.addJoin(EmployeePeer.EMP_DEPT_NUMBER, DepartmentPeer.DEPT_NUMBER);
List deptList = DepartmentPeer.doSelect(criteria);
Thanks in Advance
Sasikumar
Unlimited freedom, unlimited storage. Get it now, on
http://help.yahoo.com/l/in/yahoo/mail/yahoomail/tools/tools-08.html/
Add more friends to your messenger and enjoy! Go to http://messenger.yahoo.com/invite/
how can i write torque Group By Query
Posted by Sasikumar Natarajan <ns...@yahoo.com>.
Hi All
The following query i need to change by using torque query.
SELECT DEPT_NUMBER, DEPT_NAME, COUNT(DEPT_NUMBER) NUMBER_OF_EMPLOYEES FROM EMPLOYEES EMP, DEPARTMENTS DEPT
WHERE EMP.EMP_DEPT_NUMBER = DEPT.DEPT_NUMBER GROUP BY DEPT_NUMBER, DEPT_NAME
i did the following by using torque query. but this is wrong.
Please help me to correct the following query.
Criteria criteria = new Criteria();
criteria.addSelectColumn(DepartmentPeer.DEPT_NUMBER);
criteria.addSelectColumn(DepartmentPeer.DEPT_NAME);
criteria.addGroupByColumn(DepartmentPeer.DEPT_NUMBER);
criteria.addGroupByColumn(DepartmentPeer.DEPT_NAME);
criteria.addJoin(EmployeePeer.EMP_DEPT_NUMBER, DepartmentPeer.DEPT_NUMBER);
List deptList = DepartmentPeer.doSelect(criteria);
Thanks in Advance
Sasikumar
Unlimited freedom, unlimited storage. Get it now, on http://help.yahoo.com/l/in/yahoo/mail/yahoomail/tools/tools-08.html/
Re: ORDER BY and torque generated column names
Posted by Malcolm Kendall <mi...@blueyonder.co.uk>.
Hi Graham,
Try using the addSelectColumn() method on APeer class, and BPeer class.
Regards,
Malcolm Kendall, NetVisionIT Ltd.
Graham Leggett wrote:
> Hi all,
>
> I am struggling with a database query that is giving an error about
> the presence of specific column names, and as torque takes control
> over the column names I am in the dark as to what torque is or isn't
> doing.
>
> The query generated by torque attempts to order results by two
> columns, like this:
>
> SELECT DISTINCT FROM a, b WHERE a.policy_id=b.policy_id ORDER BY
> b.underwriting_year DESC, a.policydetail_id DESC;
>
> Being printed out from a Criteria object, the column names are
> missing, as torque fills these in for you.
>
> Postgres fails on this query with the error "ERROR: for SELECT
> DISTINCT, ORDER BY expressions must appear in select list"
>
> The trouble is, I do not know what the select list is, because torque
> fills that bit in during the query - I have no mechanism (that I know
> of) to see the final query that hits the database.
>
> If I use a select list of "*" (as in "SELECT DISTINCT * FROM...") the
> query works and postgres is happy, so the query itself seems fine, it
> seems that torque is leaving out the columns specified in orderby.
>
> Does torque give the user any control over the columns being selected
> in the query?
>
> Am I doing something wrong in the following code?
>
> criteria.addJoin(BPeer.POLICYDETAIL_ID, APeer.POLICYDETAIL_ID);
> criteria.addDescendingOrderByColumn(BPeer.UNDERWRITING_YEAR);
> criteria.addDescendingOrderByColumn(APeer.POLICYDETAIL_ID);
> APeer.doSelect(criteria);
>
> Regards,
> Graham
> --
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
Re: ORDER BY and torque generated column names
Posted by Thomas Vandahl <tv...@apache.org>.
Manaster, Carl wrote:
>> APeer.doSelect(criteria);
>
> I think the issue is that when you ask APeer to select, it will select
> only the fields from a; it's telling you that you can't ORDER BY
> b.underwriting_year because that is not one of the columns in a. You
> could specify with addSelectColumn() exactly which columns you want to
> select and use BasePeer.doSelect() instead of APeer.doSelect, but that
> will give you com.workingdogs.village Records rather than a's, if that
> matters.
doSelect() checks if there are already columns added and if so skips
that step. So to make Postgres happy *and* keep the Peer features you
could use (order is important!)
APeer.addSelectColumns(criteria);
BPeer.addSelectColumns(criteria);
List results = APer.doSelect(criteria);
Bye, Thomas.
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org
RE: ORDER BY and torque generated column names
Posted by "Manaster, Carl" <Ca...@ACTIVX.com>.
Hi, Graham,
> Am I doing something wrong in the following code?
>
> criteria.addJoin(BPeer.POLICYDETAIL_ID,
> APeer.POLICYDETAIL_ID);
> criteria.addDescendingOrderByColumn(BPeer.UNDERWRITING_YEAR);
> criteria.addDescendingOrderByColumn(APeer.POLICYDETAIL_ID);
> APeer.doSelect(criteria);
I think the issue is that when you ask APeer to select, it will select
only the fields from a; it's telling you that you can't ORDER BY
b.underwriting_year because that is not one of the columns in a. You
could specify with addSelectColumn() exactly which columns you want to
select and use BasePeer.doSelect() instead of APeer.doSelect, but that
will give you com.workingdogs.village Records rather than a's, if that
matters.
Peace,
--Carl
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org