Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Posts
    184

    Unanswered: complex Cartesian Join

    I have a query that is giving me problems.

    I have two tables tblCalendar and tblRooms that both are joined together using a cartestion join. Next I have a vwRoomReservations that is LEFT joined to the cartesian tblRooms. One of the fields I'm using to join on, comes back as 'cal.cday invalid identifier'.

    Structure tblCalendar:
    Name Type
    ------- -------
    CDAY DATE
    HOLIDAY INTEGER

    Query:

    select *
    from tblCalendar cal, tblRooms R
    left join vwRoomReservation RR on rr.roomid=r.id and cal.cday=trunc(rr.meetingStart)

    If instead of using cal.cday, I use to_date('1/1/2005','mm/dd/yyyy') it works fine but of course it only returns one day.

    Any hints or suggestions as how to do this differently?

    Thank you.

    Robert

  2. #2
    Join Date
    Nov 2003
    Location
    Belgium
    Posts
    25
    Hi,

    You specify that the join is only on one table but you reference the other table.
    Maybe, you must write a where clause for the second test.

    query becomes :

    select *
    from tblCalendar cal, tblRooms R
    left join vwRoomReservation RR on rr.roomid=r.id
    where cal.cday=trunc(rr.meetingStart)

    Xavier

  3. #3
    Join Date
    May 2004
    Posts
    184
    Xavier,

    Sorry, for taking so long to get back to you. I just got back to this project. Thank you for you observation, I will try it out.

    Regards,

    Robert

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •