Query problem

[dsnyckers]

Hi,

I want to create a query, but have no clue how to do it.
This is my case:

- I have a one to many unidirectional relationship
Section 1 --> * ArrivalDate
so my section ejb has a cmr field arrivaldates (set)
and I'm using an relation table mapping
The arrivalDate has a date column

- What I want to do is given a section id and a date, I like to retrieve all ArrivalDate ejbs that match the given date but are also related to the section.

So I need something like :
SELECT OBJECT(a)
FROM ArrivalDate a, Section s
WHERE ... AND a.date = ?2

I thought I could use the cmr field name like
f.e.: s.arrivaldates.id = a.id but that doesn't work.

Can this be done in some way using the cmr field to only check the arrivaldate ejb's that belong to the given section. Or do I have to add the relational table mapping table in the query?

TIA,
Dave

[dsnyckers]

I got it working but have another problem. I mentioned that I was querying the date in the ArrivalDate table.

But is it possible to query only on the year of the date.
So I only want the ArrivalDate ejbs where the date is of the specified year.

Query looks now like :
with %1 the id of the section (int)
and %2 the year (int)

SELECT OBJECT(a)
FROM Section s, IN(s.arrivalDates) a
WHERE s.id = %1 AND
year(a.date) = %2

but this last line doesn't work. Is there something to solve this problem,

TIA (again :)
Dave

[l.g.]

try:

SELECT OBJECT(a)
FROM Section s, IN(s.arrivalDates) a
WHERE s.id = ?1 AND
year(a.date) = ?2

[dsnyckers]

Sorry,

it was a typo of me.
i was trying it with ?1 and ?2, but that doesn't work.
the year(a.date) causes the troubles

I think, I need to replace my date column with a Year, Month, Day and Time column, so that I can query on that.

Is this the only solution?

[l.g.]

What datatype are you using as parameter for <a.date>?
I think you can use only long (milliseconds). And you cant usr <year()> function. See the specs.