Обсуждение: str_aggr function not wokring

Поиск
Список
Период
Сортировка

str_aggr function not wokring

От
aditya desai
Дата:
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.

RE: str_aggr function not wokring

От
Patrick FICHE
Дата:

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

 

Re: str_aggr function not wokring

От
aditya desai
Дата:
Thanks Patrick. I used WITH Query and feeded that output to string_aggr which worked. However it is giving performance issues. Will check on that. THanks.

On Thu, Apr 8, 2021 at 5:11 PM Patrick FICHE <Patrick.Fiche@aqsacom.com> wrote:

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

 

Re: str_aggr function not wokring

От
aditya desai
Дата:
Sure!! Thanks for the response. Apologies for multiple questions. Faced this during high priority MSSQL to PostgreSQL migration. Did not see any equivalent of XML PATH which would give desired results. Finally was able to resolve the issue by rewriting the Proc using WITH and string_aggr in combination. However still facing performance issues in the same. Will investigate it.

On Thu, Apr 8, 2021 at 5:08 PM Mike Sofen <msofen@runbox.com> wrote:

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.