Thanks Brett for your time. Yes it is more of Hibernate issue i was just trying to find if some of derby user encounter with this.
Here are tables structure and mapping.
 

CREATE TABLE PROJECT (
 PROJECT_ID VARCHAR(50) NOT NULL,
 PROJECT_NAME VARCHAR(150) NOT NULL,
 PROJECT_MANAGER VARCHAR(150) NOT NULL,
 START_DATE TIMESTAMP NOT NULL,
 END_DATE TIMESTAMP NOT NULL,
 PROGRAM_ID INTEGER,
 PRIMARY KEY (PROJECT_ID)
);
ALTER TABLE PROJECT
 ADD FOREIGN KEY (PROGRAM_ID)
 REFERENCES PROGRAM (PROGRAM_ID);
 

CREATE TABLE PROGRAM (
 PROGRAM_ID INTEGER DEFAULT AUTOINCREMENT: start 1 increment 1 NOT NULL,
 PROGRAM_NAME VARCHAR(150) NOT NULL,
 PROGRAM_MANAGER VARCHAR(150) NOT NULL,
 PRIMARY KEY (PROGRAM_ID)
);
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="a.b.model.Project" table="PROJECT">
        <id name="projectId" type="string">
            <column name="PROJECT_ID" length="50" />
            <generator class="assigned" />
        </id>
        <property name="projectName" type="string">
            <column name="PROJECT_NAME" length="150" not-null="true" />
        </property>
        <property name="projectManager" type="string">
            <column name="PROJECT_MANAGER" length="150" not-null="true" />
        </property>
        <property name="startDate" type="timestamp">
            <column name="START_DATE" length="26" not-null="true" />
        </property>
        <property name="endDate" type="timestamp">
            <column name="END_DATE" length="26" not-null="true" />
        </property>
        <property name="programId" type="java.lang.Integer">
            <column name="PROGRAM_ID" />
        </property>
    </class>
</hibernate-mapping>
<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC "-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">
<hibernate-mapping>
    <class name="a.b.model.Program" table="PROGRAM">
        <id name="programId" type="int">
            <column name="PROGRAM_ID" />
            <generator class="identity" />
        </id>
        <property name="programName" type="string">
            <column name="PROGRAM_NAME" length="150" not-null="true" />
        </property>
        <property name="programManager" type="string">
            <column name="PROGRAM_MANAGER" length="150" not-null="true" />
        </property>
    </class>
</hibernate-mapping>
 
 
 
Thanks once again.

On Mon, May 31, 2010 at 7:55 PM, Brett Wooldridge <brett.wooldridge@gmail.com> wrote:
When you say "Hibernate 3", what specific version are you talking about?  While this is more of a Hibernate question than a Derby question, what is the structure of the two tables (for Project and Program) and what does the mapping look like?

Brett


On Mon, May 31, 2010 at 10:52 PM, anyz <anyz111@gmail.com> wrote:
Its seems to be hibernate issue. Dialect in hibernate 3 is not uptodate i think.
 
We tried it as below:

SELECT proj, prog, FROM Project proj, Program prog

but it didn't work.



On Mon, May 31, 2010 at 6:19 PM, Donald McLean <dmclean62@gmail.com> wrote:
Have you tried explicitly specifying the table? i.e. Project.proj

On Mon, May 31, 2010 at 7:00 AM, anyz <anyz111@gmail.com> wrote:
> i'm trying to use hibernate 3 with Derby 10.6. Normal queries work good but
> using left outer join creates problem. The query i'm trying loads project
> program if it exists and if programm not exists the project information is
> loaded:
>
> SELECT proj, prog FROM Project
> LEFT OUTER JOIN Program ON Program.programId = Project.programId
> WHERE Project.projectName = '"MyProject"
>
> The error on executing query is :
>
> unexpected token: ON near line 1, column xxx [SELECT proj, prog, FROM
> a.b.Project LEFT OUTER JOIN Program ON Program.programId = Project.programId
> WHERE Project.projectName = 'MyProject' ]
> at org.hibernate.hql.ast.QuerySyntax
>
> Could you please guid if Hibernate dialect support for Derbby is not that
> good of i'm missing something.
>
> thanks
>



--
Family photographs are a critical legacy for
ourselves and our descendants. Protect that
legacy with a digital backup and recovery plan.

Join the photo preservation advocacy Facebook group:
http://www.facebook.com/home.php?ref=logo#/group.php?gid=148274709288