Re: ODMG - MULTIPLE TABLES - Retrieval

Fri, 10 Sep 2004 13:30:11 -0700

Thanks Armin for your input . Let me explain my question in a better way so I can be directed in right direction . I am attaching the sql also in this note...


1st Table :
--------------

TABLE_PARTA:
-------------------------
part_num(pk)
part_serial(pk)
system(pk)
qty

The primary key of this table consists of part_num,part_serial,system.
qty is a non-primary key column in the same table.

2ndTable :
--------------

TABLE_PARTB:
-------------------------
part_num(pk)
part_serial(pk)
type(pk)
cost

3rd table:
-----------------

The primary key of this table consists of part_num,part_serial,type.
cost is a non-primary key column in the same table.

TABLE_PARTC:
-------------------------
part_num(pk)
part_serial(pk)
dimension(pk)
size

The primary key of this table consists of part_num,part_serial,dimesion.
size is a non-primary key column in the same table.


The primary key of all these tables consist of three columns out of which two columns(part_num,part_serial ) are alike but not the third column.

I would like to retrieve qty from PartA table, cost from PartB table and size from PartC table . The following sql may explain it better what I am trying to attain....

select distinct \
      A.part_num, \
      A.part_serial, \
      sum(A.qty) as numerator, \
      sum(B.cost) as denominator, \
      sum(C.size) as dim \

from TABLE_PARTA A, TABLE_PARTB  B, \
    TABLE_PARTC C \

where \
     B.part_num = A.part_num \
     and B.part_serial= A.part_serial\
     and C.part_num = B.part_num \
     and C.part_serial= B.part_serial\


Thanks in advance ...


From: Armin Waibel <[EMAIL PROTECTED]>
Reply-To: "OJB Users List" <[EMAIL PROTECTED]>
To: OJB Users List <[EMAIL PROTECTED]>
Subject: Re: ODMG - MULTIPLE TABLES - Retrieval
Date: Fri, 10 Sep 2004 20:59:17 +0200

Hi,

kishore talagadadeevi wrote:

Hi,

I would like to know whether any known issues with ODMG api in handling multiple tables by mapping to a single class.

Mapping a single class to multiple tables was currently not supported (insert does not work, reading should work but I never test this) by ODMG (PB-api support this feature).

I need to just retrieve the data only . 

as said above, this could be work with odmg.

I have seen some documentation
explaining a mapping of a class to multile tables. But I want to know whether it is going to
work for my scenario or not before starting my coding ....

Requirement:
-----------------

I have three independent tables  .

All three tables have part_num and part_serial as part of composite primary key in addition to other columns.

Question: How do I retrieve qty,cost and size by joining all three tables on part_num and
part_serial columns using OJB ???

How do I define this scenario as a class descriptor in repository.xml file ???



Don't know if I understand your question, but nevertheless I will try answer something ;-)

You can use 'Extents' with a base interface.
http://db.apache.org/ojb/docu/guides/advanced-technique.html#Extents

interface Part
{
get/setNum
get/setSerial
{

PartA,...C implement interface Part

In repository file declare class-descriptor for PartA,...C (with PK attribute for num + serial) and
define these classes as extents of Part

<class-descriptor class="Part">
   <extent-class class-ref="PartA" />
   <extent-class class-ref="PartB" />
   <extent-class class-ref="PartC" />
</class-descriptor>

Criteria crit = new Criteria();
[crit.addEqualTo("fieldName", value);]
crit.addEqualTo("partNum", 12);
crit.addEqualTo("serial", 123);
Query q = QueryFactory.newQuery(Part.class, crit);
Collection result = broker.getCollectionByQuery(q);

Will return all Part classes (PartA, ...C) with part_num 12 and part_serial 123


regards,
Armin


TABLE_PARTA:
-------------------------

part_num(pk)
part_serial(pk)
system(pk)
qty


TABLE_PARTB
---------------------


part_num(pk)
part_serial(pk)
type(pk)
cost

TABLE_PARTC :
-----------------------
part_num(pk)
part_serial(pk)
dimension(pk)
size

Please throw some light on the above mentioned issue ...

Thanks in advance ...



---------------------------------------------------------------------
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]

Reply via email to