Обсуждение: Problem with complex outer join expression

I'm using 7.4.5 on win XP Pro SP1.

I'm getting:-

     ERROR:  syntax error at or near "(" at character 155

from the query:-

select dummy_records.sequence_nr,timesheets.weekending,timesheets.timesheet_id
from dummy_records
     ,left outer join timesheets
            on (timesheets.weekending = ('2006-04-09' + (integer
dummy_records.sequence_nr-1)*7)))
where dummy_records.sequence_nr between 1 and (date '2006-04-23' -
date '2006-04-09')/7+1;

What this query is trying to achieve is:-

Find all weekending dates between 2 given weekending dates and any
corresponding timesheets for those weekending dates.

What am I doing wrong here? How do I achieve what I want?


Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
www.flashdev.org.au

On 4/26/06, Chris Velevitch <chris.velevitch@gmail.com> wrote:
> I'm using 7.4.5 on win XP Pro SP1.
>
> I'm getting:-
>
>      ERROR:  syntax error at or near "(" at character 155
>
> from the query:-
>
> select dummy_records.sequence_nr,timesheets.weekending,timesheets.timesheet_id
> from dummy_records
>      ,left outer join timesheets
>             on (timesheets.weekending = ('2006-04-09' + (integer
> dummy_records.sequence_nr-1)*7)))
> where dummy_records.sequence_nr between 1 and (date '2006-04-23' -
> date '2006-04-09')/7+1;
>
> What this query is trying to achieve is:-
>
> Find all weekending dates between 2 given weekending dates and any
> corresponding timesheets for those weekending dates.

Table joins can only be done against another table & field, I don't
think you can do it using an expression like this. That should all be
in the where clause.

What do the timesheets and dummy_records tables look like?

--
Postgresql & php tutorials
http://www.designmagick.com/

"Chris Velevitch" <chris.velevitch@gmail.com> writes:
> select dummy_records.sequence_nr,timesheets.weekending,timesheets.timesheet_id
> from dummy_records
>      ,left outer join timesheets
>             on (timesheets.weekending = ('2006-04-09' + (integer
> dummy_records.sequence_nr-1)*7)))
> where dummy_records.sequence_nr between 1 and (date '2006-04-23' -
> date '2006-04-09')/7+1;

>      ERROR:  syntax error at or near "(" at character 155

> What am I doing wrong here?

The comma before LEFT OUTER JOIN is incorrect, and the keyword "integer"
is being used inappropriately.  I think you've miscounted your
parentheses too.

            regards, tom lane

On 4/26/06, chris smith <dmagick@gmail.com> wrote:
> Table joins can only be done against another table & field, I don't
> think you can do it using an expression like this. That should all be
> in the where clause.
>
> What do the timesheets and dummy_records tables look like?

timesheets.weekending is a date
dummy_records.sequence_nr is int8


Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
www.flashdev.org.au

Seems like the problem has something to do with date arithmetic. I
can't seem to add an integer expression to a date constant. It works
if I add a integer constant to a date constant, but that's not what I
want. I tried cast(expression as integer) + date '2006-04-09' but I
get 'ERROR:  operator does not exist: integer + date'. What do I need
to do to add a number to a date?


Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
www.flashdev.org.au

On Thu, Apr 27, 2006 at 16:55:22 +1000,
  Chris Velevitch <chris.velevitch@gmail.com> wrote:
> Seems like the problem has something to do with date arithmetic. I
> can't seem to add an integer expression to a date constant. It works
> if I add a integer constant to a date constant, but that's not what I
> want. I tried cast(expression as integer) + date '2006-04-09' but I
> get 'ERROR:  operator does not exist: integer + date'. What do I need
> to do to add a number to a date?

I believe that has been fixed in recent version of postgres. At some point
in the past there was only a date + int operator and not an int + date
operator.
Overloading makes it easy to forget that those are two different operators.

On 4/28/06, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Either write it as date + integer, or add an integer + date operator

I thought I did it in that order, but I'll check it again.

> (not very hard, just transpose the inputs), or use PG >= 8.0 which has
> integer + date built in.

I'm using a shared host for pg, so I'm limited to they are prepared to support.


Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
www.flashdev.org.au

On 4/27/06, Bruno Wolff III <bruno@wolff.to> wrote:
> I believe that has been fixed in recent version of postgres. At some point
> in the past there was only a date + int operator and not an int + date
> operator.
> Overloading makes it easy to forget that those are two different operators.

I'll double check the order I've been using, but I'm using shared
hosting for pg, so I'm limited to what they're prepared to support.


Chris
--
Chris Velevitch
Manager - Sydney Flash Platform Developers Group
www.flashdev.org.au