Обсуждение: str_aggr function not wokring
From: aditya desai <admad123@gmail.com>
Sent: Thursday, April 8, 2021 1:32 PM
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: str_aggr function not wokring
Hi,
I need to combine results of multiple rows in one row. I get below error. Could you please help.
Query:
select string_agg((select '******' || P.PhaseName || ' - ' || R.Recommendation AS "ABC" from tblAssessmentRecommendation R,tblAssessmentPhases P
where R.PhaseID = P.PhaseID Order BY P.sortOrder DESC),' ')
Error:
ERROR: more than one row returned by a subquery used as an expression SQL state: 21000
Regards,
Aditya.
Hi,
I would suggest you to try something like this instead
select string_agg( '******' || P.PhaseName || ' - ' || R.Recommendation '' ORDER BY P.sortOrder DESC ) AS "ABC"
from tblAssessmentRecommendation R,tblAssessmentPhases P
where R.PhaseID = P.PhaseID
Regards,
Patrick
From: aditya desai <admad123@gmail.com>
Sent: Thursday, April 8, 2021 1:32 PM
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: str_aggr function not wokring
Hi,
I need to combine results of multiple rows in one row. I get below error. Could you please help.
Query:
select string_agg((select '******' || P.PhaseName || ' - ' || R.Recommendation AS "ABC" from tblAssessmentRecommendation R,tblAssessmentPhases P
where R.PhaseID = P.PhaseID Order BY P.sortOrder DESC),' ')
Error:
ERROR: more than one row returned by a subquery used as an expression SQL state: 21000
Regards,
Aditya.
Hi,
I would suggest you to try something like this instead
select string_agg( '******' || P.PhaseName || ' - ' || R.Recommendation '' ORDER BY P.sortOrder DESC ) AS "ABC"
from tblAssessmentRecommendation R,tblAssessmentPhases P
where R.PhaseID = P.PhaseID
Regards,
Patrick
You realize that there are a million answers to your questions online? Are you doing any google searches before bothering this list with basic questions? I personally never email this list until I’ve exhausted all searches and extensive trial and error, as do most practitioners. This list is incredibly patient and polite, and...there are limits. Please consider doing more research before asking a question. In your example below, you’re getting a basic subquery error – research how to fix that. Mike
From: aditya desai <admad123@gmail.com>
Sent: Thursday, April 08, 2021 4:32 AM
To: Pgsql Performance <pgsql-performance@lists.postgresql.org>
Subject: str_aggr function not wokring
Hi,
I need to combine results of multiple rows in one row. I get below error. Could you please help.
Query:
select string_agg((select '******' || P.PhaseName || ' - ' || R.Recommendation AS "ABC" from tblAssessmentRecommendation R,tblAssessmentPhases P
where R.PhaseID = P.PhaseID Order BY P.sortOrder DESC),' ')
Error:
ERROR: more than one row returned by a subquery used as an expression SQL state: 21000
Regards,
Aditya.