Re: ERROR: return and sql tuple descriptions are incompatible - Mailing list pgsql-admin

From Erik Wienhold
Subject Re: ERROR: return and sql tuple descriptions are incompatible
Date
Msg-id q2cdz27dkwel73k27ckt2i6g6ce55wzodaogbgfnoqccupqndx@vz3cnoihz4j5
Whole thread Raw
In response to ERROR: return and sql tuple descriptions are incompatible  (M Sarwar <sarwarmd02@outlook.com>)
Responses Re: ERROR: return and sql tuple descriptions are incompatible
List pgsql-admin
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://www.postgresql.org/docs/current/tablefunc.html#TABLEFUNC-FUNCTIONS-CROSSTAB-TEXT

-- 
Erik



pgsql-admin by date:

Previous
From: M Sarwar
Date:
Subject: ERROR: return and sql tuple descriptions are incompatible
Next
From: M Sarwar
Date:
Subject: Re: ERROR: return and sql tuple descriptions are incompatible