Hello,

I have the following table structure:

CREATE TABLE MEPPBX (
ID numeric PRIMARY KEY NOT NULL,
PHAR numeric NOT NULL,
BOX numeric NOT NULL,
BEGINN date NOT NULL,
END date NULL,
GRND numeric NULL,
FOREIGN KEY (PHAR) REFERENCES MEPP(ID),
FOREIGN KEY (BOX) REFERENCES MEBOX(ID));

create table MEBOX(
ID NUMBER(22) PRIMARY KEY not null,
BOX VARCHAR2(3) not null,
VERSION NUMBER(22))

CREATE TABLE MEPP (
ID numeric PRIMARY KEY NOT NULL,
);

The MEPPBX is the table that connects MEBOX with MEPP. Additionally it holds the beginn and end date where the MEBOX is valid for the MEPP.

No I want to find out the MEPP where the MEBOX has changed to MEBOX.BOx='TEST' within the last month!

Is this possible using a criteria query or either a HQL?

Thx's a lot for any help! Rene

No, it is not possible to write a HQL query unless you have existing Hibernate classes and mappings and can show them to us.

... sorry Gavin here are the classes including xdoclet definitions. The id itself is in the AbstractModel. Thanks a lot for that quick help!!!!

/*
* @hibernate.class
* table="MEBOX"
*/
public class Box extends AbstractModel implements Serializable {
private String code;
private String status;
private String kurzbeschreibung;
private String langbeschreibung;
/**
* @hibernate.property column = "BOX" not-null = "true" length = "3"
*/
public String getCode() {
return code;
}
public void setCode(String code) {
this.code = code;
}
}

/*
* @hibernate.class
* table="MEPP"
*/
public class Packung extends AbstractModel implements Serializable {
private Collection boxCollection;
/**
* @return Returns the box.
* @hibernate.set inverse = "true" cascade = "save-update" order-by = "LABE"
* @hibernate.collection-key column = "ID"
* @hibernate.collection-one-to-many class = "at.sozvers.eko.model.PackungBox"
*/
public Collection getBoxCollection() {
return boxCollection;
}
public void setBoxCollection(Collection box) {
this.boxCollection = box;
}
}


/*
* @hibernate.class
* table="MEPPBX"
*/
public class PackungBox extends AbstractModel implements Serializable {
private Date datumBeginn;
private Date datumEnde;
private Aufnahmegrund aufnahmegrund;
private Box box;
private Packung packung;

/**
* @hibernate.property column = "LABE" not-null = "true"
*/
public Date getDatumBeginn() {
return datumBeginn;
}
public void setDatumBeginn(Date datumBE) {
this.datumBeginn = datumBE;
}
/**
* @hibernate.property column = "LAEN"
*/
public Date getDatumEnde() {
return datumEnde;
}
public void setDatumEnde(Date datumEN) {
this.datumEnde = datumEN;
}
/**
* @hibernate.one-to-one
* column = "PHAR"
* not-null = "true"
* class = "at.sozvers.eko.model.Packung"
*/
public Packung getPackung() {
return packung;
}
public void setPackung(Packung packung) {
this.packung = packung;
}
/**
* @hibernate.many-to-one
* column = "BOX"
* not-null = "true"
* class = "at.sozvers.eko.model.Box"
*/
public Box getBox() {
return box;
}
public void setBox(Box box) {
this.box = box;
}
}


I tried the following query but did not succeed (looking for all boxes that changed from something to 'N' within a specified time periode):

Criteria boxCollection = session.createCriteria(Packung.class)
.createCriteria("boxCollection");

boxCollection.add(
Expression.and(Expression.isNotNull("datumEnde"),
Expression.between("datumEnde", von, bis))
);

boxCollection
.createAlias("box", "box")
.add(
Expression.and(
Expression.and(
Expression.isNull("datumEnde"),
Expression.between("datumBeginn", von, bis)
),
Expression.eq("box.code", "N")
)
);
packungList = boxCollection.list();

... seems that I'm getting closer to the result now:

I found out that I need a subselect for the query, so I have to use HQL. The following HQL should fit my needs:

FROM at.sozvers.eko.model.Packung p
WHERE p.boxCollection.box.box = 'N'
AND p.boxCollection.datumBeginn BETWEEN :datumVon AND :datumBis
AND p.boxCollection.datumEnde IS NULL
AND p IN
(FROM p.boxCollection pBox WHERE pBox.datumEnde IS NOT NULL)

The problem is, that I now encounter a QueryException saying the following:

expecting 'elements' or 'indices' after: box

What does this mean?

Thx Rene

... now I got it:

FROM at.sozvers.eko.model.Packung p
JOIN FETCH p.boxCollection pBox
JOIN FETCH pBox.box box
WHERE box.code = 'N'
AND pBox.datumBeginn BETWEEN :datumVon AND :datumBis
AND pBox.datumEnde IS NULL
AND p IN (
SELECT pBox.packung FROM at.sozvers.eko.model.PackungBox pBox WHERE pBox.datumEnde IS NOT NULL)

The problem was the mapping of Packung:

wrong:
* @hibernate.collection-key
* column = "ID"
correct:
* @hibernate.collection-key
* column = "PHAR"