Re: change the order of FROM selection to make query work

Am 26.9.2006 schrieb "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
>Define "stopped working" ... what was wrong exactly?

oh, sorry. i forgot the error message:

Error: org.postgresql.util.PSQLException: ERROR: invalid reference to
FROM-clause entry for table "t", SQL State: 42P01, Error Code: 0

(i use squirrel-sql)

the trac (through python&pgsql) error was:
"Report execution failed: ERROR: invalid reference to FROM-clause entry
for table "t" HINT: There is an entry for table "t", but it cannot
be referenced from this part of the query."

but the code in the OP doesn't produce the error (i simplified the sql,
but propably striped the error as well)

the full code that does produce the error (and this error can be resolved
as in OP described) is:

SELECT
(CASE WHEN d.value = Null THEN '99999' ELSE d.value END)as
Wiedervorlage,
p.value AS __color__,
id AS ticket, summary, status, priority ,component, t.type AS type,
(CASE status WHEN 'assigned' THEN owner||' *' ELSE owner END) AS
owner,
time AS created,
changetime AS _changetime, description AS _description,
reporter AS _reporter,
(CASE WHEN c.value = '0' THEN 'None' ELSE c.value END) AS
Fachabteilung,
(CASE WHEN e.value = '0' THEN 'None' ELSE e.value END) AS Kategorie
FROM ticket as t, permission as perm, enum as p
LEFT OUTER JOIN ticket_custom c ON (t.id = c.ticket AND c.name =
'fachabteilung')
LEFT Outer join ticket_custom d ON (t.id = d.ticket AND d.name =
'wiedervorlage')
LEFT Outer join ticket_custom e ON (t.id = e.ticket AND e.name =
'kategorie')
WHERE status IN ('new', 'assigned', 'reopened') AND perm.action =
'mf' and perm.username='$USER'
and p.name = t.priority AND p.type='priority'
ORDER BY wiedervorlage, priority, p.value, t.type, time

cheers,
thomas