From: Wetmore, Matthew (CTR) <Matthew.Wetmore@evernorth.com>
To: M Sarwar <sarwarmd02@outlook.com>
To: Erik Wienhold <ewie@ewie.name>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: RE: ERROR: return and sql tuple descriptions are incompatible
Date: Tue, 5 Mar 2024 19:29:49 +0000
Message-ID: <4c822fcec28d4d05812bad9a3efe6354@evernorth.com> (raw)
In-Reply-To: <DM4PR19MB5978B5B33123FF5E36D010E4D3222@DM4PR19MB5978.namprd19.prod.outlook.com>
References: <0100018e0f90c970-2815e582-2562-42a7-93d6-c417afbb691e-000000@email.amazonses.com>
<PH2P110MB0922E3D143AFA253E85B3840FF22A@PH2P110MB0922.NAMP110.PROD.OUTLOOK.COM>
<PH2P110MB092205415A4084280FC9B429FF22A@PH2P110MB0922.NAMP110.PROD.OUTLOOK.COM>
<DM4PR19MB597886696589C5CE33F5D58AD3222@DM4PR19MB5978.namprd19.prod.outlook.com>
<q2cdz27dkwel73k27ckt2i6g6ce55wzodaogbgfnoqccupqndx@vz3cnoihz4j5>
<DM4PR19MB5978B5B33123FF5E36D010E4D3222@DM4PR19MB5978.namprd19.prod.outlook.com>
Character is also deprecated, and will pad space up to 10
From: M Sarwar <sarwarmd02@outlook.com>
Sent: Tuesday, March 5, 2024 10:57 AM
To: Erik Wienhold <ewie@ewie.name>
Cc: pgsql-admin@lists.postgresql.org
Subject: [EXTERNAL] Re: ERROR: return and sql tuple descriptions are incompatible
Hello Erik,
Second column TEST_NUMBER is type CHARACTER (10) which non numeric data.
Thanks,
Sarwar
________________________________
From: Erik Wienhold <ewie@ewie.name<mailto:ewie@ewie.name>>
Sent: Tuesday, March 5, 2024 1:06 PM
To: M Sarwar <sarwarmd02@outlook.com<mailto:sarwarmd02@outlook.com>>
Cc: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org> <pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org>>
Subject: Re: ERROR: return and sql tuple descriptions are incompatible
On 2024-03-05 18:17 +0100, M Sarwar wrote:
> I am trying to run the below SQL.
> SELECT * FROM CROSSTAB (
> 'SELECT
> PART.SERIAL_NUMBER ,
> TESTC.TEST_NUMBER,
> TRESULT.TEST_RESULT
> FROM BRONXdev.TEST_PART_DETAILS_ALL_MCM PART,
> BRONXDEV.TEST_RESULTS_ALL_MCM TRESULT,
> BRONXDEV.TEST_TEST_DETAILS_ALL_MCM TESTC
> WHERE PART.TEST_PART_DET_ALL_MCM_ID = TRESULT.TEST_PART_DETAILS_ALL_MCM_ID
> AND TRESULT.TEST_TEST_DETAILS_ALL_MCM_ID = TESTC.TEST_TEST_DETAILS_ALL_MCM_ID
> AND PART.DATE1 = ''12/01/2023''
> and PART.STAGE = ''BI''
> AND TESTC.TEST_NUMBER = ''TEST1P2''
> ORDER BY 1, 2'
> )
> AS Concatenated ( SERIAL_NUMBER character varying(18), TEST_NUMBER character(10), TEST_RESULT NUMERIC )
> ;
> I am getting the below error message.
>
> ERROR: return and sql tuple descriptions are incompatible
> SQL state: 42601
The problem is that you define TEST_NUMBER as an output column. You
must instead define the output row as one group column (SERIAL_NUMBER)
and N value columns (TEST_RESULT) all of the same type. For example
with 2 value columns:
AS concatenated (
SERIAL_NUMBER character varying(18),
TEST_RESULT_1 NUMERIC,
TEST_RESULT_2 NUMERIC
)
The second column (TEST_NUMBER) of your query is ignored by crosstab()
and only used to ensure that values in the same group are properly layed
out in the output row.
That is also explained with examples in the docs:
https://na01.safelinks.protection.outlook.com/?url=https%3A%2F%2Fwww.postgresql.org%2Fdocs%2Fcurrent...;
--
Erik
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: Matthew.Wetmore@evernorth.com, sarwarmd02@outlook.com, ewie@ewie.name, pgsql-admin@lists.postgresql.org
Subject: RE: ERROR: return and sql tuple descriptions are incompatible
In-Reply-To: <4c822fcec28d4d05812bad9a3efe6354@evernorth.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox