Re: Error in select

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