empire-user mailing list archives

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:chieu.nguyen@verizon.com]
Gesendet: Montag, 31. März 2014 17:43
An: user@empire-db.apache.org
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
Chieu.nguyen@verizon.com