4 Replies Latest reply on Sep 19, 2002 10:40 AM by L.G.

    Query problem

    Dave Newbie

      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

        • 1. Re: Query problem
          Dave Newbie

          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

          • 2. Re: Query problem
            L.G. Newbie

            try:

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

            • 3. Re: Query problem
              Dave Newbie

              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?

              • 4. Re: Query problem
                L.G. Newbie

                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.