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 1s3Pty-00CmV0-TW for pgsql-admin@arkaria.postgresql.org; Sun, 05 May 2024 00:38:51 +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 1s3Ptv-003IXR-A9 for pgsql-admin@arkaria.postgresql.org; Sun, 05 May 2024 00:38:48 +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 1s3Ptu-003IV1-Dg for pgsql-admin@lists.postgresql.org; Sun, 05 May 2024 00:38:47 +0000 Received: from mail-bn7nam10olkn2073.outbound.protection.outlook.com ([40.92.40.73] helo=NAM10-BN7-obe.outbound.protection.outlook.com) by magus.postgresql.org with esmtps (TLS1.2) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1s3Ptm-001awY-Lu for pgsql-admin@lists.postgresql.org; Sun, 05 May 2024 00:38:45 +0000 ARC-Seal: i=1; a=rsa-sha256; s=arcselector9901; d=microsoft.com; cv=none; b=TcU4oj1eWE0IqjPhKWKY1W4ILUjiVQTBVenhVC1z8u+kfxYNayTvWSsnBqe5Ja0XTVBEwnbQD5MYzSqFOoQR+7cJCmt5tE8HJeloenQgS/Vc0nwksEEr/p3qx8GvA5x0cVQT9qfCQCd4RJ8Lkd2Ys6fCZilAL4bByLJpDwlY+PWRzo7PkxrD1o6rjAzsVILB2g+XkQuvNaQlgeQO/zwcvVUhyOAnwfNbMAEwrVqu5BoM+qKlwk8+ljK86qVSoebcSfhtE+55YVZwqqX6956NxwS9WDchMxu7PolKLzpqz6Z2nY5/fmCuWqQP4j4uzi5AcwUF6YZQ7fi6SnlqL5h00w== ARC-Message-Signature: i=1; a=rsa-sha256; c=relaxed/relaxed; d=microsoft.com; s=arcselector9901; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-AntiSpam-MessageData-ChunkCount:X-MS-Exchange-AntiSpam-MessageData-0:X-MS-Exchange-AntiSpam-MessageData-1; bh=KgiEJbKH23ZkXBWFSYZvU3hZVOde6WnwFQk1yT2AWaE=; b=P3oiUaWU5Xte750xdgeS4/ypoN7JyH4vjDllN5O1515i9x/IdgpjY6FshhjOks1xBo0Pupz07JMy/9O5WrABtC7LVaoydX7SRY+sxH7jQoVCUmdAIt7b4chpCIIMb14kHd+yGkb4/7Z3uubd75eZVDDtOg7bN4/TJD/b4FrtmBE+/YmM06pwk67BgMm7Aa3NbHZcz7hOfAzVH5x9GX7+f7VTey77Ks7G8m6pA47XYIDop5BW5YXkrcBeLj94rHUG9UU8OY/O/Ka4GQ8aGomhmmN5SJG+9h2O/0iFJNzqjgT5jbDAudmWHMfHQZeXMDdE9upsrj6xX49cHnkqirjTeA== ARC-Authentication-Results: i=1; mx.microsoft.com 1; spf=none; dmarc=none; dkim=none; arc=none DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=outlook.com; s=selector1; h=From:Date:Subject:Message-ID:Content-Type:MIME-Version:X-MS-Exchange-SenderADCheck; bh=KgiEJbKH23ZkXBWFSYZvU3hZVOde6WnwFQk1yT2AWaE=; b=vNNjTcljg1e7KktNvgdVixbq28CpMZhVFgv7a1WnX2fPy2eakRo5YMFX660ztmFyXDzczewOOMGm0irGz1YLTf4ib5o4IGpiLGjF6NPkp7klIt+SF+Y12hXQ6BUBnetT52LPYVoV3PmJBl7FUgXDVOEyxJB46piivAShuLakD7mEq6JA4kwcV5tWsgVz1RIWonxynFXQ/En8dyMi4h2FNRBMdKV1cKMU94CfQT0HuF0wL72nKbc17M3uTfofpPq5KlsK0gv91NcC+vOcZ5PUqXlMZQZtSceNP837VIgdj5ieSdA4w7/2ZATEt1YmzJyFRNh5OYkIolcbUdUAqoc9Rw== Received: from DM4PR19MB5978.namprd19.prod.outlook.com (2603:10b6:8:6a::18) by PH0PR19MB4807.namprd19.prod.outlook.com (2603:10b6:510:26::20) with Microsoft SMTP Server (version=TLS1_2, cipher=TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384) id 15.20.7544.39; Sun, 5 May 2024 00:38:34 +0000 Received: from DM4PR19MB5978.namprd19.prod.outlook.com ([fe80::fef5:a74e:c7e0:9637]) by DM4PR19MB5978.namprd19.prod.outlook.com ([fe80::fef5:a74e:c7e0:9637%6]) with mapi id 15.20.7544.036; Sun, 5 May 2024 00:38:34 +0000 From: M Sarwar To: 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: AdqeS7LIA6dUwT0XSHW6xDaRqeDENwAAU9FLAAqbXYAAAwrDcw== Date: Sun, 5 May 2024 00:38:34 +0000 Message-ID: References: In-Reply-To: Accept-Language: en-US Content-Language: en-US X-MS-Has-Attach: X-MS-TNEF-Correlator: msip_labels: x-ms-exchange-messagesentrepresentingtype: 1 x-tmn: [cEYu8vg5g0gW7gq4suwgUiDW4KfZ/Kkl] x-ms-publictraffictype: Email x-ms-traffictypediagnostic: DM4PR19MB5978:EE_|PH0PR19MB4807:EE_ x-ms-office365-filtering-correlation-id: 2be69d68-403a-4082-d53a-08dc6c9bb255 x-microsoft-antispam: BCL:0;ARA:14566002|461199019|102099023|440099019|3412199016; x-microsoft-antispam-message-info: DZpWvFMoz2xyKdXWpz2cMvy+HpoQvDUpqBN2hpAVSEHo61AwbYtI5QTSuJzHi+Zp1k0SGevYRmxS1jcDE6WJP/qtb5s+HvVdsFHkIuQPsEraG5wMsEENcnA2HNLopQB8H53X6rUWZm2xr8h1AGuSwHDYavXhXjJGtU9Ze4u++JlKMOF2ubUrd9eBWtfLk/tzj91cb+bzQK1Ejlulj2tlaMNhq9rmHOj/MVYbAIfpXQXc8M7KYrOksLZIC4kj47G/4lWQY/GE/4QLinDoYz0S/ZaRkBGjq9XJxr+tMiFFWH7M7PBt5B2m4MOGS83HeUoKu948+Wd+sChBHQpQlXdSnYiLXlLwHXYVfs7GpUF/9H1Eyez3sJtG/rvmKXLwvtT2htDZpvKcM3MjDfjkECekpuIS8Qo9X3tLQU8ZHv/KN/JeEilCDFtYaxPTkWPaGal9uEEP3+pRJsvMlIOXlOlLqhErNKli8llrsMV9tLpZfxWP3MEnF7ToOx5KoYz5dDlxbGC40JZnF5jY/N1kb1FF6CYPzHRfuHt+u7H4AxFcc57c3XdNJYu3XIFeTCwDzpic x-ms-exchange-antispam-messagedata-chunkcount: 1 x-ms-exchange-antispam-messagedata-0: =?iso-8859-1?Q?/Hso7SQ6bfqxG5E9IUjIBvav4sgieWGOcPEH0BoOgzyPmYeX1HzFeqQpUL?= =?iso-8859-1?Q?wwqRqNl0Sf0WvJbw49d8igAEFUQZCvcCvcsjwLTKzVtBwkdNUeE3lH/Rxl?= =?iso-8859-1?Q?2Md/8kgQMIh65wo0HGs5dxAdcQA2BjVnV8/DyuRIRbhUO64YmPVo3zHxcw?= =?iso-8859-1?Q?PIHpmirMPwIMK8HUglI4toUT1bRDCQqU46+Ik1XncyKP3wTVBJXdkKJIlq?= =?iso-8859-1?Q?4WW1KLyeGnJfD/rCneMmoLAcWFjcUgxktCUNI0TB1tJ1Lfqm439Ej8K1hs?= =?iso-8859-1?Q?NPOOGpJa+31DYB2oeFckzZIlwy59cSNiy91FZZenxopJky6tIGjLK36zRE?= =?iso-8859-1?Q?pN5T6TDA3uvnRuObKo679eoOe8t9z5RwyrvGnEEtkb3xqSED83mhgQ8cHK?= =?iso-8859-1?Q?YehZ8dIQvzPZWmUMFL3IfkVovgw5ZMlYFMHJttBCma4LobVd4Shh66nnoB?= =?iso-8859-1?Q?6XOwQZloLko13pbPiRG/69iXoR/xaxjhSrtEFHq0TduM7pKnyR5Fam3pQj?= =?iso-8859-1?Q?CS5HOiKzRbrcyKnmpv/gEgDaGXsVP+GgF8C4EQGmquArmfWIUjCWt8sySL?= =?iso-8859-1?Q?ShK08c3uLmRVY/B9lHcP8AX+Nxb7OrMtTbMnp3KUQ73TWonrCYC57msjVB?= =?iso-8859-1?Q?lZ/nMk6Nk8ncTUrJ5nNItWChinBOuEw0WBpAF5xUHxtzZyLtaBvoGMdYAt?= =?iso-8859-1?Q?i2yzsLn2y9cnjv0yxeUIj0zGKxW7GGBQVrsUrdsxbNNZF4LYB7znAC5pvg?= =?iso-8859-1?Q?OiS3YTU1Zvmwre/WoYpd7EqeA5lDqq5IoX1y2nvaHESVo7G7gbU3cmCxPu?= =?iso-8859-1?Q?3l6sXTuwuQQ/dlQ3FU2F+zllpFx6DY6J+n65uNotqBPbl2ZL139rCusW3/?= =?iso-8859-1?Q?9cb/RSSzWSFrIaJPUQqy4wvAMzQpPL/EmdpmWUvLFrkVvXiVRh2J++9aqa?= =?iso-8859-1?Q?7hamUOfNYC/sfEOicumX+DyDhfIUmvxcEd6NjXmw47G/Vu1caqBGnFb1RL?= =?iso-8859-1?Q?uMFs7vbvkctr4G5boMieaFXejagV0XZxsF5TvjQlJB0EaR8HLvgjzPBp9T?= =?iso-8859-1?Q?IQZod8VaVV9hJeXaSh/YdumM2t78jgVButyehMjuYvIGmuaWI605r3NoqI?= =?iso-8859-1?Q?Ye2of05IUUOmelXI25ZYVF4bh4npY0CZ8kmvBT5xvshhPerBiHv9zRXQ0Y?= =?iso-8859-1?Q?iYRR2fXNs9So2Kmw4GCiz5rh81ss6PlfkCtpHsa6QVCE4KRqZqrHw7lTob?= =?iso-8859-1?Q?VGQCxplxCSQZ6na7lgTh0I5HTjMoQz+m/GDuf/apo=3D?= Content-Type: multipart/alternative; boundary="_000_DM4PR19MB5978A4265AFFC9F7BAEB848AD31D2DM4PR19MB5978namp_" MIME-Version: 1.0 X-OriginatorOrg: outlook.com X-MS-Exchange-CrossTenant-AuthAs: Internal X-MS-Exchange-CrossTenant-AuthSource: DM4PR19MB5978.namprd19.prod.outlook.com X-MS-Exchange-CrossTenant-RMS-PersistedConsumerOrg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-CrossTenant-Network-Message-Id: 2be69d68-403a-4082-d53a-08dc6c9bb255 X-MS-Exchange-CrossTenant-originalarrivaltime: 05 May 2024 00:38:34.7388 (UTC) X-MS-Exchange-CrossTenant-fromentityheader: Hosted X-MS-Exchange-CrossTenant-id: 84df9e7f-e9f6-40af-b435-aaaaaaaaaaaa X-MS-Exchange-CrossTenant-rms-persistedconsumerorg: 00000000-0000-0000-0000-000000000000 X-MS-Exchange-Transport-CrossTenantHeadersStamped: PH0PR19MB4807 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --_000_DM4PR19MB5978A4265AFFC9F7BAEB848AD31D2DM4PR19MB5978namp_ Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hi Erik, TEST1P1 data is coming from the category column, TESTC.TEST_NUMBER. TRESULT.TEST_RESULT column is giving value data. As I stated in my first email which has results of '\d bronx.TEST_RESULTS_= ALL_MCM_INIt' and '\d bronx.TEST_TEST_DETAILS_ALL_MCM_INIT', columns TESTC= .TEST_NUMBER and TRESULT.TEST_RESULT data types are numeric and charac= ter(10) . This is as per table definitions. Are you suggesting me to use the type either TEXT or some other data type i= n the below clause of the SQL. > ) as ConcatenatedResults ( > = SERIALNUMBER charac= ter varying , > TEST1P1 character > -- TEST_RESULT numeric > Thanks, Sarwar ________________________________ From: Erik Wienhold Sent: Saturday, May 4, 2024 7:04 PM To: M Sarwar Cc: pgsql-admin@lists.postgresql.org Subject: Re: ERROR: return and sql tuple descriptions are incompatible On 2024-05-04 20:01 +0200, M Sarwar wrote: > SELECT * > > FROM CROSSTAB ( > > ' > > SELECT > > PART.SERIAL_NUMBER, > > TESTC.TEST_NUMBER, > > TRESULT.TEST_RESULT > > FROM bronx.TEST_PART_DETAILS_ALL_MCM_INIT PART, > > bronx.TEST_RESULTS_ALL_MCM_INIT TRESULT, > > bronx.TEST_TEST_DETAILS_ALL_MCM_INIT TEST= C > > WHERE PART.TEST_PART_DET_ALL_MCM_ID =3D TR= ESULT.TEST_PART_DETAILS_ALL_MCM_ID > > AND TRESULT.TEST_TEST_DETAILS_ALL_MCM_ID =3D TES= TC.TEST_TEST_DETAILS_ALL_MCM_ID > > and PART.STAGE = = =3D ''FT'' > > AND SPLIT_PART (SERIAL_NUMBER, '':'', 1 ) = =3D ''B7307631'' > > And TESTC.TEST_NUMBER = =3D ''TEST1P1'' > > ORDER BY PART.SERIAL_NUMBER , TESTC.TEST_NUMBER > > ' > > ) as ConcatenatedResults ( > > = SERIALNUMBER charac= ter varying , > > TEST1P1 character > > -- TEST_RESULT numeric > > ); > > > > Error Encountered: > > ERROR: return and sql tuple descriptions are incompatible SQL state: 4260= 1 The output value columns (TEST1P1 and TEST_RESULT) must be of the same type. And it must be the same type as the third result column (TRESULT.TEST_RESULT). -- Erik --_000_DM4PR19MB5978A4265AFFC9F7BAEB848AD31D2DM4PR19MB5978namp_ Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable
Hi Erik,

TEST1P1 dat= a is coming from the category column, TESTC.TEST_NUMBER.
TRESULT.TEST_RESULT c= olumn is giving value data.

A= s I stated in my first email which has  results of '\d bronx.TEST_RESULTS_ALL_MCM_INIt' and '\d  = bronx.TEST_TEST_DETAILS_ALL_MCM_INIT', columns TESTC.TEST_NUMBER and TRESULT.TEST_RESULT  data types are  numeric  and   character(10) . This is as per table definitions= .

Are you suggesting me= to use the type either TEXT or some other data type in the below clause of= the SQL.


> )  as Conca= tenatedResults (
>   = ;            &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp;            = ; SERIALNUMBER  character varying ,
> TEST1P1  ch= aracter
> --  &n= bsp;           TEST_RESUL= T numeric
>

Thanks,
Sarwar

From: Erik W= ienhold <ewie@ewie.name>
Sent: Saturday, May 4, 2024 7:04 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.post= gresql.org>
Subject: Re: ERROR: return and sql tuple descriptions are incom= patible
 
On 2024-05-04 20:01 +0200, M Sarwar wrote:<= br> > SELECT *
>
>  FROM CROSSTAB (
>
> '
>
> SELECT
>
>            = ;            &n= bsp;        PART.SERIAL_NUMBER,
>
>         TESTC.TEST_NUMBER,
>
>            = ;            &n= bsp;        TRESULT.TEST_RESULT
>
> FROM      bronx.TEST_PART_DETAILS_ALL_MCM_INI= T PART,
>
>            = ;            &n= bsp;        bronx.TEST_RESULTS_ALL_MCM_I= NIT TRESULT,
>
>            = ;            &n= bsp;        bronx.TEST_TEST_DETAILS_ALL_= MCM_INIT TESTC
>
> WHERE PART.TEST_PART_DET_ALL_MCM_ID      = ;            &n= bsp;            = ; =3D TRESULT.TEST_PART_DETAILS_ALL_MCM_ID
>
>            = ;     AND TRESULT.TEST_TEST_DETAILS_ALL_MCM_ID &nb= sp;        =3D TESTC.TEST_TEST_DETAILS_A= LL_MCM_ID
>
>            = ;     and PART.STAGE      = ;            &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp; =3D ''FT''
>
>            = ;    AND SPLIT_PART (SERIAL_NUMBER, '':'', 1 )  &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp;            = ; =3D ''B7307631''
>
>            = ;     And TESTC.TEST_NUMBER    &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;            &= nbsp;           &nbs= p;          =3D ''TEST1P1'' >
> ORDER BY PART.SERIAL_NUMBER , TESTC.TEST_NUMBER
>
> '
>
> )  as ConcatenatedResults (
>
>            = ;            &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp;            = ;            &n= bsp;    SERIALNUMBER  character varying ,
>
> TEST1P1  character
>
> --           &n= bsp;  TEST_RESULT numeric
>
> );
>
>
>
> Error Encountered:
>
> ERROR: return and sql tuple descriptions are incompatible SQL state: 4= 2601

The output value columns (TEST1P1 and TEST_RESULT) must be of the same
type.  And it must be the same type as the third result column
(TRESULT.TEST_RESULT).

--
Erik
--_000_DM4PR19MB5978A4265AFFC9F7BAEB848AD31D2DM4PR19MB5978namp_--