left join conditon causes error
От | Andrus |
---|---|
Тема | left join conditon causes error |
Дата | |
Msg-id | gcj5jb$1usd$1@news.hub.org обсуждение исходный текст |
Ответы |
Re: left join conditon causes error
|
Список | pgsql-general |
Query SELECT bilkaib.* FROM prpalk, (SELECT TRUE AS db, 1 AS sign UNION SELECT FALSE,-1 ) role,konto destkonto,bilkaib LEFT JOIN kurss sihrkurs ON CASE WHEN '00' IN(cr,db) THEN bilkaib.kuupaev-1 ELSE bilkaib.kuupaev END =sihrkurs.kuupaev AND sihrkurs.raha=destkonto.raha WHERE CASE WHEN role.db THEN bilkaib.db ELSE bilkaib.cr END=destkonto.kontonr AND (destkonto.raha=prpalk.pohiraha OR sihrkurs.raha=destkonto.raha ) AND bilkaib.summa<>0 AND destkonto.kontonr<>'00' AND (bilkaib.kuupaev='2008-01-01 00:00:00.000000' OR (bilkaib.cr!='00' AND bilkaib.db!='00')) AND destkonto.kontonr LIKE '111%' ESCAPE '!' AND bilkaib.kuupaev BETWEEN '2008-01-01 00:00:00.000000' AND '2008-01-03 00:00:00.000000' causes error ERROR: invalid reference to FROM-clause entry for table "destkonto" LINE 7: ... =sihrkurs.kuupaev AND sihrkurs.raha=destkonto.... ^ HINT: There is an entry for table "destkonto", but it cannot be referenced from this part of the query. I tried to remove AND sihrkurs.raha=destkonto.raha and move it to where clause: AND (destkonto.raha=prpalk.pohiraha OR sihrkurs.raha=destkonto.raha ) from left join but in this case query returns duplicate rows from bilkaib table. Why destkonto table cannot used but other tables in FROM list can be used in join condition ? How to use such left join ? Andrus.
В списке pgsql-general по дате отправления: