-->
These old forums are deprecated now and set to read-only. We are waiting for you on our new forums!
More modern, Discourse-based and with GitHub/Google/Twitter authentication built-in.

All times are UTC - 5 hours [ DST ]



Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 
Author Message
 Post subject: Complex Query - One to One with Dffective Date
PostPosted: Thu Aug 06, 2009 2:03 pm 
Newbie

Joined: Mon Jul 27, 2009 4:47 pm
Posts: 1
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.


Top
 Profile  
 
Display posts from previous:  Sort by  
Forum locked This topic is locked, you cannot edit posts or make further replies.  [ 1 post ] 

All times are UTC - 5 hours [ DST ]


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum

Search for:
© Copyright 2014, Red Hat Inc. All rights reserved. JBoss and Hibernate are registered trademarks and servicemarks of Red Hat, Inc.