I need to do a join between the following two tables:
Code:
CREATE TABLE "VENDOR"
(
SETID varchar2(5) NOT NULL,
VENDOR_ID varchar2(10) NOT NULL,
PRIM_ADDR_SEQ_NUM decimal(22) NOT NULL
);
Code:
CREATE TABLE "VENDOR_ADDR"
(
SETID varchar2(5) NOT NULL,
VENDOR_ID varchar2(10) NOT NULL,
ADDRESS_SEQ_NUM decimal(22) NOT NULL,
EFFDT timestamp
);
Code:
@Entity
@Table(name="VENDOR")
public class Vendor implements Serializable {
private static final long serialVersionUID = 1L;
private PrimaryKey pk;
private VendorAddr primAddr;
@Id
public PrimaryKey getPrimaryKey() {
return pk;
}
public void setPrimaryKey(PrimaryKey pk) {
this.pk = pk;
}
@OneToOne
@JoinColumns({@JoinColumn(insertable=false, name="SETID", referencedColumnName="SETID", updatable=false),
@JoinColumn(insertable=false, name="VENDOR_ID", referencedColumnName="VENDOR_ID", updatable=false),
@JoinColumn(insertable=false, name="PRIM_ADDR_SEQ_NUM", referencedColumnName="ADDRESS_SEQ_NUM", updatable=false)})
public VendorAddr getPrimAddr() {
return primAddr;
}
public void setPrimAddr(VendorAddr addr) {
this.primAddr = addr;
}
// ========================================================================
// Inner Classes
@Embeddable
public static class PrimaryKey implements Serializable {
private static final long serialVersionUID = 1L;
private String setid;
private String vendorId;
private PrimaryKey() {
}
@Column(name="SETID", nullable=false)
public String getSetid() {
return this.setid;
}
public void setSetid(String setid) {
this.setid = setid;
}
@Column(name="VENDOR_ID", nullable=false)
public String getVendorId() {
return this.vendorId;
}
public void setVendorId(String vendorId) {
this.vendorId = vendorId;
}
}
}
Code:
@Entity
@Table(name="VENDOR_ADDR")
public class VendorAddr implements Serializable {
private static final long serialVersionUID = 1L;
private PrimaryKey pk;
private Date effDt;
@Id
public PrimaryKey getPrimaryKey() {
return pk;
}
public void setPrimaryKey(PrimaryKey pk) {
this.pk = pk;
}
@Column(name="EFFDT")
public Date getEffDt() {
return effDt;
}
public void setEffDt(Date effDt) {
this.effDt = effDt;
}
// ========================================================================
// Inner Classes
@Embeddable
private static class PrimaryKey implements Serializable {
private static final long serialVersionUID = 1L;
private String setid;
private String vendorId;
private BigInteger addressSeqNum;
private PrimaryKey() {
}
@Column(name="SETID", nullable=false)
public String getSetid() {
return setid;
}
public void setSetid(String setid) {
this.setid = setid;
}
@Column(name="VENDOR_ID", nullable=false)
public String getVendorId() {
return vendorId;
}
public void setVendorId(String vendorId) {
this.vendorId = vendorId;
}
@Column(name="ADDRESS_SEQ_NUM", nullable=false)
public BigInteger getAddressSeqNum() {
return addressSeqNum;
}
public void setAddressSeqNum(BigInteger addressSeqNum) {
this.addressSeqNum = addressSeqNum;
}
}
}
With sample data as follows:
Code:
SETID VENDOR_ID PRIM_ADDR_SEQ_NUM
CO 0000000001 1
Code:
SETID VENDOR_ID ADDRESS_SEQ_NUM EFFDT
CO 0000000001 1 2099-01-01
CO 0000000001 1 2007-12-05
CO 0000000001 1 1952-01-01
The property 'primAddr' in VENDOR should contain only the VENDOR_ADDR record with the max effective date less than or equal to today's date.
I have tried a few techniques that looked promising, but nothing seemed to lead in the right direction. Any hints as to new things to try would be appreciated.
Login
FAQ
Search