Re: Error in select - Mailing list pgsql-general

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@atichile.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