Hi Nguyen,
its simpler than you think:
You don't need a DBQuery object to do this. A DBQuery object is only required
if you need to add a join.
So in your case you can simply write:
DBCommand impactCmd = db.createCommand();
impactCmd.select(
db.T_CUSTOMER_CIRCUIT_IMPACT.C_CIRCUIT_WITH_ATTR_ID );
addWhereClauseForType(impactCmd, idType, lID);
DBCommand dbCmd = db.createCommand();
dbCmd.select( db.T_CIRCUIT_EQUIPMENT.count() );
dbCmd.where(
db.T_CIRCUIT_EQUIPMENT.C_CIRCUIT_WITH_ATTR_ID.in(impactCmd) );
That should give you the result that you want.
Regards
Rainer
Von: Nguyen, Chieu X [mailto:[email protected]]
Gesendet: Montag, 31. März 2014 17:43
An: [email protected]
Betreff: Using select statement in the where clause
Hi Rainer,
I am new to empire-db. The SQL below is what I would like empire-db to
generate.
select count(*)
from circuit_equipment
where circuit_with_attr_id in (select circuit_with_attr_id from
customer_circuit_impact
where item_id =
74006036);
I am seeing this from empire-db when I print out my DBCommand. I would like to
know why is empire-db adding the table CUSTOMER_CIRCUIT_IMPACT in the FROM
statement. The query below take a long time to run because of the extra table
that was added.
SELECT count(*)
FROM CIRCUIT_EQUIPMENT t6, CUSTOMER_CIRCUIT_IMPACT t7
WHERE t6.CIRCUIT_WITH_ATTR_ID IN ((SELECT t7.CIRCUIT_WITH_ATTR_ID
FROM CUSTOMER_CIRCUIT_IMPACT t7
WHERE t7.ITEM_ID=74006036))
Here is the code that produced the SQL above.
DBCommand impactCmd = db.createCommand();
impactCmd.select(
db.T_CUSTOMER_CIRCUIT_IMPACT.C_CIRCUIT_WITH_ATTR_ID );
addWhereClauseForType(impactCmd, idType, lID);
DBQuery CIRCUIT_WITH_ATTR_ID = new DBQuery(impactCmd);
DBCommand dbCmd = db.createCommand();
dbCmd.select( db.T_CIRCUIT_EQUIPMENT.count() );
dbCmd.where(
db.T_CIRCUIT_EQUIPMENT.C_CIRCUIT_WITH_ATTR_ID.in(CIRCUIT_WITH_ATTR_ID) );
Please help.
Thank You,
Chieu Nguyen
[email protected]
