empire-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Nguyen, Chieu X" <chieu.ngu...@verizon.com>
Subject RE: SQL General Questions
Date Mon, 28 Apr 2014 14:38:36 GMT
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
Chieu.nguyen@verizon.com

From: Rainer Döbele [mailto:doebele@esteam.de]
Sent: Saturday, April 26, 2014 4:40 AM
To: user@empire-db.apache.org
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:chieu.nguyen@verizon.com]
to: user@empire-db.apache.org<mailto:user@empire-db.apache.org>
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
Chieu.nguyen@verizon.com<mailto:Chieu.nguyen@verizon.com>


Mime
View raw message