Return-Path: X-Original-To: apmail-empire-db-user-archive@www.apache.org Delivered-To: apmail-empire-db-user-archive@www.apache.org Received: from mail.apache.org (hermes.apache.org [140.211.11.3]) by minotaur.apache.org (Postfix) with SMTP id 5E2E4F341 for ; Mon, 28 Apr 2014 14:39:06 +0000 (UTC) Received: (qmail 95485 invoked by uid 500); 28 Apr 2014 14:39:06 -0000 Delivered-To: apmail-empire-db-user-archive@empire-db.apache.org Received: (qmail 95466 invoked by uid 500); 28 Apr 2014 14:39:05 -0000 Mailing-List: contact user-help@empire-db.apache.org; run by ezmlm Precedence: bulk List-Help: List-Unsubscribe: List-Post: List-Id: Reply-To: user@empire-db.apache.org Delivered-To: mailing list user@empire-db.apache.org Received: (qmail 95458 invoked by uid 99); 28 Apr 2014 14:39:05 -0000 Received: from athena.apache.org (HELO athena.apache.org) (140.211.11.136) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 28 Apr 2014 14:39:05 +0000 X-ASF-Spam-Status: No, hits=1.5 required=5.0 tests=HTML_MESSAGE,RCVD_IN_DNSWL_LOW,SPF_PASS X-Spam-Check-By: apache.org Received-SPF: pass (athena.apache.org: domain of chieu.nguyen@verizon.com designates 199.249.25.209 as permitted sender) Received: from [199.249.25.209] (HELO omzsmtpe02.verizonbusiness.com) (199.249.25.209) by apache.org (qpsmtpd/0.29) with ESMTP; Mon, 28 Apr 2014 14:39:00 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=simple/simple; d=verizon.com; i=chieu.nguyen@verizon.com; q=dns/txt; s=corp; t=1398695939; x=1430231939; h=from:to:date:subject:message-id:references:in-reply-to: mime-version; bh=5z6Wpy5i1AKFH0GOuHiuUFCfY4yKimUuMMwuNuIQK5s=; b=XDRXeNwvf0EGnK8y6DWwLZzSqXZJoG1qJRVxRu3CHFodgdq2ND1quf19 /jk3kQY7zJcs7dd5Kp0/eytWIKQ0uVNTjbwW5ijXTnshDJBuHAcRUphcX JC0/FEy7jko4/x1mnRJeS989HbcpwGDHLoa3y6aFC0ZYYuPLFLWFzDiQc A=; X-IronPort-Anti-Spam-Filtered: false Received: from unknown (HELO fldsmtpi03.verizon.com) ([166.68.71.145]) by omzsmtpe02.verizonbusiness.com with ESMTP; 28 Apr 2014 14:38:38 +0000 From: "Nguyen, Chieu X" X-IronPort-AV: E=Sophos;i="4.97,944,1389744000"; d="scan'208,217";a="708297583" Received: from fhdp1lumxc7hb01.verizon.com (HELO FHDP1LUMXC7HB01.us.one.verizon.com) ([166.68.59.188]) by fldsmtpi03.verizon.com with ESMTP; 28 Apr 2014 14:38:38 +0000 Received: from FHDP1LUMXC7V41.us.one.verizon.com ([166.68.125.36]) by FHDP1LUMXC7HB01.us.one.verizon.com ([166.68.59.188]) with mapi; Mon, 28 Apr 2014 10:38:37 -0400 To: "user@empire-db.apache.org" Date: Mon, 28 Apr 2014 10:38:36 -0400 Subject: RE: SQL General Questions Thread-Topic: SQL General Questions Thread-Index: Ac9hM45LFDQY9jdgQRau7umKlwBOHABu+BkA Message-ID: References: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: acceptlanguage: en-US Content-Type: multipart/alternative; boundary="_000_DB0A1CB7BBFBB9499155678C54C1BF7B01391949ABFHDP1LUMXC7V4_" MIME-Version: 1.0 X-Virus-Checked: Checked by ClamAV on apache.org --_000_DB0A1CB7BBFBB9499155678C54C1BF7B01391949ABFHDP1LUMXC7V4_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable 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_CIRCUI= T_WITH_ATTR.C_CIRCUIT_WITH_ATTR_ID) .and(db.T_CIRCUIT_WITH_MULTI_ATTR.C_ATTRIBUTE_NAME.is("SECONDARY_C= IRCUIT") ), Thank You, Chieu Nguyen Chieu.nguyen@verizon.com From: Rainer D=F6bele [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 w= hat I can say: To add more than one AND to a join (or to add an OR) you should use the whe= re() function: e.g.: cmd.join(db.T_CIRCUIT_WITH_ATTR.C_CIRCUIT_WITH_ATTR_ID, db.T_CIRCUIT_WITH_M= ULTI_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 =3D "[A-Za-z0-9_]"; DBFuncExpr REGEXP_CUSTOMER_CIRCUIT =3D 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 mi= stake. 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 re: SQL General Questions Hi Rainer, I have some basic SQL questions that I am not sure how to write it using Em= pire-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 =3D t3.CIRC= UIT_WITH_ATTR_ID and t3.ATTRIBUTE_NAME=3D'SECONDARY_CIRCUIT', CUSTOMER_CIRCUIT_IMPACT t7 WHERE t7.ITEM_ID=3D74114696 AND (t2.PON is not null OR t2.PO_CIRCUIT is not null) AND t7.CIRCUIT_WITH_ATTR_ID=3Dt2.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_CIRCUI= T_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_NA= ME, "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 =3D some value And REGEXP_LIKE(customer_circuit, string); REGEXP_LIKE is a function ca= ll. I would appreciate your help. Thank You, Chieu Nguyen Chieu.nguyen@verizon.com --_000_DB0A1CB7BBFBB9499155678C54C1BF7B01391949ABFHDP1LUMXC7V4_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable

Sorry.=A0 This is what I did for the 1st question.=

 

cmd.joi= n(db.T_CIRCUIT_WITH_ATTR,

=A0=A0=A0=A0=A0=A0=A0=A0=A0db.T_CIRCUIT_WITH_MULTI_ATTR.C_CIRCUIT_WI= TH_ATTR_ID.is(db.T_CIRCUIT_WITH_ATTR.C_CIRCUIT_WITH_ATTR_ID)

=A0=A0=A0=A0=A0=A0=A0 =A0.and(<= span style=3D'font-size:10.0pt;font-family:Consolas;color:#0000C0'>db.T_C= IRCUIT_WITH_MULTI_ATTR.C_ATTRIBUTE_NAME.is("SECONDARY_CIRCUIT"= ) ),<= /p>

 

 =

Thank You,

 

 

 

C= hieu Nguyen

Chieu.nguyen@verizon.com

 

<= b>From:<= /span> Rainer D=F6bele [mailto:doebele@esteam.de]
Sent: Saturday, Ap= ril 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 s= hould use the where() function:

<= span style=3D'font-size:10.0pt;font-family:"Tahoma","sans-serif"'>&nbs= p;

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)

&nb= sp;   .where(db.T.C1.is("ABC")

=

      = .and(db.T.C2.is("= ;XYZ")

     = ; .and(db.= T.<= span style=3D'color:#0000C0'>C3.isBetween(0,5));

&n= bsp;

 =

 

= 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 =3D "[A-Za-z0-9_]";

DBFuncExpr REGEXP_CUSTOMER_CIRCUIT =3D new= DBFuncEx= pr(db.T.CUSTOMER_CIRCUIT, "REGEXP_LIKE(?, {0})", new= Object[] { expr }, null, false, DataType.BOOL);

cmd.where (REGEXP_CUSTOMER_CIRCUIT.is(tr= ue));

 

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
re:<= /b> SQL General Questions

 

Hi Rainer,

 

I have some bas= ic 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?

 

Thi= s is what I would like to achieve.

 

SELECT t2.PO_CIRCUIT, t2= .PON--, t3.ATTRIBUTE_VALUE

FROM C= IRCUIT_WITH_ATTR t2

left join CIR= CUIT_WITH_MULTI_ATTR t3 on t2.CIRCUIT_WITH_ATTR_ID =3D t3.CIRCUIT_WITH_ATTR= _ID and t3.ATTRIBUTE_NAME=3D'SECONDARY_CIRCUIT',

CUSTOMER_CIRCUIT_IMPACT t7=

WHERE t7.ITEM_ID=3D74114696

 &nbs= p;AND (t2.PON is not null OR t2.PO_CIRCUIT is not null)

  AND t7.CIRCUIT_WITH_ATTR_ID=3Dt2.CIRCU= IT_WITH_ATTR_ID;

 

This is what I have written using empire-d= b

   = ;             c= md.select(db.T_CIRCUIT_WITH_ATTR.C_PO_CIRCUIT, db.T_CIRCUIT_WITH_ATT= R.= = C_PON, db.T_CIRCUIT_WITH_MULTI_ATTR.C_ATTRIBUTE_NAME);

        = ;        cmd.join(db.T_CIRCUIT_WIT= H_ATTR.C_CIRCUIT_WITH_ATTR_ID, db.T_CIRCUIT_WITH_MULTI_ATTR.C_CIRCUIT_WITH_A= TTR_ID, DBJoinType.LEFT);

//           &n= bsp;            = ; .and(db.T_CIRCUIT_WITH_MULTI_ATTR.C_ATTRIBUTE_NAME, "SECONDARY_CIRCU= IT"); This line gave me an error

          &nb= sp;     cmd.where(db.T_CUSTOMER_CIRCUIT_IMPACT.C_ITE= M_ID.is(lItemId) );

  &= nbsp;           &nbs= p; cmd.where(db.T_CUSTOMER_CIRCUIT_IMPACT.C_CIRCUIT_WITH_ATTR_ID.is(<= /span>db.T_CIRCUIT_WITH_ATTR.C_CIRCUIT_WITH_ATTR_ID) );

      Also, = I am not sure how to add the 2nd ‘AND’ statement.

 

2.<= span style=3D'font:7.0pt "Times New Roman"'>  How to include= a function call in an ‘AND’ statement.

 

Regular SQL:

  Select customer_circuit

  From customer_impact

  Where item_id =3D some= value

 &nbs= p;  And REGEXP_LIKE(customer_circuit, string= ); REGEXP_LIKE is a function call.

 

I would appreciate your help.

 

 

 

Thank You,=

 

 

 

Chieu Ng= uyen

Chieu.nguyen@verizon.com

 

= --_000_DB0A1CB7BBFBB9499155678C54C1BF7B01391949ABFHDP1LUMXC7V4_--