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]
