Sorry. This is what I did for the 1st question.
cmd.join(db.T_CIRCUIT_WITH_ATTR,
db.T_CIRCUIT_WITH_MULTI_ATTR.C_CIRCUIT_WITH_ATTR_ID.is(db.T_CIRCUIT_WITH_ATTR.C_CIRCUIT_WITH_ATTR_ID)
.and(db.T_CIRCUIT_WITH_MULTI_ATTR.C_ATTRIBUTE_NAME.is("SECONDARY_CIRCUIT") ),
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]>
