Thread: 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@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

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

Carmen Gloria Sepulveda Dedes <csepulveda@atichile.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