Re: Error in select

Hello.

When I run the next query:

SELECT DATE_TRUNC('hour', TL.TAL005_DATE), TL.SRV_ID, TL.MSU_NUMBER,
DS.DESCRIPTION, DS.CALLTYPE, COUNT(*)
FROM OWNER_CATALOG.TAL005 TL,
OWNER_CATALOG.DS_AREA_CODE DS
WHERE SRV_ID = 1 AND
TAL005_DATE > to_date('01011900','ddmmyyyy') AND
DS.CODE = ( SELECT MAX(DSS.CODE)
FROM OWNER_CATALOG.DS_AREA_CODE DSS
WHERE DSS.CODE =
SUBSTR(TL.TAL005_INCLI,1,LENGTH(DSS.CODE)))
GROUP BY DATE_TRUNC('hour', TL.TAL005_DATE), TL.SRV_ID, TL.MSU_NUMBER,
DS.DESCRIPTION,DS.CALLTYPE;

I get:

ERROR: variable not found in subplan target list

This query works fine in 7.3.4, but we are migrating to 7.4 and it
doesn't work.

Some idea about the problem????

Thanks.

CG

Carmen Gloria Sepulveda Dedes <csepulveda(at)atichile(dot)com> writes:
> I get:
> ERROR: variable not found in subplan target list

Could we have enough context to reproduce the problem? I don't have
time to guess at your table definitions ...

regards, tom lane

Ok ... sorry.

The tables ...
CREATE TABLE STS_TRFAREA (
c_date TIMESTAMP ,
srv SMALLINT ,
msu SMALLINT ,
description VARCHAR(30) ,
calltype VARCHAR(1) ,
ncalls INTEGER
) WITHOUT OIDS;

CREATE TABLE TAL005(
file_id BIGINT ,
srv_id SMALLINT NOT NULL,
msu_number SMALLINT NOT NULL,
mmu_id INTEGER ,
tal005_date TIMESTAMP ,
tal005_seccid VARCHAR(12) ,
tal005_tconect INTEGER ,
tal001_tconect INTEGER ,
tal005_mboxid VARCHAR(16) ,
tal005_incli VARCHAR(16) ,
tal005_msgid VARCHAR(18) ,
tal005_tvcmsg INTEGER ,
tal005_nfaxpg INTEGER ,
tal005_msgclas SMALLINT ,
tal005_msgcncl SMALLINT ,
tal005_msgsts SMALLINT ,
tal005_clgrsnterm SMALLINT
) WITHOUT OIDS;

CREATE TABLE DS_AREA_CODE(
code VARCHAR(16) NOT NULL,
description VARCHAR(30) NOT NULL,
calltype VARCHAR(1) ,
UNIQUE (code)
) WITHOUT OIDS;

I want to insert into table STS_TRFAREA, by selecting on TAL005 AND
DS_AREA_CODE,
with:
INSERT INTO STS_TRFAREA (C_DATE, SRV, MSU, DESCRIPTION,
CALLTYPE, NCALLS)
SELECT DATE_TRUNC('hour', TL.TAL005_DATE), TL.SRV_ID,
TL.MSU_NUMBER,
DS.DESCRIPTION, DS.CALLTYPE, COUNT(*)
FROM TAL005 TL,
DS_AREA_CODE DS
WHERE SRV_ID = 1 AND
TAL005_DATE >
to_date('01011900','ddmmyyyy') AND
DS.CODE = ( SELECT MAX(DSS.CODE)
FROM DS_AREA_CODE DSS
WHERE DSS.CODE =
SUBSTR(TL.TAL005_INCLI,1,LENGTH(DSS.CODE)))
GROUP BY DATE_TRUNC('hour', TL.TAL005_DATE), TL.SRV_ID,
TL.MSU_NUMBER, DS.DESCRIPTION, DS.CALLTYPE;

... but ... on execute, I get the error:
ERROR: variable not found in subplan target list

Any idea?

We have postgresql 7.4 installed on Tru64 and Solaris. The error is the
same.
In other machine we have postgresql 7.3.4 on Solaris and this insert
works fine.

Thanks.

CG

Tom Lane wrote:

> Carmen Gloria Sepulveda Dedes <csepulveda(at)atichile(dot)com> writes:
> > I get:
> > ERROR: variable not found in subplan target list
>
> Could we have enough context to reproduce the problem? I don't have
> time to guess at your table definitions ...
>
> regards, tom lane

Carmen Gloria Sepulveda Dedes <csepulveda(at)atichile(dot)com> writes:
> Ok ... sorry.
> [example snipped]
> ... but ... on execute, I get the error:
> ERROR: variable not found in subplan target list

Hmm. I tried your example on CVS tip and didn't see a problem, which
means either that the bug is already fixed or that the example with
no data isn't sufficient to trigger it. If you enter just the table
declarations you gave into an empty database and then try the INSERT,
does it fail? If not, can you make it fail by altering the values of
enable_nestloop, enable_mergejoin, enable_hashjoin?

regards, tom lane