Hi Rainer, Thank you for answering my questions.
This is what I did for the "JOIN table2 on table2.id = table1.id and table2.column1 = 'ABC'" dbCmd.join(db.T_CIRCUIT_WITH_ATTR, db.T_CUSTOMER_CIRCUIT_IMPACT.C_CIRCUIT_WITH_ATTR_ID.is(db.T_CIRCUIT_WITH_ATTR.C_CIRCUIT_WITH_ATTR_ID) ); I will try your suggestion for question 2 and let you know how it comes out. Thank You, Chieu Nguyen [email protected] From: Rainer Döbele [mailto:[email protected]] Sent: Saturday, April 26, 2014 4:40 AM To: [email protected] Subject: re: SQL General Questions Hi Nguyen, I am not quite sure, whether I fully understand your problem, but here is what I can say: To add more than one AND to a join (or to add an OR) you should use the where() function: e.g.: cmd.join(db.T_CIRCUIT_WITH_ATTR.C_CIRCUIT_WITH_ATTR_ID, db.T_CIRCUIT_WITH_MULTI_ATTR.C_CIRCUIT_WITH_ATTR_ID, DBJoinType.LEFT) .where(db.T.C1.is("ABC") .and(db.T.C2.is("XYZ") .and(db.T.C3.isBetween(0,5)); If you want to use SQL Functions like the REGEXP_LIKE that are specific to your database, you can use a DBFuncExpr like this: String expr = "[A-Za-z0-9_]"; DBFuncExpr REGEXP_CUSTOMER_CIRCUIT = new DBFuncExpr(db.T.CUSTOMER_CIRCUIT, "REGEXP_LIKE(?, {0})", new Object[] { expr }, null, false, DataType.BOOL); cmd.where (REGEXP_CUSTOMER_CIRCUIT.is(true)); Hope that will solve your problems. Note: I have not tested this code, so it cannot be sure that there is no mistake. But in principle this is how to do it. Best regards Rainer from: Nguyen, Chieu X [mailto:[email protected]] to: [email protected]<mailto:[email protected]> re: SQL General Questions Hi Rainer, I have some basic SQL questions that I am not sure how to write it using Empire-DB. 1. How do I include an 'AND' statement in a left join? This is what I would like to achieve. SELECT t2.PO_CIRCUIT, t2.PON--, t3.ATTRIBUTE_VALUE FROM CIRCUIT_WITH_ATTR t2 left join CIRCUIT_WITH_MULTI_ATTR t3 on t2.CIRCUIT_WITH_ATTR_ID = t3.CIRCUIT_WITH_ATTR_ID and t3.ATTRIBUTE_NAME='SECONDARY_CIRCUIT', CUSTOMER_CIRCUIT_IMPACT t7 WHERE t7.ITEM_ID=74114696 AND (t2.PON is not null OR t2.PO_CIRCUIT is not null) AND t7.CIRCUIT_WITH_ATTR_ID=t2.CIRCUIT_WITH_ATTR_ID; This is what I have written using empire-db cmd.select(db.T_CIRCUIT_WITH_ATTR.C_PO_CIRCUIT, db.T_CIRCUIT_WITH_ATTR.C_PON, db.T_CIRCUIT_WITH_MULTI_ATTR.C_ATTRIBUTE_NAME); cmd.join(db.T_CIRCUIT_WITH_ATTR.C_CIRCUIT_WITH_ATTR_ID, db.T_CIRCUIT_WITH_MULTI_ATTR.C_CIRCUIT_WITH_ATTR_ID, DBJoinType.LEFT); // .and(db.T_CIRCUIT_WITH_MULTI_ATTR.C_ATTRIBUTE_NAME, "SECONDARY_CIRCUIT"); This line gave me an error cmd.where(db.T_CUSTOMER_CIRCUIT_IMPACT.C_ITEM_ID.is(lItemId) ); cmd.where(db.T_CUSTOMER_CIRCUIT_IMPACT.C_CIRCUIT_WITH_ATTR_ID.is(db.T_CIRCUIT_WITH_ATTR.C_CIRCUIT_WITH_ATTR_ID) ); Also, I am not sure how to add the 2nd 'AND' statement. 2. How to include a function call in an 'AND' statement. Regular SQL: Select customer_circuit From customer_impact Where item_id = some value And REGEXP_LIKE(customer_circuit, string); REGEXP_LIKE is a function call. I would appreciate your help. Thank You, Chieu Nguyen [email protected]<mailto:[email protected]>
