empire-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rainer Döbele <doeb...@esteam.de>
Subject AW: Using select statement in the where clause
Date Mon, 31 Mar 2014 16:00:28 GMT
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

 


Mime
View raw message