Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rhn6P-00DNpp-F5 for pgsql-admin@arkaria.postgresql.org; Wed, 06 Mar 2024 08:58:18 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rhn6O-007Dqp-4Y for pgsql-admin@arkaria.postgresql.org; Wed, 06 Mar 2024 08:58:16 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rhaUc-002EfW-5J for pgsql-admin@lists.postgresql.org; Tue, 05 Mar 2024 19:30:26 +0000 Received: from mx0b-000e5c01.pphosted.com ([67.231.157.188]) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rhaUW-002wgu-PN for pgsql-admin@lists.postgresql.org; Tue, 05 Mar 2024 19:30:25 +0000 Received: from pps.filterd (m0072696.ppops.net [127.0.0.1]) by mx0b-000e5c01.pphosted.com (8.17.1.19/8.17.1.19) with ESMTP id 425HqaKD022938; Tue, 5 Mar 2024 14:30:16 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=evernorth.com; h=from : to : cc : subject : date : message-id : references : in-reply-to : content-type : mime-version; s=pod011016231234; bh=K1xBYfdgMRV4Q7FDpll65RC2zgpofYub077iqnA7/0o=; b=v2aP2IBgP17k/tcfc2WcPJrLPjdzrew0EXW5uvzOKLJLkYzrFV1ovHWSBHU+Fq9eB1mq Y9impXfugJFnXGCD3hgUZ75jXV5Ok+mJ/EeUtIWphdl35Y7/fDIYWg4NO7MXJdwd9oHO HaVcHhZYHcT0Coii85a6BQwGWt/8Y3PmW9fQ+YZj+iOHb/sto7XGaSuIV4wt/hYVDt+s ObI77kf+LgQl4pBOz/NWnJvvxxrK/BigtC7y6kw/xtKODtsLHzciwKRsEvmEhkgOgrnm v8u6bnxWlXVGERiPOUPKJ4oiJPARv4V03pYWtM3gfEDI7vC4dkXZg2xGd53waJYmQ0bA 6A== Received: from express-scripts.com (mx0a-000e5c14.pphosted.com [148.163.133.209]) by mx0b-000e5c01.pphosted.com (PPS) with ESMTPS id 3wp861gaa6-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT); Tue, 05 Mar 2024 14:30:15 -0500 Received: from m0206226.ppops.net (m0206226.ppops.net [127.0.0.1]) by pps.cigna (8.17.1.5/8.17.1.5) with ESMTP id 425JQcop022357; Tue, 5 Mar 2024 13:29:52 -0600 Received: from eiaappxp0007.gid.prd.globalcore.com ([170.48.0.130]) by mx0a-000e5c14.pphosted.com (PPS) with ESMTPS id 3wm0gdnh53-1 (version=TLSv1.2 cipher=ECDHE-RSA-AES256-GCM-SHA384 bits=256 verify=NOT); Tue, 05 Mar 2024 13:29:51 -0600 Received: from cvlappxp20750.internal.cigna.com (HELO PS2PW1012187.accounts.root.corp) ([10.36.52.82]) by EIAAPPXP0007.gid.prd.globalcore.com with ESMTP/TLS/ECDHE-RSA-AES256-GCM-SHA384; 05 Mar 2024 19:29:50 +0000 Received: from PS2PW1012186.accounts.root.corp (10.221.225.154) by PS2PW1012187.accounts.root.corp (10.221.225.157) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.2.1258.27; Tue, 5 Mar 2024 14:29:49 -0500 Received: from PS2PW1012186.accounts.root.corp ([fe80::9fec:fffb:1381:33ad]) by PS2PW1012186.accounts.root.corp ([fe80::9fec:fffb:1381:33ad%6]) with mapi id 15.02.1258.027; Tue, 5 Mar 2024 14:29:49 -0500 From: "Wetmore, Matthew (CTR)" To: M Sarwar , Erik Wienhold CC: "pgsql-admin@lists.postgresql.org" Subject: RE: ERROR: return and sql tuple descriptions are incompatible Thread-Topic: ERROR: return and sql tuple descriptions are incompatible Thread-Index: AQHaby8DqGVZYDVrYky8A8WzXiSTR7EpiGiA Date: Tue, 5 Mar 2024 19:29:49 +0000 Message-ID: <4c822fcec28d4d05812bad9a3efe6354@evernorth.com> References: <0100018e0f90c970-2815e582-2562-42a7-93d6-c417afbb691e-000000@email.amazonses.com> In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: x-originating-ip: [10.221.252.1] x-proofpoint-routing: 000e5c14 Content-Type: multipart/alternative; boundary="_000_4c822fcec28d4d05812bad9a3efe6354evernorthcom_" MIME-Version: 1.0 X-CFilter-Loop: Reflected X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.272,Aquarius:18.0.1011,Hydra:6.0.619,FMLib:17.11.176.26 definitions=2024-03-05_16,2024-03-05_01,2023-05-22_02 X-Proofpoint-GUID: PYcHJopxqYTu-KHbB768su8C1SxKXbow X-Proofpoint-ORIG-GUID: PYcHJopxqYTu-KHbB768su8C1SxKXbow X-Connecting-IP: 148.163.133.209 X-External-Mail: True X-Content-Length: 14070 X-Proofpoint-Virus-Version: vendor=baseguard engine=ICAP:2.0.272,Aquarius:18.0.1011,Hydra:6.0.619,FMLib:17.11.176.26 definitions=2024-03-05_16,2024-03-05_01,2023-05-22_02 X-Proofpoint-Spam-Details: rule=notspam policy=default score=0 phishscore=0 spamscore=0 malwarescore=0 adultscore=0 mlxlogscore=999 bulkscore=0 mlxscore=0 clxscore=1011 priorityscore=1501 suspectscore=0 lowpriorityscore=0 impostorscore=0 classifier=spam adjust=0 reason=mlx scancount=1 engine=8.12.0-2311290000 definitions=main-2403050157 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_4c822fcec28d4d05812bad9a3efe6354evernorthcom_ Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: quoted-printable Character is also deprecated, and will pad space up to 10 From: M Sarwar Sent: Tuesday, March 5, 2024 10:57 AM To: Erik Wienhold Cc: pgsql-admin@lists.postgresql.org Subject: [EXTERNAL] Re: ERROR: return and sql tuple descriptions are incomp= atible Hello Erik, Second column TEST_NUMBER is type CHARACTER (10) which non numeric data. Thanks, Sarwar ________________________________ From: Erik Wienhold > Sent: Tuesday, March 5, 2024 1:06 PM To: M Sarwar > Cc: 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 =3D TRESULT.TEST_PART_DETAILS_ALL_MCM= _ID > AND TRESULT.TEST_TEST_DETAILS_ALL_MCM_ID =3D TESTC.TEST_TEST_DETAILS_ALL_= MCM_ID > AND PART.DATE1 =3D ''12/01/2023'' > and PART.STAGE =3D ''BI'' > AND TESTC.TEST_NUMBER =3D ''TEST1P2'' > ORDER BY 1, 2' > ) > AS Concatenated ( SERIAL_NUMBER character varying(18), TEST_NUMBER charac= ter(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=3Dhttps%3A%2F%2Fwww.post= gresql.org%2Fdocs%2Fcurrent%2Ftablefunc.html%23TABLEFUNC-FUNCTIONS-CROSSTAB= -TEXT&data=3D05%7C02%7C%7C8826a46f3d7d47b7aa6508dc3d3f0f61%7C84df9e7fe9f640= afb435aaaaaaaaaaaa%7C1%7C0%7C638452588247259565%7CUnknown%7CTWFpbGZsb3d8eyJ= WIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7= C&sdata=3DV2cICNmt8zEsOAJO%2BZE3OEDblPOj9aFk2RJkii76QdI%3D&reserved=3D0 -- Erik --_000_4c822fcec28d4d05812bad9a3efe6354evernorthcom_ Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

Character is also deprecated, and wil= l pad space up to 10

 

From: M Sarwar <sarwarmd02@outloo= k.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 n= on numeric data.

Thanks,

Sarwar

 

From: Erik W= ienhold <ewie@ewie.name>
Sent: Tuesday, March 5, 2024 1:06 PM
To: M Sarwar <sarwarmd0= 2@outlook.com>
Cc: pgsql-admin@= lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: ERROR: return and sql tuple descriptions are incompatib= le

 

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 =3D TRESULT.TEST_PART_DETAILS_ALL_= MCM_ID
> AND TRESULT.TEST_TEST_DETAILS_ALL_MCM_ID =3D TESTC.TEST_TEST_DETAILS_A= LL_MCM_ID
> AND PART.DATE1 =3D ''12/01/2023''
> and PART.STAGE =3D ''BI''
> AND TESTC.TEST_NUMBER =3D ''TEST1P2''
> ORDER BY 1, 2'
> )
> AS Concatenated ( SERIAL_NUMBER character varying(18), TEST_NUMBER cha= racter(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_N= UMBER character varying(18),
            TEST_RES= ULT_1 NUMERIC,
            TEST_RES= ULT_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=3Dhttps%3A%2F%2Fwww.postgresql.org%2Fdocs%2Fcurrent%2Ftablefunc.html%= 23TABLEFUNC-FUNCTIONS-CROSSTAB-TEXT&data=3D05%7C02%7C%7C8826a46f3d7d47b= 7aa6508dc3d3f0f61%7C84df9e7fe9f640afb435aaaaaaaaaaaa%7C1%7C0%7C638452588247= 259565%7CUnknown%7CTWFpbGZsb3d8eyJWIjoiMC4wLjAwMDAiLCJQIjoiV2luMzIiLCJBTiI6= Ik1haWwiLCJXVCI6Mn0%3D%7C0%7C%7C%7C&sdata=3DV2cICNmt8zEsOAJO%2BZE3OEDbl= POj9aFk2RJkii76QdI%3D&reserved=3D0

--
Erik

--_000_4c822fcec28d4d05812bad9a3efe6354evernorthcom_--