Hi all,
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
bronxdb1=> \d bronx.TEST_RESULTS_ALL_MCM_INIt
Table "bronx.test_results_all_mcm_init"
Column | Type | Collation | Nullable | Default
------------------------------+--------------------------+-----------+----------+----------------------------------
test_results_all_mcm_id | integer | | not null | generated by default as identity
test_part_details_all_mcm_id | bigint | | |
test_result | numeric | | |
test_test_details_all_mcm_id | integer | | |
bronxdb1=> \d bronx.TEST_TEST_DETAILS_ALL_MCM_INIT
Table "bronx.test_test_details_all_mcm_init"
Column | Type | Collation | Nullable | Default
------------------------------+--------------------------+-----------+----------+----------------------------------
test_test_details_all_mcm_id | integer | | not null | generated by default as identity
stage | character(50) | | |
test_number | character(10) | | |
This is known issue and googled almost half day today and could not succeed.
There are lab works available on the internet but they are not helping in solving my issue.
Postgres Version 13.5
AWS RDS platform.
Thanks,
Sarwar