Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (herse.apache.org: domain of dmitri.pissarenko@gmail.com
 designates 64.233.166.178 as permitted sender)
DomainKey-Signature: a=rsa-sha1; c=nofws;
        d=gmail.com; s=beta;
        h=received:message-id:date:from:to:subject:mime-version:content-type:content-transfer-encoding:content-disposition;
        b=mw/U0+8QkjH/XWxyAAmzcYGQkgV+gdptQBxzPSdZQY00LHR5/g/tBkaKe0tRtyYcmbkDm2p/Z3lahdbi1oWZmw/wzYtmLSNmHUAS9TQqjVWGVncZc0PxTH1ZcsCbiJvz+kJoNZ8P7Xo9euCChyaOyLm2fl2QnkDmpWPV83196Z8=
Message-ID: <e012616b0706270618v6e775c3bu26d8721c0f9e2f71@mail.gmail.com>
Date: Wed, 27 Jun 2007 15:18:09 +0200
From: "Dmitri Pissarenko" <dmitri.pissarenko@gmail.com>
To: derby-user@db.apache.org
Subject: Views in Derby
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Hello!

I have Table A with following fields:

1) A.Date/time
2) A.Location

In table B for each record of table A there is zero, one or more
records with following fields:

1) B.name
2) B.type

B.type can take on one of exactly four values, say "TYPE1", "TYPE2",
"TYPE3", "TYPE4".

I need to view these data in several ways.

View #1

Fields:

1) A.Date/time
2) A.Location
3) B records with type TYPE1 as string
4) B records with type TYPE2 as string
5) B records with type TYPE3 as string
6) B records with type TYPE4 as string
7) All B records as string

Sort order: 1) Location 2) date/time

For instance:

A.Date/time | A.Location |  B records TYPE1 | B records TYPE2 | B
records TYPE3 | B records TYPE4 | All B records
2007-06-27 15:08 | Location 1 | BName1, BName2 | BName3 | BName4 |
BName 5 | BName1, BName2, BName3, BName4, BName 5

View #2

Fields:

1) A.Date/time
2) A.Location
3) All B records as string

Sort order: 1) Location 2) date/time

In this view, I want to see part of the data from View #1, but with a
difference - rows with same content in field 3) should not be shown.

That is, I DON'T want this:

A.Date/time | A.Location | All B records as string
2007-06-27 15:14 | Loc1 | BName1, BName2, BName3
2007-06-27 15:15 | Loc1 | BName1, BName2, BName3
2007-06-27 15:16 | Loc1 | BName1, BName2, BName3
2007-06-27 15:17 | Loc1 | BName1, BName2

But I DO want this:

A.Date/time | A.Location | All B records as string
2007-06-27 15:14 | Loc1 | BName1, BName2, BName3
2007-06-27 15:17 | Loc1 | BName1, BName2

I have two questions:

How to do this in Derby with

a) minimum amount of Java coding and
b) with minimum amount of duplicated data (if I have to use tables,
instead of views for views #1 and #2, then data are being duplicated)
?

Thanks in advance

Dmitri Pissarenko
-- 
http://www.xing.com/profile/Dmitri_Pissarenko
http://dapissarenko.blogspot.com/