You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by Arnar Birgisson <ar...@oddi.is> on 2004/05/14 20:48:15 UTC
Outer join in Report Query
Hello there,
I have a problem similar to Wallace's
(http://www.mail-archive.com/ojb-user@db.apache.org/msg09417.html) from
a few weeks ago.
I have a table containing a log of errors/mistakes that happen in the
production of a book or some artifact (this is for a commercial
printer). One field in this log is called "JobID" and can be any string.
In most cases, but not nearly all, this string is a reference number to
another table "Jobs" which contains a description of the job as well as
the customer id and name.
Now, as I said, JobID in the Problems table can be any string and
doesn't need to reference an existent row in the Jobs table. My
application only handles problem reports from the Problems table as
entities, and doesn't make any changes to the other table (which is
populated by a legacy job scheduling system). I have an OJB mapping for
the Problems table that is working great.
However, I would like to display the job description and customer name
along with the problem, given that the JobID of the problem exists in
the Jobs table. In a nutshell, I want the results of this SQL query in a
ReportQuery:
select P.ProblemId, P.ProblemDescription, P.JobId, J.JobDescription,
J.Customer
from Problems P
left outer join Jobs J on P.JobId = J.JobId
How would I do this? I wan't the JobId field to be a plain String
attribute of the Problem class, not a reference to a seperate Job class
(in fact, I don't have any need for such a class at all).
Can I use the QueryBySql class to generate such custom report queries? I
didn't find any documentation for that class, so I'm taking a wild shot
:o)
Of course, I can do this directly through JDBC, but I just wanted to
check with you if there is a way to do this through OJB.
Arnar
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
RE: Outer join in Report Query
Posted by Arnar Birgisson <ar...@oddi.is>.
Hello,
Do all the tables have to share a common primary key?
If so, my problem is not solved by this (see below in my first post).
Could I solve it with a custom rowreader? Perhaps that would be
ineffective.
Arnar
> -----Original Message-----
> From: Jakob Braeuchi [mailto:jbraeuchi@gmx.ch]
> Sent: 14. maí 2004 19:14
> To: OJB Users List
> Subject: Re: Outer join in Report Query
>
>
> hi arnar,
>
> this feature is available in the latest from cvs. see
> InheritenceMultipleTableTest.
>
> hth
> jakob
>
> Arnar Birgisson wrote:
>
> > Just one thing I thought of, I saw somewhere that "one
> class multiple
> > tables" mappings was a feature to be expected in future versions of
> > OJB, which would solve my problem. Also, the "table"
> attribute of the
> > class-descriptor element in the DTD points to this as well.
> >
> > Is this a planned feature?
> >
> > Arnar
> >
> >
> >>-----Original Message-----
> >>From: Arnar Birgisson [mailto:arnarb@oddi.is]
> >>Sent: 14. maí 2004 18:48
> >>To: ojb-user@db.apache.org
> >>Subject: Outer join in Report Query
> >>
> >>
> >>Hello there,
> >>
> >>I have a problem similar to Wallace's
> >>(http://www.mail-archive.com/ojb-user@db.apache.org/msg09417.h
> >>tml) from a few weeks ago.
> >>
> >>I have a table containing a log of errors/mistakes that
> >>happen in the production of a book or some artifact (this is
> >>for a commercial printer). One field in this log is called
> >>"JobID" and can be any string. In most cases, but not nearly
> >>all, this string is a reference number to another table
> >>"Jobs" which contains a description of the job as well as the
> >>customer id and name.
> >>
> >>Now, as I said, JobID in the Problems table can be any string
> >>and doesn't need to reference an existent row in the Jobs
> >>table. My application only handles problem reports from the
> >>Problems table as entities, and doesn't make any changes to
> >>the other table (which is populated by a legacy job
> >>scheduling system). I have an OJB mapping for the Problems
> >>table that is working great.
> >>
> >>However, I would like to display the job description and
> >>customer name along with the problem, given that the JobID of
> >>the problem exists in the Jobs table. In a nutshell, I want
> >>the results of this SQL query in a
> >>ReportQuery:
> >>
> >>select P.ProblemId, P.ProblemDescription, P.JobId,
> >>J.JobDescription, J.Customer from Problems P
> >> left outer join Jobs J on P.JobId = J.JobId
> >>
> >>How would I do this? I wan't the JobId field to be a plain
> >>String attribute of the Problem class, not a reference to a
> >>seperate Job class (in fact, I don't have any need for such a
> >>class at all).
> >>
> >>Can I use the QueryBySql class to generate such custom report
> >>queries? I didn't find any documentation for that class, so
> >>I'm taking a wild shot
> >>:o)
> >>
> >>Of course, I can do this directly through JDBC, but I just
> >>wanted to check with you if there is a way to do this through OJB.
> >>
> >>Arnar
> >>
> >>
> >>------------------------------------------------------------
> ---------
> >>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> >>For additional commands, e-mail: ojb-user-help@db.apache.org
> >>
> >>
> >
> >
> >
> >
> ---------------------------------------------------------------------
> > To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> > For additional commands, e-mail: ojb-user-help@db.apache.org
> >
> >
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Outer join in Report Query
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi arnar,
this feature is available in the latest from cvs. see InheritenceMultipleTableTest.
hth
jakob
Arnar Birgisson wrote:
> Just one thing I thought of, I saw somewhere that "one class multiple
> tables" mappings was a feature to be expected in future versions of OJB,
> which would solve my problem. Also, the "table" attribute of the
> class-descriptor element in the DTD points to this as well.
>
> Is this a planned feature?
>
> Arnar
>
>
>>-----Original Message-----
>>From: Arnar Birgisson [mailto:arnarb@oddi.is]
>>Sent: 14. maí 2004 18:48
>>To: ojb-user@db.apache.org
>>Subject: Outer join in Report Query
>>
>>
>>Hello there,
>>
>>I have a problem similar to Wallace's
>>(http://www.mail-archive.com/ojb-user@db.apache.org/msg09417.h
>>tml) from a few weeks ago.
>>
>>I have a table containing a log of errors/mistakes that
>>happen in the production of a book or some artifact (this is
>>for a commercial printer). One field in this log is called
>>"JobID" and can be any string. In most cases, but not nearly
>>all, this string is a reference number to another table
>>"Jobs" which contains a description of the job as well as the
>>customer id and name.
>>
>>Now, as I said, JobID in the Problems table can be any string
>>and doesn't need to reference an existent row in the Jobs
>>table. My application only handles problem reports from the
>>Problems table as entities, and doesn't make any changes to
>>the other table (which is populated by a legacy job
>>scheduling system). I have an OJB mapping for the Problems
>>table that is working great.
>>
>>However, I would like to display the job description and
>>customer name along with the problem, given that the JobID of
>>the problem exists in the Jobs table. In a nutshell, I want
>>the results of this SQL query in a
>>ReportQuery:
>>
>>select P.ProblemId, P.ProblemDescription, P.JobId,
>>J.JobDescription, J.Customer from Problems P
>> left outer join Jobs J on P.JobId = J.JobId
>>
>>How would I do this? I wan't the JobId field to be a plain
>>String attribute of the Problem class, not a reference to a
>>seperate Job class (in fact, I don't have any need for such a
>>class at all).
>>
>>Can I use the QueryBySql class to generate such custom report
>>queries? I didn't find any documentation for that class, so
>>I'm taking a wild shot
>>:o)
>>
>>Of course, I can do this directly through JDBC, but I just
>>wanted to check with you if there is a way to do this through OJB.
>>
>>Arnar
>>
>>
>>---------------------------------------------------------------------
>>To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
>>For additional commands, e-mail: ojb-user-help@db.apache.org
>>
>>
>
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
RE: Outer join in Report Query
Posted by Arnar Birgisson <ar...@oddi.is>.
Just one thing I thought of, I saw somewhere that "one class multiple
tables" mappings was a feature to be expected in future versions of OJB,
which would solve my problem. Also, the "table" attribute of the
class-descriptor element in the DTD points to this as well.
Is this a planned feature?
Arnar
> -----Original Message-----
> From: Arnar Birgisson [mailto:arnarb@oddi.is]
> Sent: 14. maí 2004 18:48
> To: ojb-user@db.apache.org
> Subject: Outer join in Report Query
>
>
> Hello there,
>
> I have a problem similar to Wallace's
> (http://www.mail-archive.com/ojb-user@db.apache.org/msg09417.h
> tml) from a few weeks ago.
>
> I have a table containing a log of errors/mistakes that
> happen in the production of a book or some artifact (this is
> for a commercial printer). One field in this log is called
> "JobID" and can be any string. In most cases, but not nearly
> all, this string is a reference number to another table
> "Jobs" which contains a description of the job as well as the
> customer id and name.
>
> Now, as I said, JobID in the Problems table can be any string
> and doesn't need to reference an existent row in the Jobs
> table. My application only handles problem reports from the
> Problems table as entities, and doesn't make any changes to
> the other table (which is populated by a legacy job
> scheduling system). I have an OJB mapping for the Problems
> table that is working great.
>
> However, I would like to display the job description and
> customer name along with the problem, given that the JobID of
> the problem exists in the Jobs table. In a nutshell, I want
> the results of this SQL query in a
> ReportQuery:
>
> select P.ProblemId, P.ProblemDescription, P.JobId,
> J.JobDescription, J.Customer from Problems P
> left outer join Jobs J on P.JobId = J.JobId
>
> How would I do this? I wan't the JobId field to be a plain
> String attribute of the Problem class, not a reference to a
> seperate Job class (in fact, I don't have any need for such a
> class at all).
>
> Can I use the QueryBySql class to generate such custom report
> queries? I didn't find any documentation for that class, so
> I'm taking a wild shot
> :o)
>
> Of course, I can do this directly through JDBC, but I just
> wanted to check with you if there is a way to do this through OJB.
>
> Arnar
>
>
> ---------------------------------------------------------------------
> To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
> For additional commands, e-mail: ojb-user-help@db.apache.org
>
>
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org