db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Dmitri Pissarenko" <dmitri.pissare...@gmail.com>
Subject Views in Derby
Date Wed, 27 Jun 2007 13:18:09 GMT
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/

Mime
View raw message