Re: Union

dilan · **Posted:** Mon Sep 05, 2005 11:41 pm

Need help with Hibernate? Read this first:
http://www.hibernate.org/ForumMailingli ... AskForHelp

Hibernate version: 2.0 +

I have two tables, they are known as WmaRegister and ExternalSales,
WmaRegister has a string field walNumber and
ExternalSales has a string field walFrom,

i want to generate a sql, which gives the result set as WmaRegister and ExternalSales , when
walNumber = walFrom

this the hsql i got so far

Code:

Iterator iter = sess.iterate(
                "select wma, es "             + " ;
            + " from com.wf.core.WmaRegister wma, "
            + " com.wf.core.ExternalSales es "
            + " where (es.walFrom = wma.walNumber) "
            + " and wma.dipnrNumber like '%' || ? || '%' "
            + " and wma.licenceHolders like '%' || ? || '%' "
            + " and wma.managementZone like '%' || ? || '%' "
            + " and wma.category like '%' || ? || '%' "
            + " order by " + searchKey, 
               new Object[] { dipnrNumber, name, managementZone, category },
                new Type[] { Hibernate.STRING, Hibernate.STRING, Hibernate.STRING, Hibernate.STRING  } ); 

Code:

while (iter.hasNext()) {
            logger.debug("*");
                Object[] current = (Object[]) iter.next();
                //if (Calendar.getInstance().before(((WaterOffer) current[0]).getExpiryDate())) {
               //String saletype = ((WaterOffer) current[0]).getSale().booleanValue() ? "permanent" : "temporary";
               //if (StateTradingRelationship.getStrStatus(State.getStateByName(((WaterOffer) current[0]).getSellingRegion().getState().getName()),State.getStateByName(Region.getRegionByName(regionName).getState().getName()),saletype) == true) { 
                  result.add(new WmaSearchResultsBean(
                     (WmaRegister) current[0],
                     (ExternalSales) current[1]));
               //}
               // }
            }

but which not working;

here is the sql level:

SELECT count(wal_number) FROM wma_register WHERE wal_number IN (SELECT wal_to AS num FROM external_sales UNION SELECT wal_from FROM external_sales);

can some one help me to develop suitable hsql for me?

thanks

kgignatyev · **Posted:** Tue Sep 06, 2005 11:11 am

Code:

Iterator iter = sess.iterate(
                "select wma, es "             + " ;
            + " from com.wf.core.WmaRegister wma, "
            + " com.wf.core.ExternalSales es "
            + " where (es.walFrom = wma.walNumber) "
            + " and wma.dipnrNumber like ? "
            + " and wma.licenceHolders like ? "
            + " and wma.managementZone like ? "
            + " and wma.category like ? "
            + " order by " + searchKey,
               new Object[] { "%" + dipnrNumber + "%", "%"+name+"%","%"+ managementZone+"%", "%"+category+"%" },
                new Type[] { Hibernate.STRING, Hibernate.STRING, Hibernate.STRING, Hibernate.STRING  } );

dilan · **Posted:** Wed Sep 07, 2005 2:43 am

Quote:

Iterator iter = sess.iterate(
"select wma, es " + " ;
+ " from com.wf.core.WmaRegister wma, "
+ " com.wf.core.ExternalSales es "
+ " where (es.walFrom = wma.walNumber) "
+ " and wma.dipnrNumber like ? "
+ " and wma.licenceHolders like ? "
+ " and wma.managementZone like ? "
+ " and wma.category like ? "
+ " order by " + searchKey,
new Object[] { "%" + dipnrNumber + "%", "%"+name+"%","%"+ managementZone+"%", "%"+category+"%" },
new Type[] { Hibernate.STRING, Hibernate.STRING, Hibernate.STRING, Hibernate.STRING } );

thanks for the reply, every thing works, but
its not listen to es.walFrom = wma.walNumber, it display all the queres except, es.walFrom = wma.walNumber, why is that?
where both walFrom and walNumber both are string fields!

kgignatyev · **Posted:** Wed Sep 07, 2005 10:43 am

>>its not listen to es.walFrom = wma.walNumber

What does it mean?

dilan · **Posted:** Thu Sep 08, 2005 3:12 am

Well , from the above sql,
i get the result set of all the value, which
es.walFrom != wma.walNumber,
i dont know how it happens, but thats what the results that im getting?

both es.walFrom and wma.walNumber are string fields.

dilan · **Posted:** Fri Sep 09, 2005 2:17 am

Is there is a any other way to join two tables, according to the two string vaules of each table,

eg; list of two table, where
es.walFrom = wma.walNumber

because i cant get it to work, its gives the result set, which is inverse of what i requred.

kgignatyev · **Posted:** Fri Sep 09, 2005 11:42 am

It is hard to tell where is the problem without seeing all the actual mappings and classes. I've made simple test and it works properly in H2.1.8 and H3.

Try to localize the problem possibly by simplifying the query just to pinpoint cause.

Here is my test:

Code:

Query query;
    query = s.createQuery("select p,r from P021 p, R021 r\n" +
        "where p.name=r.name");
    List res = query.list();
    print( res );

------------------
mapping

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd" >

<hibernate-mapping package="h2tests" >

<class  table="p021" name="P021" lazy="false"  >
    <id name="id" >
      <generator class="assigned"/>
    </id>
   <property name="name" />
   <set name="childHolder" inverse="true" cascade="all-delete-orphan"  >
          <key column="u_id"/>
          <one-to-many class="C021" />
   </set>
</class>

<class table="c021" name="C021" >
  <id name="id">
    <generator class="assigned"/>
  </id>
  <property name="name"/>
  <many-to-one class="P021"
            name="parent"
            column="u_id"
            not-null="true"
            insert="false"
            update="false"
            />
</class>

  <class  table="r021" name="R021" lazy="false"  >
    <id name="id" >
      <generator class="assigned"/>
    </id>
   <property name="name" />
  
</class>

</hibernate-mapping>

--------------

DROP TABLE C021;
DROP TABLE P021;
DROP TABLE R021;

CREATE TABLE P021 (
  id int PRIMARY KEY,
  name varchar(50)
);

CREATE TABLE C021 (
 id int PRIMARY KEY,
 name varchar(50),
 u_id int,
 FOREIGN KEY (u_id) REFERENCES p021 (id)
);

CREATE TABLE R021(
 id int PRIMARY KEY,
 name varchar(50)
);

INSERT INTO P021 VALUES ( 1, 'N-1');
INSERT INTO P021 VALUES ( 10, 'N-10');

INSERT INTO C021 VALUES ( 1, 'C-1',1);
INSERT INTO C021 VALUES ( 2, 'C-2',1);
INSERT INTO C021 VALUES ( 3, 'C-3',1);
INSERT INTO C021 VALUES ( 4, 'C-4',1);

INSERT INTO R021 VALUES ( 1, 'C-1');
INSERT INTO R021 VALUES ( 2, 'C-2');
INSERT INTO R021 VALUES ( 3, 'N-1');
INSERT INTO R021 VALUES ( 4, 'N-2');

Result of the query has one row as expected.

dilan · **Posted:** Sun Sep 11, 2005 9:51 am

thanks for your feed back so far, i just try to understand whats causes the problem, and what im doing wrong

is it nessary to use;

Quote:

CREATE TABLE C021 (
id int PRIMARY KEY,
name varchar(50),
u_id int,
FOREIGN KEY (u_id) REFERENCES p021 (id)
);

when i compair just two tables,

in my case, im compairing walFrom and/or walTo in EXTERNAL_SALES with wal_number in WMA_REGISTER,

These are the two XML files that i got for the two tables,

<?xml version="1.0" ?>
<!DOCTYPE hibernate-mapping (View Source for full doctype...)>
- <hibernate-mapping default-cascade="none" default-access="property" auto-import="true">
- <class name="com.wf.core.ExternalSales" table="EXTERNAL_SALES" lazy="false" mutable="true" polymorphism="implicit" dynamic-update="false" dynamic-insert="false" batch-size="1" select-before-update="false" optimistic-lock="version">
- <id name="id" type="long" unsaved-value="null">
<generator class="native" />
</id>
<property name="provider" type="string" not-null="false" unique="false" update="true" insert="true" />
- <many-to-one name="fromRegion" class="com.wf.core.Region" not-null="false" unique="false" outer-join="auto" update="true" insert="true">
<column name="from_region" />
</many-to-one>
- <many-to-one name="toRegion" class="com.wf.core.Region" not-null="false" unique="false" outer-join="auto" update="true" insert="true">
<column name="to_region" />
</many-to-one>
- <property name="walFrom" type="string" not-null="false" unique="false" update="true" insert="true">
<column name="wal_from" />
</property>
- <property name="walTo" type="string" not-null="false" unique="false" update="true" insert="true">
<column name="wal_to" />
</property>
<property name="startIrrigationSeason" type="string" not-null="false" unique="false" update="true" insert="true" />
<property name="endIrrigationSeason" type="string" not-null="false" unique="false" update="true" insert="true" />
<property name="price" type="long" not-null="true" unique="false" update="true" insert="true" />
<property name="quantity" type="double" not-null="true" unique="false" update="true" insert="true" />
- <property name="saleDate" type="calendar" not-null="false" unique="false" update="true" insert="true">
<column name="saledate" />
</property>
<property name="sale" type="boolean" not-null="false" unique="false" update="true" insert="true" />
- 
</class>
</hibernate-mapping>

<?xml version="1.0" ?>
<!DOCTYPE hibernate-mapping (View Source for full doctype...)>
- <hibernate-mapping default-cascade="none" default-access="property" auto-import="true">
- <class name="com.wf.core.WmaRegister" table="WMA_REGISTER" lazy="false" mutable="true" polymorphism="implicit" dynamic-update="false" dynamic-insert="false" batch-size="1" select-before-update="false" optimistic-lock="version">
- <id name="id" type="long" unsaved-value="null">
<generator class="native" />
</id>
- <property name="walNumber" type="string" not-null="false" unique="false" update="true" insert="true">
<column name="wal_number" />
</property>
<property name="category" type="string" not-null="false" unique="false" update="true" insert="true" />
- <property name="dipnrNumber" type="string" not-null="false" unique="false" update="true" insert="true">
<column name="dipnr_number" />
</property>
<property name="status" type="string" not-null="false" unique="false" update="true" insert="true" />
- <property name="waterSource" type="string" not-null="false" unique="false" update="true" insert="true">
<column name="water_source" />
</property>
- <property name="tenureType" type="string" not-null="false" unique="false" update="true" insert="true">
<column name="tenure_type" />
</property>
- <property name="managementZone" type="string" not-null="false" unique="false" update="true" insert="true">
<column name="management_zone" />
</property>
- <property name="shareComponents" type="double" not-null="false" unique="false" update="true" insert="true">
<column name="share_components" />
</property>
- <property name="nwaNumber" type="string" not-null="false" unique="false" update="true" insert="true">
<column name="n_w_a_number" />
</property>
- <property name="licenceHolders" type="string" not-null="false" unique="false" update="true" insert="true">
<column name="licence_holders" />
</property>
<property name="updated" type="calendar" not-null="false" unique="false" update="true" insert="true" />
</class>
</hibernate-mapping>

kgignatyev · **Posted:** Sun Sep 11, 2005 9:08 pm

Sorry for the confision caused by less relevant code - that is remnant of another test. Table C021 is irrelevant for the use case, if you look at the query closely then P021 and r021 participate in the join explicitly ( C021 holds chidren of P021, it actually takes part in the query, but it is implicit via Hibernate mapping ), and tehej are joined by 'name' field which is not part of any FK relationships.

I would suggest creating a simple use case that will use shortened version of your mapping files ( just couple of properties per class) and try to run such test and see if there will be any problem.