Problem with complex outer join expression

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