This works now. Thanks a lot.
-----Original Message-----
From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 14, 2003 4:58 AM
To: OJB Users List
Subject: Re: outer join
hi zhe,
do you want to use a manually written sql, or do you prefer
QueryByCriteria ? if you go for the latter, use
setPathOuterJoin("collUsers").
hth
jakob
Zhe Liu wrote:
> Hi,
>
> I cannot make outer join work for the following query by setPathOuterJoin:
>
> select * from company, users where company.companyid = users.companyid (+)
>
> Could anybody tell me what's the right way to do that? Table schema and
> repository definations are attached.
>
> Thanks,
> Zhe
>
> /*==============================================================*/
> /* Table : COMPANY */
> /*==============================================================*/
>
>
> create table COMPANY (
> COMPANYID NUMBER(10) not null,
> ABBRV VARCHAR2(10) not null,
> COMPANY_NAME VARCHAR2(100) not null,
> DISPLAYNAME VARCHAR2(32) not null,
> CREATION_DATE DATE,
> CREATED_BY VARCHAR2(32),
> LAST_MODIFY_DATE DATE,
> LAST_MODIFY_BY VARCHAR2(32),
> LOGO VARCHAR2(100),
> constraint PK_COMPANY_COMPID primary key (COMPANYID)
> )
> /
>
> /*==============================================================*/
> /* Table : USERS */
> /*==============================================================*/
>
>
> create table USERS (
> USERID NUMBER(6) not null,
> login_name VARCHAR2(32) not null,
> FIRST_NAME VARCHAR2(50) not null,
> MIDDLE_NAME VARCHAR2(50),
> LAST_NAME VARCHAR2(50) not null,
> PASSWORD VARCHAR2(20) not null,
> EMAIL VARCHAR2(50) not null,
> timezone VARCHAR2(50),
> EXPIRE_DATE DATE,
> VALID_DAYS NUMBER(3),
> PASSWORD_MODIFY_DATE DATE,
> PASSWORD_MODIFY_BY VARCHAR2(32),
> CREATION_DATE DATE,
> CREATED_BY VARCHAR2(32),
> LAST_MODIFY_DATE DATE,
> LAST_MODIFY_BY VARCHAR2(32),
> COMPANYID NUMBER(10),
> constraint PK_USER_ID primary key (UserId),
> constraint AK_LOGINNAME_USERS unique (login_name),
> constraint FK_USER_COMPID foreign key (COMPANYID)
> references COMPANY (COMPANYID)
> )
> /
>
> <class-descriptor class="com.gsnx.ojbeval.db.Company"
> table="GSNPROTO.COMPANY">
> <field-descriptor name="companyid" column="COMPANYID"
> jdbc-type="BIGINT"
> primarykey="true" autoincrement="true"/>
> <field-descriptor name="abbrv" column="ABBRV" jdbc-type="VARCHAR"/>
> <field-descriptor name="companyName" column="COMPANY_NAME"
> jdbc-type="VARCHAR"/>
> <field-descriptor name="createdBy" column="CREATED_BY"
> jdbc-type="VARCHAR"/>
> <field-descriptor name="creationDate" column="CREATION_DATE"
> jdbc-type="TIMESTAMP"/>
> <field-descriptor name="displayname" column="DISPLAYNAME"
> jdbc-type="VARCHAR"/>
> <field-descriptor name="lastModifyBy" column="LAST_MODIFY_BY"
> jdbc-type="VARCHAR"/>
> <field-descriptor name="lastModifyDate" column="LAST_MODIFY_DATE"
> jdbc-type="TIMESTAMP"/>
> <field-descriptor name="logo" column="LOGO" jdbc-type="VARCHAR"/>
> <collection-descriptor name="collUsers"
> element-class-ref="com.gsnx.ojbeval.db.Users" auto-retrieve="true"
> auto-update="false" auto-delete="false">
> <inverse-foreignkey field-ref="companyid"/>
> </collection-descriptor>
> </class-descriptor>
>
> <class-descriptor class="com.gsnx.ojbeval.db.Users"
table="GSNPROTO.USERS">
> <field-descriptor name="companyid" column="COMPANYID"
jdbc-type="BIGINT"/>
> <field-descriptor name="createdBy" column="CREATED_BY"
> jdbc-type="VARCHAR"/>
> <field-descriptor name="creationDate" column="CREATION_DATE"
> jdbc-type="TIMESTAMP"/>
> <field-descriptor name="email" column="EMAIL" jdbc-type="VARCHAR"/>
> <field-descriptor name="expireDate" column="EXPIRE_DATE"
> jdbc-type="TIMESTAMP"/>
> <field-descriptor name="firstName" column="FIRST_NAME"
> jdbc-type="VARCHAR"/>
> <field-descriptor name="lastModifyBy" column="LAST_MODIFY_BY"
> jdbc-type="VARCHAR"/>
> <field-descriptor name="lastModifyDate" column="LAST_MODIFY_DATE"
> jdbc-type="TIMESTAMP"/>
> <field-descriptor name="lastName" column="LAST_NAME"
jdbc-type="VARCHAR"/>
> <field-descriptor name="loginName" column="LOGIN_NAME"
> jdbc-type="VARCHAR"/>
> <field-descriptor name="middleName" column="MIDDLE_NAME"
> jdbc-type="VARCHAR"/>
> <field-descriptor name="password" column="PASSWORD"
jdbc-type="VARCHAR"/>
> <field-descriptor name="passwordModifyBy" column="PASSWORD_MODIFY_BY"
> jdbc-type="VARCHAR"/>
> <field-descriptor name="passwordModifyDate"
column="PASSWORD_MODIFY_DATE"
> jdbc-type="TIMESTAMP"/>
> <field-descriptor name="timezone" column="TIMEZONE"
jdbc-type="VARCHAR"/>
> <field-descriptor name="userid" column="USERID" jdbc-type="BIGINT"
> primarykey="true" autoincrement="true"/>
> <field-descriptor name="validDays" column="VALID_DAYS"
> jdbc-type="BIGINT"/>
> <reference-descriptor name="aCompany"
> class-ref="com.gsnx.ojbeval.db.Company" auto-retrieve="true"
> auto-update="false" auto-delete="false">
> <foreignkey field-ref="companyid"/>
> </reference-descriptor>
> </class-descriptor>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: [EMAIL PROTECTED]
> For additional commands, e-mail: [EMAIL PROTECTED]
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
