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 Frank Nguyen <fr...@sbcglobal.net> on 2008/08/22 21:09:23 UTC
Criteria.addSelectColumn problem
I have 3 tables, t1, t2 and t3. t3 table is just a join-ref table of t1 and t2 in such a way that t3.c2 is ref index to t1.c1 and t3.c3 is a ref index to t2.c1 (t3.c1 is index for t3 table). Thus, table t3 has many records w/ same value in column 2 and 3. What I want is to select only rows from table 3 with unique c3 values. The statement:
"select * distinct from t3 where t3.c2 = 3333"
gives me 27 rows where many of them have the same c2=3333 and c3=same number, obviously. (i.e. 3333 4532, 3333 4532, 3333 4533, 3333 4534, etc.) which is not what I wanted.
All I want is to have distinctive rows with c3 unique. So, the following statement gives me exactly what I want:
select c3 distinct from t3 where t3.c2=3333
gives me 7 row with c3 distinctive (i.e. 3333 4532, 3333 4533, 3333 4534, etc.)
However, when I do it in Torque:
crit.add (c2, 3333)
crit.addSelectColumn (c3)
crit.setDistinct();
Iterator results = t3Peer.doSelect(crit).iterator()
would throw Exception at doSelect() because during the populateObject(), it found only 1 column instead of all columns as default to populate the data into it.
So, to make it short, is there a way to select records from a table with a specified, distinctive column(s) in Torque?
Thanks in advance,
Frank Nguyen
(408) 836-6235 (Cell)
RE: Criteria.addSelectColumn problem
Posted by Thomas Fischer <fi...@seitenbau.net>.
> crit.add (c2, 3333)
> crit.addSelectColumn (c3)
> crit.setDistinct();
> Iterator results = t3Peer.doSelect(crit).iterator()
>
> would throw Exception at doSelect() because during the
> populateObject(), it found only 1 column instead of all columns as
> default to populate the data into it.
Alternatice to Carl's answer:
If you want the complete objects instead of just the values of c3, xou
want to use a subselect. You express the condition in the subselect, and
then select all the objects in the outer select. Like
innercrit.add (c2, 3333)
innercrit.addSelectColumn (c3)
innercrit.setDistinct();
crit.add(c2, 3333);
crit,add(c3, innercrit);
Iterator results = t3Peer.doSelect(crit).iterator();
Hope this works (have not tried it), but in principle it is possible.
Thomas
RE: Criteria.addSelectColumn problem
Posted by "Manaster, Carl" <Ca...@ACTIVX.com>.
Hi, Frank,
> However, when I do it in Torque:
>
> crit.add (c2, 3333)
> crit.addSelectColumn (c3)
> crit.setDistinct();
> Iterator results = t3Peer.doSelect(crit).iterator()
>
> would throw Exception at doSelect() because during the
> populateObject(), it found only 1 column instead of all
> columns as default to populate the data into it.
>
> So, to make it short, is there a way to select records from a
> table with a specified, distinctive column(s) in Torque?
I think what you are looking for is to replace t3Peer with BasePeer.
This will not return t3 records; instead it will return just generic
Records, and those records will hold only the specified (c3) select
column.
Peace,
--Carl
---------------------------------------------------------------------
To unsubscribe, e-mail: torque-user-unsubscribe@db.apache.org
For additional commands, e-mail: torque-user-help@db.apache.org