You are viewing a plain text version of this content. The canonical link for it is here.
Posted to derby-user@db.apache.org by Dmitri Pissarenko <dm...@gmail.com> on 2007/06/27 15:18:09 UTC
Views in Derby
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/
Re: Views in Derby
Posted by Dmitri Pissarenko <dm...@gmail.com>.
Thanks!
--
http://www.xing.com/profile/Dmitri_Pissarenko
http://dapissarenko.blogspot.com/
Re: Views in Derby
Posted by Peter Ondruska <pe...@gmail.com>.
OK, sorry, forgot to name the inner table (x):
SELECT * FROM (your_SELECT_with_GROUP_BY) x ORDER BY ...
On 29.6.2007, at 10:28, Dmitri Pissarenko wrote:
> Hello!
>
> On 6/28/07, Peter Ondruska <pe...@gmail.com> wrote:
>> SELECT * FROM (your_current_SELECT_statement_with_GROUP_BY) ORDER
>> BY ...
>
> Thanks for your answer!
>
> When I do this, I'm getting this error:
>
> SQL State = 42X01 SQL Code = 30000 SQL Message = Syntax error:
> Encountered "ORDER" at line 5, column 1. Exception message =
> java.sql.SQLSyntaxErrorException: Syntax error: Encountered "ORDER" at
> line 5, column 1.
>
> Best regards
>
> Dmitri Pissarenko
> --
> http://www.xing.com/profile/Dmitri_Pissarenko
> http://dapissarenko.blogspot.com/
Re: Views in Derby
Posted by Dmitri Pissarenko <dm...@gmail.com>.
Hello!
On 6/28/07, Peter Ondruska <pe...@gmail.com> wrote:
> SELECT * FROM (your_current_SELECT_statement_with_GROUP_BY) ORDER BY ...
Thanks for your answer!
When I do this, I'm getting this error:
SQL State = 42X01 SQL Code = 30000 SQL Message = Syntax error:
Encountered "ORDER" at line 5, column 1. Exception message =
java.sql.SQLSyntaxErrorException: Syntax error: Encountered "ORDER" at
line 5, column 1.
Best regards
Dmitri Pissarenko
--
http://www.xing.com/profile/Dmitri_Pissarenko
http://dapissarenko.blogspot.com/
Re: Views in Derby
Posted by Peter Ondruska <pe...@gmail.com>.
SELECT * FROM (your_current_SELECT_statement_with_GROUP_BY) ORDER BY ...
On 28.6.2007, at 15:03, Dmitri Pissarenko wrote:
> Hello!
>
> Now I understand why this error happens: GROUP BY doesn't take into
> account the sort order (Starttime).
>
> Is there an elegant way to make GROUP BY take into account sort order
> of the records?
>
> TIA
>
> Dmitri Pissarenko
Re: Views in Derby
Posted by Dmitri Pissarenko <dm...@gmail.com>.
Hello!
Now I understand why this error happens: GROUP BY doesn't take into
account the sort order (Starttime).
Is there an elegant way to make GROUP BY take into account sort order
of the records?
TIA
Dmitri Pissarenko
Re: Views in Derby
Posted by Dmitri Pissarenko <dm...@gmail.com>.
Hello!
Thanks for your hint!
The idea with GROUP BY is really good.
I tried to use it, but now have following problem.
I have these data:
Datetime | Location | SomeDataAsString
--------------------+-----------+-----------------
14.05.2007 10:58:55 | LOC_01 | A, B
14.05.2007 11:08:33 | LOC_01 | A, B
14.05.2007 11:14:42 | LOC_01 | A, B
14.05.2007 11:16:11 | LOC_01 | A, B
14.05.2007 11:32:31 | LOC_01 | A, B
14.05.2007 14:20:03 | LOC_01 | A, B
14.05.2007 17:24:06 | LOC_01 | A, B
14.05.2007 17:40:38 | LOC_01 | A, B
15.05.2007 17:26:44 | LOC_01 | B
15.05.2007 17:26:47 | LOC_01 | A, B
and I want to display them so:
Starttime | Endtime | Location | SomeDataAsString
--------------------+---------------------+----------+-----------------
14.05.2007 10:58:55 | 15.05.2007 17:26:44 | LOC_01 | A, B
15.05.2007 17:26:44 | 15.05.2007 17:26:47 | LOC_01 | B
15.05.2007 17:26:47 | 15.05.2007 17:26:47 | LOC_01 | A, B
That is, from 14.05.2007 10:58:55 to 14.05.2007 17:40:38 the field
SomeDataAsString was equal to "A, B" and for this time period only one
row should be generated.
At 15.05.2007 17:26:44 the value of SomeDataAsString changes - a new
row is needed.
Finally, at 15.05.2007 17:26:47 SomeDataAsString changes back to "A,
B" - that's the third row.
To make this I wrote following SQL query:
SELECT MIN(Datetime) AS STARTTIME, MAX(Datetime) AS ENDTIME, Location,
SomeDataAsString
FROM APP.MyTable
GROUP BY Location, SomeDataAsString
ORDER BY Location, STARTTIME
It returns following result, which is wrong:
Starttime | Endtime | Location | SomeDataAsString
--------------------+---------------------+----------+-----------------
14.05.2007 10:58:55 | 15.05.2007 17:26:47 | LOC_01 | A, B
15.05.2007 17:26:44 | 15.05.2007 17:26:44 | LOC_01 | B
How can I fix this error?
TIA
Dmitri Pissarenko
Re: Views in Derby
Posted by Stanley Bradbury <St...@gmail.com>.
Dmitri Pissarenko wrote:
> 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
HI Dmitri -
I don't have a clear understanding of your question but if it is simply
that you are getting the four records you list as 'DON'T want' and want
the two records listed under 'DO want' I think you can do this with a
group-by SQL statement. Try this and let me know if it works:
select min(Date/Time), Location, ALL_as_string
from <myViewName>
group by Location, ALL_as_string