Hi Erik,
I appreciate your response on this.
Now I have changed from ORDER BY 1,2 to ORDER BY 1.
Sorting TEST_NUMBER is taken care while generating TEST_NUMBER columns data.
Reference from the link.
AS concatenated (
SERIAL_NUMBER character varying(18), TEST_RESULT_1 NUMERIC, TEST_RESULT_2 NUMERIC )
Question on this: As per this, there is no need to define the column TEST_NUMBER format. That means, database is identifying the category column ( TEST_NUMBER ) on it's own. Am I right?
Thank you,
Sarwar
From: Erik Wienhold <ewie@xxxxxxxxx>
Sent: Saturday, May 4, 2024 8:56 PM To: M Sarwar <sarwarmd02@xxxxxxxxxxx> Cc: pgsql-admin@xxxxxxxxxxxxxxxxxxxx <pgsql-admin@xxxxxxxxxxxxxxxxxxxx> Subject: Re: ERROR: return and sql tuple descriptions are incompatible On 2024-05-05 02:38 +0200, M Sarwar wrote:
> TEST1P1 data is coming from the category column, TESTC.TEST_NUMBER. > TRESULT.TEST_RESULT column is giving value data. Column TESTC.TEST_NUMBER (i.e. the second column) is ignored by crosstab and it only requires the use of ORDER BY 1,2 to get correct results. Output columns TEST1P1 and TEST_RESULT are two of the many value columns which are only filled by the third result column. I now remember it's the same issue from your very first crosstab mail: https://na01.safelinks.protection.outlook.com/?url=""> > 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. I'd say use numeric for TEST1P1 and TEST_RESULT. But the column names and types suggest that you expect the second result column in the crosstab output. Which isn't possible to my knowledge. -- Erik |