From: 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: Sun, 5 May 2024 00:38:34 +0000
Message-ID: <DM4PR19MB5978A4265AFFC9F7BAEB848AD31D2@DM4PR19MB5978.namprd19.prod.outlook.com> (raw)
In-Reply-To: <vulsb3n6dcd7wzjuhslppt3oij3nkwqh2olagmx2ibtnjzpccu@7c2jvcyjm2x2>
References: <PH2P110MB0922C5109BE107AD82331420FF1EA@PH2P110MB0922.NAMP110.PROD.OUTLOOK.COM>
<DM4PR19MB59783F61D28BE07B43F68C31D31E2@DM4PR19MB5978.namprd19.prod.outlook.com>
<vulsb3n6dcd7wzjuhslppt3oij3nkwqh2olagmx2ibtnjzpccu@7c2jvcyjm2x2>
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 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 ConcatenatedResults (
> SERIALNUMBER character varying ,
> TEST1P1 character
> -- TEST_RESULT numeric
>
Thanks,
Sarwar
________________________________
From: Erik Wienhold <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.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 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.STAGE = ''FT''
>
> AND SPLIT_PART (SERIAL_NUMBER, '':'', 1 ) = ''B7307631''
>
> And TESTC.TEST_NUMBER = ''TEST1P1''
>
> ORDER BY PART.SERIAL_NUMBER , TESTC.TEST_NUMBER
>
> '
>
> ) as ConcatenatedResults (
>
> SERIALNUMBER character varying ,
>
> TEST1P1 character
>
> -- TEST_RESULT numeric
>
> );
>
>
>
> Error Encountered:
>
> ERROR: return and sql tuple descriptions are incompatible SQL state: 42601
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
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: sarwarmd02@outlook.com, ewie@ewie.name, pgsql-admin@lists.postgresql.org
Subject: Re: ERROR: return and sql tuple descriptions are incompatible
In-Reply-To: <DM4PR19MB5978A4265AFFC9F7BAEB848AD31D2@DM4PR19MB5978.namprd19.prod.outlook.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